This is part 1 of my Row Level Security blogs for my upcoming book project.
Row Level Security is a very interesting feature. Unlike Dynamic Data Masking (link), which looks interesting on it’s face, once you dig in the utilization of the feature looks to be limited by how granular you can make the security work. Row Level Security on the other hand is very configurable, and looks mainly to be limited by the possible performance limitations (and there may be a method or two to attack it, but I haven’t been able to make them work in CTP 3.2 yet)
For the simple case, we will create a simple table valued user defined function (TVF) that returns either a single row with 1, or no rows. 1 means to show the row, no row the opposite. The parameters will be mapped to 1 or more columns as input. This means that for every row that is to be output to the user from any queries, this function will be executed. If this sounds like something that is generally considered a bad idea, it is. But to be fair, 1: this is a simple TVF, so it is more or less like a subquery and 2: other methods of implementing row level security suffer from common issues.
In the next (at least) three blogs, I will cover row level security in some detail, with examples of several ways it can be used. Later in my experimentation with features for the book, I will attempt to use the feature with a very large dataset for performance testing, but I want to wait until after RTM for that exercise. (Or let’s be real, Aaron Bertrand or another of my MVP friends will probably have beaten me to the subject, which will be easier for me anyhow!)
The following is the version of SQL Server I used for this series of blogs until I state otherwise:
select @@version
Microsoft SQL Server 2016 (CTP3.2) – 13.0.900.73 (X64)
Dec 10 2015 18:49:31
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
To get started you will need to go to a SQL Server 2016 database (I am using the same one I created for the Dynamic Data Masking example) and we are going to drop and create 3 login-less users (in real scenarios, use roles), so we know they are starting fresh security wise:
DROP USER IF EXISTS BigHat; –DROP IF EXISTS works with users as well as objects:
CREATE USER BigHat WITHOUT LOGIN;
DROP USER IF EXISTS MedHat –MediumHat, which we want to get all of SmallHat’s rights, but not BigHats
CREATE USER MedHat WITHOUT LOGIN;
DROP USER IF EXISTS SmallHat — gets a minimal amount of security
CREATE USER SmallHat WITHOUT LOGIN;
Next, the rowLevelSecurity function are best if placed in their own schema (for security purposes, so if you give a user access to a user schema, they won’t have access to this schema… This paradigm fails if you use db_datareader and db_datawriter, particularly as later we will be adding some tables to the schema I will be creating when we get to the more “creative” solutions.) I will make a very obviously named schema:
CREATE SCHEMA rowLevelSecurity;
Now we create a function that will be used to say, for a given row, should the user be able to see a row. This function will later be applied to one or more configuration of security policies.
For the tables we will create for the demos, I will include a “ManagedByUser” column, which will hold the database user name of the user. This will be the parameter passed in to my function.
The predicate for the function needs to determine:
- Bighat user sees all
- SmallHat user sees only rows where ManagedByUser = ‘SmallHat’ (we wll expand this in later blogs, but we are starting here
- MedHat sees rows where ManagedByUser = ‘MedHat’ or the username <> ‘BigHat’, allowing for other low rights user to be createdin the future
So I will create the following function:
CREATE FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
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() = ‘BigHat’)); –Or the user is the BigHat person, they can see everything
You don’t need to give the user rights to the function, but in order to test the function, I will give the user access temporarily
GRANT SELECT ON rowLevelSecurity.ManagedByUser$SecurityPredicate TO PUBLIC; –testing only
Now, we can test the function in the security context of each user
EXECUTE AS USER = ‘smallHat’;
GO
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘BigHat’);
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘MedHat’);
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘SmallHat’);
GO
REVERT;
Returns Nothing, Nothing, 1; indicating that the user would be able to SmallHat rows only
EXECUTE AS USER = ‘medHat’;
GO
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘BigHat’);
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘MedHat’);
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘SmallHat’);
GO
REVERT;
Returns Nothing, 1, 1; indicating that the user would be able to SmallHat and MedHat rows, but not BigHat’s
EXECUTE AS USER = ‘bigHat’;
GO
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘BigHat’);
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘MedHat’);
SELECT * FROM rowLevelSecurity.ManagedByUser$SecurityPredicate(‘SmallHat’);
GO
REVERT;
Returns 1, 1, 1; indicating that the user would be able to see all rows. So let’s remove the rights on the function, and create our sample table
REVOKE SELECT ON rowLevelSecurity.ManagedByUser$SecurityPredicate TO PUBLIC; –was for testing only
Just a very simple table, and grant select rights to our three users:
/* create the schema if you don’t have it yet:
CREATE SCHEMA Demo;
*/
CREATE TABLE Demo.SaleItem
(
SaleItemId int CONSTRAINT PKSaleIitem PRIMARY KEY,
ManagedByUser nvarchar(15) –more typically would be sysname, but nvarchar(15) is easier to format for testing
)
INSERT INTO Demo.SaleItem
VALUES (1,’BigHat’),(2,’BigHat’),(3,’MedHat’),(4,’MedHat’),(5,’SmallHat’),(6,’SmallHat’);
GO
GRANT SELECT ON Demo.SaleItem TO SmallHat, MedHat, BigHat;
At this point, each of these users can see every row in the table. We are going to change that quick.
We are going to create a SECURITY POLICY object (note that it is schema owned), with one FILTER PREDICATE. This is used to filter read access to rows. In he next blog in the series, we will see the way to protect against writes and deletes to certain rows as well.
–simple, data viewing filter
CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem
WITH (STATE = ON); –go ahead and make it apply
Note that you can have other predicates on the same table that we will discuss in the next blog, as well as predicates on different table in the same security policy. Whether you want to do that will likely depend on what kind of control you want to be able to turn off a policy for some purpose, and how you manage your DDL. If I discover there to be some particular value in either direction, I will update this blog.
You can only have one enabled filter predicate per table. If you try to put another in the same policy you get either message, depending on if you try to create two in the same policy, or different policies:
Msg 33262, Level 16, State 1, Line 146
A FILTER predicate for the same operation has already been defined on table ‘Demo.SaleItem’ in the security policy ‘rowLevelSecurity.Demo_SaleItem_SecurityPolicy’.
Msg 33264, Level 16, State 1, Line 146
The security policy ‘rowLevelSecurity.Demo_SaleItem_SecurityPolicy2’ cannot be enabled with a predicate on table ‘Demo.SaleItem’. Table ‘Demo.SaleItem’ is already referenced by the enabled security policy ‘rowLevelSecurity.Demo_SaleItem_SecurityPolicy’.
Next, let’s test our predicates by executing select statements on the table we have created in the context of our three users:
EXECUTE AS USER = ‘SmallHat’;
GO
SELECT * FROM Demo.SaleItem;
GO
REVERT;
Which returns:
SaleItemId ManagedByUser
———– —————
5 SmallHat
6 SmallHat
Which makes sense to our desired outcome based on the business rules. Next, for MedHat:
EXECUTE AS USER = ‘MedHat’;
GO
SELECT * FROM Demo.SaleItem;
GO
REVERT;
All of MedHat rows, and SmallHat:
SaleItemId ManagedByUser
———– —————
3 MedHat
4 MedHat
5 SmallHat
6 SmallHat
And finally, the BigHat:
EXECUTE AS USER = ‘BigHat’;
GO
SELECT * FROM Demo.SaleItem;
GO
REVERT;
SaleItemId ManagedByUser
———– —————
1 BigHat
2 BigHat
3 MedHat
4 MedHat
5 SmallHat
6 SmallHat
Awesome, now, let’s compare this to the contents of the table as the dbo sees it:
SELECT * FROM Demo.SaleItem;
Returns:
SaleItemId ManagedByUser
———– —————
Well, that was less successful than I kind of expected. As the sa/dbo, I can’t see any of the data. This runs counter-intuitive to the common practice/thinking that these users/admin roles are not subject to security. For MOST cases, the DBA will want to include the dbo user in the TVF that you base your functions on. I will change this now for our demos:
DROP SECURITY POLICY IF EXISTS rowLevelSecurity.Demo_SaleItem_SecurityPolicy; –if exists helps when debugging!
go
ALTER FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
WHERE @ManagedByUser = USER_NAME()
OR (USER_NAME() = ‘MedHat’ and @ManagedByUser <> ‘BigHat’)
OR (USER_NAME() IN (‘BigHat’,’dbo’))); –give ‘dbo’ full rights
GO
CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem
WITH (STATE = ON);
NOW we compare our output with the output of the BigHat query:
SELECT * FROM Demo.SaleItem;
And we get the data happiness we expected:
SaleItemId ManagedByUser
———– —————
1 BigHat
2 BigHat
3 MedHat
4 MedHat
5 SmallHat
6 SmallHat
For the final read operation demonstration, lets see how it works from a stored procedure.
CREATE PROCEDURE Demo.SaleItem$select
AS
SET NOCOUNT ON;
SELECT USER_NAME(); –Show the userName so we can see the context
SELECT * FROM Demo.SaleItem;
GO
GRANT EXECUTE ON Demo.SaleItem$select to SmallHat, MedHat, BigHat;
Now execute the procedure as the different users (I am only going to include SmallHat to avoid being over repetitive, try it out for yourself)
EXECUTE AS USER = ‘SmallHat’;
GO
EXEC Demo.SaleItem$select;
GO
REVERT;
Which returns:
—————————–
SmallHat
SaleItemId ManagedByUser
———– —————
5 SmallHat
6 SmallHat
This shows us that the row level security works as expected without ownership chaining coming into place for the selection of rows, but it does come into play for running the TVF that determines which rows can be seen. In a later entry in this series, I will show how you can use a table in the function if you can’t simply code the query to just use system functions.
So how could you override it? Just like in the Dynamic Data Masking examples, in the procedure code, use WITH EXECUTE AS to elevate to a different users rights (and we will see some other possible solutions later as well in the third entry where I will show some methods of using values other than the simple system functions.)
ALTER PROCEDURE Demo.SaleItem$select
WITH EXECUTE AS ‘BigHat’ –use a similar user/role, and avoid dbo/owner if possible to avoid security holes.
AS
SET NOCOUNT ON;
SELECT USER_NAME();
SELECT * FROM Demo.SaleItem;
Now execute this and note the output:
EXECUTE AS USER = ‘smallHat’
go
EXEC Demo.SaleItem$select
GO
REVERT
Not only did you get the elevated rights of the user for objects they own, you now look like that user to the USER_NAME() function which is good for this example.
———————————-
BigHat
SaleItemId ManagedByUser
———– —————
1 BigHat
2 BigHat
3 MedHat
4 MedHat
5 SmallHat
6 SmallHat
The downside here is that if you are using USER_NAME for any sort of logging purposes, this might be a bad thing. I would suggest that if you are using the user’s context for logging stuff like RowLastModifiedByUser, to consider using ORIGINAL_LOGIN(), which will always return the server principal that the user attached to the server with.
In the next entry, I will be covering the predicates whcih apply to the INSERT, UPDATE, and DELETE stements called BLOCK PREDICATES; which block write actions from occurring on the objects in the security
policy.
If you can’t wait and want to see some of where my material came from (along with blind experimenation!) Here are several links I found useful in writiing about this subject if you can’t wait until part 2 (and why should you?):
Links:
http://www.infoq.com/news/2015/06/SQL-Server-Row-Level-Security
https://msdn.microsoft.com/en-us/library/dn765135.aspx
https://www.mssqltips.com/sqlservertip/4094/phase-out-contextinfo-in-sql-server-2016-with-sessioncontext/
https://www.mssqltips.com/sqlservertip/4004/sql-server-2016-row-level-security-introduction/
Load comments