Published on March 14th, 2015 | by Warner Chaves0
Security in Azure SQL Database: Row Level Security
Another day, yet another feature of Azure SQL Database that is NOT on the on-premises SQL Server! Oh and it looks like there’s some focus here on security, what with Dynamic Data Masking coming out and now Row Level Security. It’s an exciting time to be a DBA, new features being released cloud-first, giving us previews of what might be coming to our own data centers soon. Not only do we get a ‘preview’ look but it also allows bloggers like myself to create material way before the features come to the on-premises product so DBAs are ready when that time comes.
And obviously, if you’re an actual user of Azure SQL Database, all you need is to be on the latest V12 version and you’re ready to go!
What is it?
Row Level Security is a new way to describe the security authorization level a given user has on the data down to the individual row level. So let’s think about it this way:
Level 1: we create a login and database user to allow the user to connect to our database.
Level 2: we give that user permissions to access a table.
Level 3: we can go further and grant permissions only on some specific columns.
And here is where Row Level Security comes in:
Level 4: we can go further and grant permissions on specific ROWS of the table.
At this level, since you have control over columns and rows, you can filter out specific cells from the table if you want.
When should you use it?
The use case for this feature is filtering rows based on the users role in the application. For example, an engineer only has to read rows that belong to his particular project, an accountant only sees the rows for the accounts he’s managing, a marketing analyst only sees the data from the campaigns that’s they’re running, etc.
What about views or triggers or…. ?
Historically, this type of security has been handled through the use of views. The developers had to create different views based on different roles or they had to use application logic to pass the appropriate WHERE clauses to the view. This approach has some weaknesses though:
a) You end up repeating code when you have many different views that are very similar but only differ on the role of the user calling the view.
b) Part of your security is dependent on the application itself, instead of it being enforced directly on the database level.
Row Level Security helps in these situations by directly expressing these security limits inside the database.
Let’s see an example!
Alright, so I have an Azure SQL Database already on the V12 that contains a table called ArtistRates. This table has an ID, an Artist Name and their Rate.
I also have created a new user on this database called limitedReader and have assigned it to the db_datareader role.
When I do a SELECT * from ArtistRates, I get the results below:
Now let’s say we want to implement a new security rule:
– The limitedReader user is not going to be allowed to look at the artists that have a rate that goes over 150000. Those artists will be handled by other more experienced agents that have a different role.
So, to codify this rule we need two things:
1) A function that will give us the condition to filter out rows.
In this case we would use something like this:
CREATE FUNCTION Security.fn_RatesSecurityPredicate(@rate as int) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_RatesSecurityPredicate_result WHERE @rate <=150000 AND USER_NAME() = 'limitedReader';
This means, return a 1 (1 = TRUE = we DO want to see this row) whenever the user is the limitedReader and the rate is below or equal to 150000.
This function is the one that contains the predicate that you want to use to decide if you will show the row or not.
2) A policy to tie the function to a particular column on the table. Now that we have a function to describe our security predicate we need to tie it into the actual Table where we are interested in applying it. Note that this also means that one function could be applied to multiple tables if necessary.
In our example, we now tie it into the ArtistRates table:
CREATE SECURITY POLICY RateFilter ADD FILTER PREDICATE dbo.fn_RatesSecurityPredicate(Rate) ON dbo.ArtistRates WITH (STATE = ON);
With this policy in place we can now test out the access.
If I run again a SELECT * FROM ArtistRates using the limitedReader, this is what I get:
Note how all the artists with rates above 150000 have disappeared from the results, even though there was no predicate on the SELECT and we’re not using any other intermediate object (like a view or stored procedure or trigger) to apply the logic.
One big note: in it’s current implementation, Row Level Security will not allow SELECT, DELETE or UPDATE of values that are filtered out, however, a user can INSERT a new row that will “violate” their own predicate. This is not a problem for read-only users, but something to keep in mind for users with write permissions.
As I mentioned at the beginning, I love that we get to see all these features as they are developed and put into the cloud instead of waiting years to see what the SQL Server team is cooking up. In this particular case, I think Row Level Security comes to fill a gap on the SQL security landscape that was being filled by tedious or error prone workarounds like the ones I mentioned. DBA’s at this point can manage exactly what data they want their users to see and more secure implementations will come because of it.
Do you see any other use cases for Row Level Security that you might apply in your organization? As usual, let us know your thoughts in the comments!