At least once in every DBA career comes the challenge of Access Control done within the RDBMS. With Row Level Security, the fine-grained access control for scenarios requiring centralized security logic comes built-in. However for many scenarios the SQL 2016 implementation maintains application transparency, requiring no changes to queries! This video shows how to configure RLS for tables and the protection it offers without complicating application query code.
- [Instructor] Every dbi comes across a scenario where you have to filter which users can see which rows in particular tables and in the past, that meant elaborate views and stored procedures. With SQL Server 2016 though, you have row-level security, which is fine-grained access control, which is completely transparent to client applications. And it's applied at the table level without having to implement your own custom stored procedures to make it work for insert, update, and delete. Let's see how this works. We have the concept of a security policy, which will be applied to our table.
And we have the idea of a table-valued function, which will determine which rows should be visible to which users. And it's entirely up to us to define the predicate, the filter, that determines who sees what. You can also apply a block to insert, update, and delete. But I'm going to show you the filter mechanism, which is row-level security based on input provided at the time the query runs. So starting at the top here, let's just look at our data for a moment. We have a table with a lot of locations in it.
And I'm running a query here. What I'm looking for, California, Colorado, Washington, and only waterfalls. And we're making a count, so we're counting how many waterfalls there are in each of these states. Now I'm going to create this as a view and we'll use this to test throughout our exercise. So currently, my user, the interactive user for this query, is dbo. And we're going to make it more interesting by creating three new users. So we'll have user as a manager, who should see everything.
And a user from California who should see only California data. And a user from Colorado who should see only Colorado data. That's how we determine to do our security. Of course you might have security policies for things like classification, but that will determine the following step. So we will create a schema on which we will place a function and here is our function. And this will depend entirely on how you determine to filter your data. In this case, we will filter by either username, being manager who can see everything.
Or a little bit more elaborate, if the username contains an underscore, we will check that the letters before the underscore, the two letters, the sub-string at the beginning of the username, matches the state. So remember the user should be created. If I'm the user CA_Anton, I can see California, because we'll match the state with CA. If I'm CO_Anton, we will match the state with CO, the first part of my username. So this is considered the predicate, the function that determines whether a user should see a particular row.
Function has been created and now we will attach the function, our policy. So take a look at the database itself under security. On the left hand side we have security policies, this is the security policy we just created for our table, and the policy is enforced using user defined function, table-valued function, if in filter. So those two pieces together allows us to then filter who sees what on this table. First of all, these users are new, they have no rights.
So we'll go back to the top, we'll run our select statement as dbo and we see nothing. The filter is currently being applied as user dbo, I don't match any of the criteria and so even as database owner, my username does not meet the criteria in the filter so I see nothing. We come back down to the GRANT SELECT on the table, to the manager and to those two users. So those three users now have select permissions on that table.
Doesn't change anything yet. The filter still applies so now as Manager, I'll select the COUNT from that table and you see about two point two million so the manager is seeing all of the rows in that table. I run the same select count for that table, but this time as the California user. And now we see 121,000 rows. So the select count has been filtered in terms of what it can see based on our predicate.
Similarly for Colorado, instead of 121,000, the count for Colorado only, 49,000. So you can see our query is being filtered based on who we are. And we can see a more elaborate example. So now we're looking for data from California, Colorado, and Washington. So if I execute as the manager, five for that one line. And I run my query, I see all of the results.
I'll REVERT. Now if I execute as the Anton user from California, I run the same query, now I only see California data. So the fantastic work has been done for us by the system, we do not need to keep track of who's running the query. The predicate will be applied at all times and our users only see what our predicate allows them to see. Now let me GRANT SELECT ON a view to these two users and we'll run the SELECT FROM the view as the manager.
We see all of the data. And we'll run as the CA user and we only see California data. The underlying table enforces the predicate in terms of who can see what, however the view is on top of the table so the view is still subject to the filter being applied to the table itself. This greatly simplifies row-level security for the dba's, significantly reduce the number of stored procedures and views that you have create if you filter based on predicates.
- Working with the Query Store
- Temporal tables
- SQL Server on Linux
- Row-level security
- JSON support
- Operational analytics