Why “also” in my title? On 29 August, my colleague Daniel Westermann wrote an article about Row Level Security in the next version of PostgreSQL.

Row Level Security called also RLS is a new feature in SQL Server 2016 and of course in SQL Azure.
This new security in SQL Server returns only rows that the user has permission to access.

The best way to understand is with a little sample and I use Daniel’s example.
I create a database RLS_TEST and a table t with an id, a name and a salary columns.
I create also 2 SQL logins u1 and u2 with specific permissions on columns.

RLS05 I test the select with my 2 users.
Select with users u1 and u2

In a Select *, it is logic with permissions to have an error for my 2 logins.
When I select the good columns, no problems.
Select with users u1 and u2 with named columns

A best practice is to create a separate schema for RLS objects.
I create the schema “rls”.
create the schema rls

Like Daniel, I will a filter with the condition: if the user is “u2” and this user is selecting from “t” then only let the user “u2” see rows that have a salary less than 3000.

The first step is to create a function with these conditions and I add a condition for the db_owner role to be sure to see all rows as owner.
new function for security policy

The second step is to create the security policy associated to the function.
Create Security Policy

You can see in the Security folder from the database with SSMS, a new folder Security Policies with this policy.
By default, when you create the policy, its status is enable.

I launch select from dbo, u1 and u2 sides and see the result:
select with security policy

You can notice that for u2, we have 1 row like my filter is set.
The second point is for u1, I have nothing.
To have the select with the user u1, I need to add another condition like USER_NAME()=’u1′.
But I will also filter with the condition: if the user is “u1” and this user is selecting from “t” then only let the user “u1” see rows that have the name name1 and name2.

I try to alter my function to add this condition:
Alter the function

As you can see, I receive the error message:
Msg 3729, Level 16, State 3, Procedure fn_salarypredicate, Line 4
Cannot ALTER ‘rls.fn_salarypredicate’ because it is being referenced by object ‘SalarySecurityPolicy’.

The function is linked to the policy.

I try with a DISABLE STATE for the Policy, but I have the same message:
Alter the function with DISABLE STATE for the Policy

The only way that I have found is to drop the policy before the change…
Alter the function with DROP Policy

I run all select and the result is what I expected
Select with the new policy

Daniel indicate that in PostgreSQL, you have a new system information function “row level security active”. In SQL server, you have also new system views:

new DMV Policy
Conclusion: Same as Daniel 😉 … It is really a great feature from SQL Server 2016 and useful in case of sensible data like salaries or medicine information.
More information about Row Level Security on msdn