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/.
Load comments