HandsOn - SQL Database Row-Level Security
Few days ago we released, in preview, a new cool feature for SQL Database: row-level security. This feature opens new security patterns, particularly coupled with the upcoming data obfuscation feature. In this post I’ll show you how to get started with a viable pattern; for more details please refer to the Row-Level Security Preview page on MSDN.
First and foremost, remember this is just a preview feature so it could change in the future.
Now, what is row-level security? Basically it’s an application transparent custom row filter.
In other words you now have the power to decide which row to show right after it has been fetched from a table.
Let me show you an example. You have a table with 5 rows, as shown below:
Up until now, depending of your permission set, you are either query the table or you don’t. In other words, the recordset composition is entirely dependent of the search predicate. Since we don’t have WHERE arguments all the rows are returned. Using row-level security we can choose which row to return adding a custom filter to every query performed against our table. So, for our example, we could filter the “private” rows returning only the public rows:
The other rows are still in the database, they're just filtered.
All you have to do is provide SQL Database with a function SQL will call for each row fetched from the table. If your function returns a value SQL will not filter the row. If your function returns an empty recordset the row will be filtered out.
Simple isn't it?
In truth you have to perform two steps. Beside our filter function you have to tell SQL Database which table to filter. This is accomplished with a new statement: CREATE SECURITY POLICY. Given that security policy, SQL is able to call your function for each access to the specific table automatically.
Here https://msdn.microsoft.com/library/7221fa4e-ca4a-4d5c-9f93-1b8a4af7b9e8.aspx you can find some simple examples to get started.
Note however some important details: these filters are in query phase only. There is nothing preventing you to insert rows you would not be able to query afterwards.
From now on I'll use another, more realistic, example (full source code is available here: https://gist.github.com/MindFlavor/61bf05e357cddbeffb02). Suppose we have a table with sensitive information. Each row has a clearance level needed to retrieve it. Your users have a specific clearance level. You want to let each user retrieve only the rows he’s allowed to.
So we have a table specifying the username and the granted security level (let's call it [Sec]. [securitylevel] ):
(here GrantedLevel specifies the “security clearance” for the user). And a table like this:
(Here RequiredLevel is the minimum “security clearance” needed to fetch the row).
We have to create our custom function. Note that it must be an inline table value function. You cannot use multi statement table value functions here. This is good performance-wise. Our function should receive the required clearance level in input and, checking the current user, tell SQL if it should return the row examined.
This is easy. All we have to do is to query our [Sec].[securitylevel] table to see if the currently context user has the required security level:
CREATE FUNCTION [Sec]. [Fn_testsecuritylevel] (@RequiredSecurityLevel AS INT)
returns TABLE
WITH schemabinding
AS
RETURN
SELECT 1 AS 'Result'
FROM [Sec].[securitylevel]
WHERE ( [username] = User_name()
AND @RequiredSecurityLevel < [grantedlevel] )
OR ( User_name() = 'dbo' );go
It’s good practice to allow unrestricted access to dbo: it would have it anyway and it will only cause confusion (and don’t tell me your application relies on dbo! ;)).
With the function in place all we have to do is to create the SECURITY POLICY:
CREATE security policy [Sec].[PrivilegeBasedTableFilter] ADD filter predicate [Sec].[fn_testSecurityLevel](requiredlevel) ON [Sec].[PrivilegeBasedTable] WITH (state = ON);
Note how we linked the function to our table. Also note that the function will receive the RequiredLevel field for each row to evaluate. Think about it as an implicit CROSS APPLY function.
If we query the table (as dbo):
We receive 10 rows, 1 for each security clearance.
Now look at the execution plan: instead of a simple clustered index scan we find:
The join operator is our inline function predicate:
So you can clearly see how SQL Database injected our filtering function in the statement above.
Now if we were to create 3 users:
- VIP user with clearance level 10.
- AverageGuy with clearance level 5.
- PowerlessGuy with clearance level 1.
We could test our filtering function just changing execution context. For example PowerlessGuy will only see 1 row:
While AverageGuy will get 5 rows. Note how COUNT works consistently:
Moreover, since SQL injects our filtering function down in the execution tree, join expressions get filtered too as expected:
With this execution plan:
Happy coding,