Today, I worked on a service request that our customer needs to avoid that some users could retrieve some rows but depending on a specific value of customer application. In this situation, I suggested to use sp_set_session_context that allows to save some details in the session context and use it during the duration of the session.
The first thing that I need to do, is, for example, run the following TSQL command every time that I stablished a connection to the SQL Database - EXEC sp_set_session_context 'Allow', 1
The value 1 means that I will be able to see the data and 0 not.
Right now, I need to create the function using this code:
CREATE FUNCTION SecPred1(@Allow int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS valor where @Allow=1
And I'm going to create a dummy table:
CREATE TABLE Protegido1
(ID int IDENTITY PRIMARY KEY,
Nombre varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
Apellido varchar(100) NOT NULL,
NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
UserID int );
INSERT Protegido1 (Nombre, Apellido, NrTlf, Email, UserId) VALUES
('Roberto', 'Torres', '91551234567', 'RTorres@contoso.com', 5),
('Juan', 'Galvin', '95551234568', 'JGalvin@contoso.com', 5),
('José', 'Garcia', '95551234569', 'Jgarcia@contoso.net',1);
Finally, I'm going to apply this function to the security context:
CREATE SECURITY POLICY [secpol1] ADD FILTER PREDICATE [dbo].[SecPred1](CONVERT(int,SESSION_CONTEXT(N'Allow') )) on [dbo].[Protegido1]
For example, if I change the value to 0 using the following TSQL: EXEC sp_set_session_context 'Allow', 0 and I run the command select * from Protegido1 I don't have any results but if I changed to 1, for example, EXEC sp_set_session_context 'Allow', 1 I'm going to obtain all the values.
Enjoy!
Microsoft
