Do you need to limit the specific table data that users can see? Row-level security allows you to filter rows based on a number of factors.
- [Narrator] Row-level security is a long-requested feature in SQL Server, and Microsoft finally introduced it with SQL Server 2016, and this feature continues on into SQL Server 2017. It's a feature that's available on all additions of the product, whether you're using standard edition Enterprise or Express. And it meets a lot of business needs, for a lot of customers. So there are a number of, kind of, classic scenarios we talk about when we talk about row-level security. One would be, when you have all of your customers in the same table and the customers need to be able to report against that table, without seeing data from the other customers.
Another scenario that's a little bit more focused would be a nurse. Maybe that nurse is only able to see the medical records of the patients that he or she is managing. These are just a couple of examples. There are a lot more that you can probably think of within your own applications. So row level security allows you to configure tables, so that users can only see the rows that they have been granted access to. This is based on a predicate function, and it limits the rows, which are returned to the user, regardless of which application they're using.
So if you think about it like a normal query, it adds an additional where clause to the query that does the filter predicate. And that filter predicate silently filters the rows, that are accessible by the user and this effects insert, updates and delete statements and not just selects. And you can have the block predicates prevent the user from writing data so you can do after insert, after update or before update and before delete. This block predicate will return an error into your application indicating that the user is attempting to modify rows to which they don't have access.
In the implementation methodology, which we'll talk about a little more in detail in the next slide, is you create the inline table function, that identifies the rows that are accessible to the users, and the function can be as simple or as complex as you need, then you use a security policy to apply that inline function, to one or more tables. You wanna make sure you're keeping those kind of simple because, like I mentioned, you're adding another where cause and if you make it too complex, it can cause your performance to go down. So the method where the users connect to the database, determines how you will need to write the in-line table function.
So you choose to write it, to scan on application, or have it match log ins. And you create the security policy that bonds to the function. One of the other things you will have to do, is, you will have to modify the application to set the session context to use a data base value that sets the row level filtering, as you'll see in the demo in the next video. Either way, you create a row level filtering, in-line table function, you do have to enable schema binding and the function must return a column that contains a valid value when the user can view the row.
- Designing row-level security
- Using views to implement row-level security in SQL Server pre-2016
- Data encryption
- Configuring Always Encrypted
- Dynamic data masking