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.
I test the select with my 2 users.
In a Select *, it is logic with permissions to have an error for my 2 logins.
When I select the good columns, no problems.
A best practice is to create a separate schema for RLS objects.
I 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.
The second step is to create the security policy associated to the function.
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:
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:
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:
The only way that I have found is to drop the policy before the change…
I run all select and the result is what I expected
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:
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