By using Row Level Security(RLS) you can filter and block access to individual rows within a table based on some custom logic that you write inside a predicate function.
A predicate function is an ordinary inline table-valued function(TVF) which SQL Server executes against every DML operation on the table.Your predicate function typically compare the current user context with some column value in each and return yes or no if that row is accessible.You can apply the predicate function to the table by creating a security policy and SQL Server immediately filters in the case of
select or blocks in the case of insert,update,delete.You can easily turn on and off at any time for any table.There are two types of predicates
- Filter Predicates
- Block Predicate
You can apply filter predicates on any select, update ot delete statement so that rows deemed inaccessible to the user are not processed by the select , update or delete operation.
This predicate is more like triggers, preventing the user from inserting , updating or deleting rows that violate the rules of the predicate function. there are variations for predicate function
AFTER INSERT, AFTER UPDATE
Can’t insert or update rows to values that would violate the predicate
BEFORE UPDATE, BEFORE DELETE
Can’t update or delete rows that violates the predicate
Security Predicate Functions
Security Predicate Functions are table valued function (TVF) which you write in T-SQL. This TVF must be a schema bound which means that all object references, tables or other functions must be fully qualified by schema name even if it’s default dbo schema.
For showing the demo I have created a sample Sales table where with orders where we want each salesperson to access only the orders in the table they are assigned.Means each person should have private access to their owns orders and none of the sales person can access to other salesperson orders.So we will grant read only access to their orders and only one admin user has read and write access to all the rows in a table.
Create a user, this could be any database user whether that user is a SQL server login or windows authentication login.Here I am created the users without any login
Create an sec schema
You can see username parameter which map to the username column when we create the security policy below. In the above function you can see Table return WITH SCHEMABINDING which is mandatory for predicate functions.
Let’s apply security policy on sales table for testing the predicate function created. for that I am running predicate function as User1
When I select the above predicate function you can see a permission error . The SELECT permission was denied on the object ‘fn_securitypredicate’, database ‘SampleDB’, schema ‘sec’. which is expected because we will not allow the normal users to execute the table valued function selecting from directly. For testing i am giving a temporary grant select permission to the User1.
Execute the select statement again.Now you can see predicate function return 1
I am again executing the function by passing as User2 and you can see that no result is returned meaning that rows with User2 in the sales Username column are inaccessible to queries executed by User1
Let’s test the Table Valued Function for the Admin User. For that I am going to REVERT to default dbo user and removing the select permission from the TVF that we added temporarily before
which will return 1
Let’s create a new Security policy with Filter Predicate we have created before
Let’s select the records from sales table and you can see no records return because Now it’s run under default dbo user.
SELECT * FROM Sales
Let’s execute as User1 and you can see that three records are showing corresponding to the user1
EXECUTE AS USER=’User1′
Select * from Sales
You can download the source from here