Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I realized recently that I hadn’t really blogged about Row-Level Security, so this post covers some of the things I know at a high level.
What is Row-Level Security?
This was a feature added to SQL Server in SQL Server 2016 that makes it easy to grant access to rows of data based on some characteristic of a user. At a high level, this means:
- I have something that segregates rows of data, like a CustomerID as a column in an Orders table.
- I want a customer to only be able to view their orders, those associated with their customer ID.
- This has to work, even if they didn’t use a WHERE clause and did a SELECT *.
- In this case, a user for CustomerID 4 would only see Orders that had CustomerID=4 in those rows.
We used to be able to do this with views, but this was cumbersome, and it was obfuscation. There was no security mechanism that actually ensured a user logged in wouldn’t see other rows.
Row-Level Security
This was a first class security mechanism that uses security policies and functions to control access. The way this works is as follows.
We create a function that is a table-valued function which takes a parameter(s) from a column(s) and returns a 1 if the user should view a row. In this case, we would use a WHERE clause in the query in the function that looks for Orders.CustomerID = @CustomerID.
We bind this function in a security policy that binds the function to the table, and specifies the column (or columns) used as parameters to the function. We also specify the predicate involved. There are two types:
- Filter predicates – limit read access
- Blog predicates – limit write (insert/update/delete) access
We give permissions to the function to users.
Does it Work?
Yes. It works very well from a security standpoint. Since we are tying this to users or logins, the performance of determining if the user or login has access can be slow. The IS_ROLEMEMBER() and similar functions are not super efficient and you can have performance issues across millions of rows.
However, it works.
I’ll write more in the future on the details.
SQL New Blogger
I was watching a presentation recently on this topic. I’ve written about this for SQL Server Central, but when I checked, I hadn’t really done much blogging on it.
Here I’m re-using knowledge, but in a basic way. I took 15 minutes to write a high level description. I’ll do a few more posts that demo setting this up for reads, one for writes, maybe one to get around how this might have a hole for security purposes. At least 3 more posts.
You could learn this and blog 3-4 times about what you learn and how to set up it up situations.