THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Row Level Security-Part 3-A few more advanced scenarios

In this final entry (for now!) in my series on Row Level Security (Part 1, Part 2) I am going to show a couple of slightly more complex scenarios. I will only scratch the surface of what you could do, but keep in mind that performance is really important to how you make use of Row Level Security. In a future blog, I will build a couple of really large tables and try out Dynamic Data Masking and Row Level Security, as I am sure many other bloggers will as well.

I will do two main scenarios. First dealing with the scenario that you have one database principal context you are working with, and the second allowing you to set up a table that you specify that a user has rights to an individual row.

Code for this third entry executed using:

SELECT @@version

---------------------------------------
Microsoft SQL Server 2016 (RC2) - 13.0.1300.275 (X64)   Mar 26 2016 03:43:12   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Single Login

In this first scenario, you have an app that only does one login, but users have different roles that you want to seperate out. SQL Server 2016 has added a connection level function that allows you to set a context on the session that a system function can read. This is a practice I have used for many years with context_info, but it was a single value that other users might tramle on. Session_context gives you a set of name-value pairs that you can read in, allowing you to set variables for a connection.

Basically, you can execute something like this on your connection:

EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'BigHat';

And then when you need the value, execute:

SELECT SESSION_CONTEXT(N'securityGroup');

You get the following back (truncated from the max 256 characters):

---------------
BigHat

Note: if you want to read more about the details of session_context, check out Aaron Bertrand's great blog here: https://www.mssqltips.com/sqlservertip/4094/phase-out-contextinfo-in-sql-server-2016-with-sessioncontext/

Ok, so now we will drop the security policy, and recreate the function using the SESSION_CONTEXT instead of USER_NAME() as
we did it in the previous blogs:

DROP SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy;
GO

ALTER FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
            WHERE @ManagedByUser = SESSION_CONTEXT(N'securityGroup') --If the ManagedByUser = the securityGroup
               OR (SESSION_CONTEXT(N'securityGroup') = N'MedHat' and @ManagedByUser <> 'BigHat')  --if the securityGroup is MedHat, and the row isn't managed by BigHat
               OR (SESSION_CONTEXT(N'securityGroup') = 'BigHat') --Or the user is the BigHat person, they can see everything ;
               OR (USER_NAME() = 'dbo')); --dbo gets it all


Compare to our earlier predicate function from part 1 (link)

    RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
            WHERE @ManagedByUser = USER_NAME() --If the ManagedByUser = the database username
               OR (USER_NAME() = 'MedHat' and @ManagedByUser <> 'BigHat') --if the user is MedHat, and the row isn't managed by BigHat
                OR (USER_NAME() IN ('BigHat','dbo'))); --give 'dbo' full rights;

The only difference is that we are only using USER_NAME for the DBO user, and if you aren't a member of one of the security groups you will see nada.

Now we recreate our security policy that lets people see data if they manage the row, and insert only to the security group they are set to.

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
ADD FILTER PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate]([ManagedByUser]) ON Demo.saleItem,
ADD BLOCK PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate]([ManagedByUser]) ON Demo.SaleItem AFTER INSERT
WITH (STATE = ON, SCHEMABINDING = ON);

Bear in mind that you should usually not let a user do the following in ad-hoc SQL, because there is no control over the value parameter that would make sense (other than perhaps limiting the group names. But any user could put anything in if they can execute the procedure (as I am about to do.)

First using an undefined security group;

EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'Undefined';
SELECT * FROM Demo.SaleItem;


Wait, what?

SaleItemId  ManagedByUser
----------- ---------------
1           BigHat
2           BigHat
3           MedHat
4           MedHat
5           SmallHat
6           SmallHat
7           BigHat
8           BigHat

Ah yes, I am the dbo. Security testing is tricky! I will set context to SmallHat, one of the accounts we have been using.

EXECUTE AS USER = 'SmallHat';

Now try again!

EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'Undefined';
SELECT * FROM Demo.SaleItem;

No rows are returned

saleItemId  ManagedByUser
----------- ---------------

Now let us try one of the roles we defined.

EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'SmallHat';
SELECT * FROM Demo.SaleItem;

saleItemId  ManagedByUser
----------- ---------------
5           SmallHat
6           SmallHat

The two rows expected. Next the last two.

EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'MedHat';
SELECT * FROM Demo.SaleItem;
EXEC sys.sp_set_session_context @key = N'securityGroup', @value = 'BigHat';
SELECT * FROM Demo.SaleItem;

SaleItemId  ManagedByUser
----------- ---------------
3           MedHat
4           MedHat
5           SmallHat
6           SmallHat

SaleItemId  ManagedByUser
----------- ---------------
1           BigHat
2           BigHat
3           MedHat
4           MedHat
5           SmallHat
6           SmallHat
7           BigHat
8           BigHat

What is important to note here is if you are doing this wih a single connetion, you don't necessarily have to disconnect, but it is imperative that the user executes the session context setting procedure before every execution.

Don't forget to revert back to dbo so you can do the next configuration if desired:

REVERT;

(Slightly More) Complex (But Not As Complex as It Could Be) Security Configurations

Realitically, you can do almost any mapping you want. In this example, I just want to demo using a table to hold an access control list, where in addition to the security we already have configured, I specifically say what my 2 users can see beyond what they are set up as managers, while leaving BigHat Open to seeing all data.

To start with, I will create a table of primary keys for the SaleItem, along with the security context names (I don’t need to grant rights to the users to read from the table, just like I don’t have to grant rights to execute the function.)

CREATE TABLE rowLevelSecurity.SaleItemOverride
(
    SaleItemId int NOT NULL,
    ManagedByUser nvarchar(15) NOT NULL,
    CONSTRAINT PKSaleItem PRIMARY KEY (SaleItemId, ManagedByUser)
);


I am going back to using USER_NAME() instead of the session context, but the concept is very much the same.
Now we create some security data. What I am going to do here is, in addition to idea that the user gets to see
all items that they are the managing user for, now we are giving them access to additional rows.

INSERT INTO rowLevelSecurity.SaleItemOverride
VALUES ( 1, 'SmallHat'), (2,'SmallHat'),(8,'MedHat');

Now we add in the SaleItemId into the parameters, so we can check the data against the data in the table we have
just created.

DROP SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy;
GO

ALTER  FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@saleItemId int, @ManagedByUser AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
            WHERE @ManagedByUser = USER_NAME()
               OR (USER_NAME() = N'MedHat' and @ManagedByUser <> 'BigHat')
               OR USER_NAME() = 'BigHat'   
              --adding on this predicate that says to check the user name to the managedbyUser column in the override table        
               OR EXISTS ( SELECT 1
                            FROM   rowLevelSecurity.SaleItemOverride
                            WHERE (ManagedByUser = USER_NAME() --the user who is being given access to a row
                                   or USER_NAME() = N'MedHat') --or MedHat who has accesss to all that the small user has
                              AND SaleItemId = @saleItemId)
                           );
GO


And now recreate the security policy, this time with two parameters for each function:

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate](SaleItemId, ManagedByUser) ON Demo.saleItem,
    ADD BLOCK PREDICATE [rowLevelSecurity].[ManagedByUser$SecurityPredicate](SaleItemId, ManagedByUser) ON Demo.SaleItem AFTER INSERT
    WITH (STATE = ON, SCHEMABINDING = ON);


With this recreated, now let's try it out. First we will show the override rows, and then change to the SmallHat security principal and see what we get. First reviewing what is in the override table.

SELECT *
FROM   rowLevelSecurity.SaleItemOverride;

 
This returns:

SaleItemId  ManagedByUser
----------- ---------------
1           SmallHat
2           SmallHat
8           MedHat

So for SmallHat, we should be able to see SalesItemId 1 and 2, no matter who the ManagedyUser is, along with any rows with ManagedByUser = SmallHat in the SaleItem table:

EXECUTE AS USER = 'SmallHat';
GO
SELECT * FROM Demo.SaleItem;
GO
REVERT;


Which we can.

SaleItemId  ManagedByUser
----------- ---------------
1           BigHat
2           BigHat
5           SmallHat
6           SmallHat

Now let's see what happens when we do MedHat, who can see the rows of SmallHat too, even if they were given to them through our security table:

EXECUTE AS USER = 'MedHat';
GO
SELECT * FROM Demo.SaleItem;
GO
REVERT;

SaleItemId  ManagedByUser
----------- ---------------
1           BigHat
2           BigHat
3           MedHat
4           MedHat
5           SmallHat
6           SmallHat
8           BigHat

And if you execute the BigHat example, you will see that they do still have complete access.

Naturally, this is not the end of the possible scenarios you could implement with row level security. You will be able to create complex examples, commonly not by using users, but using the groups that users are members of, and/or possibly your complex org chart, which might be a hierarchy. Using a hierarchy is one example that Books Online suggests as a bad idea, but check on the concept of the Kimball Helper table as a way to have your hierarchy, but a flattened version of it as well that will make queries a lot faster (in my test examples from my hierarchies presentatation with over 1/2 million nodes, I have seen amazing performance even on a laptop level machine) if you can use a
key lookup rather than some form of scan.

I will be adding to the blog after I finish the book some deeper examples of hiearchies. For an example now of how Jamey Johnston (@statcowboy) has built a hierarchy to work with some data in the real world, check his blog where he details the examples from his 2016 presentation: http://blog.jameyjohnston.com/oil-gas-sql-server-security-demo/.

Published Wednesday, April 06, 2016 10:03 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nivas said:

Thank you for the excellent write up. I have a scenario where i need to implement the row level security based on a hierarchical structure. I have records with columns : loan numbers, team names, branch ,region. Each team should be able to see records only to their particular team based on team names, branch managers should see records based on the branch and regional managers should have access only to their region based on the region. Could you please help me in achieving this. I was able to achieve the filter based on the teams or roles. But I still need to work on the 2 other parts i.e., branch and region. Any kind of help would be greatly appreciated!!

March 23, 2017 4:05 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement