{"id":5688,"date":"2015-09-29T16:00:36","date_gmt":"2015-09-29T14:00:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/row-level-security-rls-is-also-coming-to-ms-sql-server\/"},"modified":"2015-09-29T16:00:36","modified_gmt":"2015-09-29T14:00:36","slug":"row-level-security-rls-is-also-coming-to-ms-sql-server","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/row-level-security-rls-is-also-coming-to-ms-sql-server\/","title":{"rendered":"Row Level Security (RLS) is also coming to MS SQL Server"},"content":{"rendered":"

Why \u201calso\u201d in my title? On 29 August, my colleague Daniel Westermann<\/a> wrote an article about Row Level Security in the next version of PostgreSQL<\/a>.<\/p>\n

Row Level Security called also RLS is a new feature in SQL Server 2016 and of course in SQL Azure.
\nThis new security in SQL Server returns only rows that the user has permission to access.<\/p>\n

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

\"RLS05\"<\/a> I test the select with my 2 users.
\n\"Select<\/a><\/p>\n

In a Select *, it is logic with permissions to have an error for my 2 logins.
\nWhen I select the good columns, no problems.
\n\"Select<\/a><\/p>\n

A best practice is to create a separate schema for RLS objects.
\nI create the schema “rls”.
\n\"create<\/a><\/p>\n

Like Daniel, I will a filter with the condition: if the user is \u201cu2\u201d and this user is selecting from \u201ct\u201d then only let the user \u201cu2\u201d see rows that have a salary less than 3000.<\/p>\n

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.
\n\"new<\/a><\/p>\n

The second step is to create the security policy associated to the function.
\n\"Create<\/a><\/p>\n

You can see in the Security folder from the database with SSMS, a new folder Security Policies with this policy.
\nBy default, when you create the policy, its status is enable.<\/p>\n

I launch select from dbo, u1 and u2 sides and see the result:
\n\"select<\/a><\/p>\n

You can notice that for u2, we have 1 row like my filter is set.
\nThe second point is for u1, I have nothing.
\nTo have the select with the user u1, I need to add another condition like USER_NAME()=’u1′.
\nBut I will also filter with the condition: if the user is \u201cu1\u201d and this user is selecting from \u201ct\u201d then only let the user \u201cu1\u201d see rows that have the name name1 and name2.<\/p>\n

I try to alter my function to add this condition:
\n\"Alter<\/a><\/p>\n

As you can see, I receive the error message:
\nMsg 3729, Level 16, State 3, Procedure fn_salarypredicate, Line 4
\nCannot ALTER ‘rls.fn_salarypredicate’ because it is being referenced by object ‘SalarySecurityPolicy’.<\/span>
\nThe function is linked to the policy.<\/p>\n

I try with a DISABLE STATE for the Policy, but I have the same message:
\n\"Alter<\/a><\/p>\n

The only way that I have found is to drop the policy before the change…
\n\"Alter<\/a><\/p>\n

I run all select and the result is what I expected
\n\"Select<\/a><\/p>\n

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:<\/p>\n