Implementing “Row Level Security” (RLS) with “Filter Predicate” in SQL Server 2016 – Part 1
For an Organization its Data is the most important thing, and the Org’s IT department takes lot of measures to make sure data does not fall in wrong hands. The DBA’s and Application programmers setup different layers of security on top of data so that the user is only able to see the Filtered data/rows from a table that he has access to. DB programmers normally create abstracted Views or Stored Procedures with complex logic on top of the Tables by using other master-mapping tables that contains the user-data access key. Sometimes these security logics are not upto the standards, and may have various versions on a database and thus it becomes difficult to track, update and make them fail-proof. So, with SQL Server 2016 the new Row Level Security feature is going to handle this Out of the Box and as a Standard.
RLS or Row Level Security is a feature that enables fine grained control over access to rows in a table, allowing you to easily control which users can access which data with complete transparency to the application.
With this feature rows are filtered based on the Execution Context of the Query, rather than the current user access rights. A secure logic can be created to determine which user can see which rows and restrict any kind of data (rows) by designing a flexible and robust Security policy for a table.
–> To setup Row Level Security (RLS) on a particular table it needs only these simple steps:
1. Create Users and Grant Read/SELECT access for a Particular table.
2. Create a new Inline Table-Valued Function that will contain the Filter Predicate for that table. This Function Predicate can be a sophisticated business logic with multiple JOINs or a simple WHERE ManagerCode = ‘userHR’.
3. Create a new Security Policy for this table and add the above Function (Filter) Predicate to it.
Please note: that these Functions & Security Policies should be unique for a table. So to create RLS for an another table, you will need to create separate Function & Security Policy.
–> Step 1.a. Let’s create some test accounts: I will create three users for:
1. The CEO, over-all admin of the company data.
2. HR department head
3. Finance department head
CREATE USER userCEO WITHOUT LOGIN; GO CREATE USER userHR WITHOUT LOGIN; GO CREATE USER userFin WITHOUT LOGIN; GO
–> Create a sample table [dbo].[Employee]: with a self-referencing Manager ID column.
CREATE TABLE dbo.Employees ( [EmpCode] NVARCHAR(50), -- Employee ID [EmpName] NVARCHAR(250), -- Employee/Manager Full Name [Salary] MONEY, -- Fictious Salary [MgrCode] NVARCHAR(50) -- Manager ID ); GO
-> Now insert some test records:
-- Top Boss CEO INSERT INTO dbo.Employees VALUES ('userCEO' , 'CEO Top Boss' , 800, NULL) -- Next 2 levels under CEO INSERT INTO dbo.Employees VALUES ('userHR' , 'HR User' , 700, 'userCEO'); INSERT INTO dbo.Employees VALUES ('userFin' , 'Finance User' , 600, 'userCEO'); -- Employees under Kevin INSERT INTO dbo.Employees VALUES ('manojp' , 'Manoj Pandey' , 100, 'userHR'); INSERT INTO dbo.Employees VALUES ('saurabhs', 'Saurabh Sharma', 400, 'userHR'); INSERT INTO dbo.Employees VALUES ('deepakb' , 'Deepak Biswal' , 500, 'userHR'); -- Employees under Amy INSERT INTO dbo.Employees VALUES ('keshavk' , 'Keshav K' , 200, 'userFin'); INSERT INTO dbo.Employees VALUES ('viveks' , 'Vivek S' , 300, 'userFin'); GO
–> Let’s check the records before applying “Row Level Security”:
SELECT * FROM dbo.Employees; -- 8 rows GO
As a normal SEELCT and without RLS, it just ignores my Execution Context and execute the Query and return all the 8 rows.
–> The Traditional way to setup the Row Level Security till now was as follows (a simple example):
-- Stored Procedure with User-Name passed as parameter: CREATE PROCEDURE dbo.uspGetEmployeeDetails (@userAccess NVARCHAR(50)) AS BEGIN SELECT * FROM dbo.Employees WHERE [MgrCode] = @userAccess OR @userAccess = 'userCEO'; -- CEO, the admin should see all rows END GO -- Execute the SP with different parameter values: EXEC dbo.uspGetEmployeeDetails @userAccess = 'userHR' -- only 3 rows GO EXEC dbo.uspGetEmployeeDetails @userAccess = 'userFin' -- only 2 rows GO EXEC dbo.uspGetEmployeeDetails @userAccess = 'userCEO' -- all 8 rows GO
The above method is prone to issues, like SQL Injection and any other user can apply other user’s User-Name and get the information that he is not allowed to see. With this type of method you have to apply another security layer at the application level so whenever a user executes the SP it gets executed with the same user’s User-Name.
–> The new Row Level Security feature let you:
– apply this security at the database level
– and there is no need to apply the WHERE clause filter for the User-Name.
This makes the security system more reliable and robust by reducing the surface area of your security system.
–> Step 1.b. Grant Read/SELECT access on the dbo.Employee table to all 3 users:
GRANT SELECT ON dbo.Employees TO userCEO; GO GRANT SELECT ON dbo.Employees TO userHR; GO GRANT SELECT ON dbo.Employees TO userFin; GO
–> Step 2. Let’s create an Inline Table-Valued Function to write our Filter logic:
CREATE FUNCTION dbo.fn_SecurityPredicateEmployee(@mgrCode AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_SecurityPredicateEmployee_result -- Predicate logic WHERE @mgrCode = USER_NAME() OR USER_NAME() = 'userCEO'; -- CEO, the admin should see all rows GO
This function returns value 1 when:
– a row in the MgrCode (i.e. the Manager Code) column is the same as the user executing the query (@@mgrCode = USER_NAME())
– or if the user executing the query is the Top Boss user (USER_NAME() = ‘userCEO’)
–> Step 3. Create a security policy adding the function as a filter predicate:
CREATE SECURITY POLICY ManagerFilter ADD FILTER PREDICATE dbo.fn_SecurityPredicateEmployee(MgrCode) -- Filter Column from dbo.Employee table ON dbo.Employees WITH (STATE = ON); -- The state must be set to ON to enable the policy. GO
The above Security Policy takes the Filter Predicate Logic from the associated Function and applies it to the Query as a WHERE clause.
–> Now let’s again check the records after applying “Row Level Security”:
SELECT * FROM dbo.Employees; -- 0 rows, GO
The simple “SELECT *” statement will fetch me zero rows after applying RLS, as my user ID is not configured to have access to any of those rows & Table.
–> And if you check in the Execution Plan of above SELECT statement without WHERE clause, it will show you the Filter Predicate that is added by the Security Policy defined in Step #3 for applying RLS on this table, which looks like this:
[TestManDB].[dbo].[Employee].[MgrCode]=user_name()
OR user_name()=N’userCEO’
–> Let’s check the 3 users we created and provided them customized access to the dbo.Employee table and rows in it:
-- Execute as our immediate boss userHR (3 rows): EXECUTE AS USER = 'userHR'; SELECT * FROM dbo.Employees; -- 3 rows REVERT; GO -- Execute as our immediate boss userFin: EXECUTE AS USER = 'userFin'; SELECT * FROM dbo.Employees; -- 2 rows REVERT; GO -- Execute as our Top boss userCEO (8): EXECUTE AS USER = 'userCEO'; SELECT * FROM dbo.Employees; -- 8 rows REVERT; GO
–> The results of the above 3 SELECTs looks like this:
So, as you can see the three users we created resulted in different results:
– The HR & Finance users got just 3 & 2 rows, respectively.
– But the admin CEO user got all the 8 rows.
–> Thus, by using RLS Filter Predicate feature in SQL Server 2016 you can create your own customized Security by creating an Inline Table-Valued Function that is linked to the Security Policy for your Table.
–> Check Block Predicates with RLS in my [next post, Part 2].
–> Final Cleanup
DROP SECURITY POLICY [dbo].[ManagerFilter] GO DROP FUNCTION [dbo].[fn_SecurityPredicateEmployee] GO DROP TABLE [dbo].[Employee] GO DROP PROCEDURE dbo.uspGetEmployeeDetails GO
Check the same demo on YouTube:
Great article, very well explained, lucid. A new feature to take away.
thanks Keshav!
Hi Manoj,
This is definitely one of the very well written article with example on RLS. Thanks for taking time to write this.
One humble request –
There is a small typo which needs correction. Within the Stored Proc -dbo.uspGetEmployeeDetail, please make the table name as employees on line no. 6. It was throwing an error saying employee table doesn’t exist.
Thanks Suhas, for pointing it out. corrected !!!
One Question Manoj. What is the performance impact of doing this?
Well regarding perf I didn’t not see any improvement nor any significant degradation. But I think there would be scope of pref-issues depending on how you are going to query the data.
Thanks Manoj!
Very good informative stuff!
Thanks
Hi Manoj,
I can achieve the same by using View. So my question is why we are going to use row level security ? Please refer the below query for your reference .
BEGIN TRAN
CREATE TABLE Employee(Id INT,Name VARCHAR(10))
INSERT INTO Employee(Id,Name) VALUES(1,’Prasad’),(2,’Priyan’),(3,’Rajeev’)
SELECT * FROM Employee
GO
CREATE VIEW Vw_Emp
AS
SELECT * FROM dbo.Employee WHERE Name =USER_NAME()–IN(‘Prasad’,’Priyan’)
GO
INSERT INTO VW_Emp VALUES(4,’Dumas’)
GO
CREATE USer Prasad WITHOUT LOGIN
GRANT SELECT ON dbo.VW_Emp TO Prasad
GO
EXECUTE AS USER=’Prasad’
SELECT * FROM dbo.VW_Emp
REVERT;
DROP VIEW VW_Emp
DROP TABLE Employee
ROLLBACK TRAN
Thank you sir. I am a systems architect studying for 70-473. This is one of the topics of the exam. Since I do not have any DBA experience, it took me some time to understand this from the Microsoft documentation but it definitely got VERY clear after reading this in your site. Congratulations and thanks for people like you that makes other people’s life easier because the effort they put while sharing knowledge. Keep the hard work !
Thanks for the blog.
This mentions the recommendations that RLS be implemented thru a new schema. Could you update to accommodate best practice?
Great Article, How can we achieve row level security for multiple users?
How can we achieved Row Level Security with multiple users for one row
For multiple users you will have to use a security group having all similar users in that group.
Thanks for your reply!
I created security groups Admin which should have access to all the rows, also other security groups, where similar users are grouped together. In below code column Employee Group has the group name which states users in that group should access the row. I tried below code but it did not work. Any thoughts? Thanks
CREATE FUNCTION dbo.fn_SecurityPredicateEmployee (@EmployeeGroup AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
WHERE IS_MEMBER(‘Domain\Admin’) = 1 OR IS_MEMBER(‘Domain\’+@EmployeeGroup) = 1;
GO
Hi ,
Im getting below Error;
Msg 343, Level 15, State 1, Line 57
Unknown object type ‘SECURITY’ used in a CREATE, DROP, or ALTER statement.
Msg 102, Level 15, State 1, Line 58
Incorrect syntax near ‘FILTER’.
What is your SQL Server version? RLS works on 2016 and above.
Hi Manoj,
Thanks for the great article. Wondering if the same feature can be extended to lookup of multiple columns ? (i.e., for a user fetch all matching records based on several columns present in user access table)