Lesson Learned #134: Row Level Security and Session Context

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.