Row Level Security in SQL Server 2016
Microsoft launched latest version of SQL Server is SQL Server 2016 with many new security features like ‘Row Level Security’, ‘Dynamic Data Masking’, ‘Always Encrypted’ and ‘Enhancement of Transparent Data Encryption’ that are helpful for secure data.
Row Level Security (RLS), which provides control over access to rows in a table based on user’s authentication, who is executing a query. RLS helps in controlling access to data easily with complete transparency for different users within an organization.
Suppose we have some critical business data and want to limit access to the data based on hierarchy of the user. We want users to see the information that is tied to their respective departments and role only. For this, Row Level Security allows an easy control with complete transparency that only user specific data will be available to access.
Row Level Security (RLS) Implementation contains two parts as:
- Creating a Predicate Function
A Predicate function is an Inline Table Valued function, which checks whether the user who is executing a particular query on the table has the permission to the rows in the table or not. It will return the value 1 for each row in the table, where the user has an access, else returns no result.
- Security Policy
A Security Policy, once created will attach the Predicate function filter to the table. This ensures that whenever a user runs some SQL commands, which references the table in the Security Policy, it runs the Predicate function as well.So, now first design a database and some users for example:CREATE DATABASE Row_Level_TestGOUSE Row_Level_TestGOCREATE USER Mike WITHOUT LOGIN;CREATE USER Nick WITHOUT LOGIN;GONow Create a table and insert data for example and grant select to the new users:CREATE TABLE dbo.DepartmentSales([Employee] NVARCHAR(10),[Dep] NVARCHAR(5),[sales] MONEY)GOINSERT INTO dbo. DepartmentSales VALUES ('Nick','Dep1',6984)INSERT INTO dbo. DepartmentSales VALUES ('Nick', 'Dep1',7845)INSERT INTO dbo. DepartmentSales VALUES ('Mike', 'Dep2',1254)INSERT INTO dbo. DepartmentSales VALUES ('Mike', 'Dep2',2354)INSERT INTO dbo. DepartmentSales VALUES ('Mike', 'Dep2',1452)INSERT INTO dbo. DepartmentSales VALUES ('Mike', 'Dep2',1120)GOGRANT SELECT ON dbo. DepartmentSales TO MikeGRANT SELECT ON dbo. DepartmentSales TO NickGONow create a filter predicate function as below:CREATE FUNCTION dbo.Row_Level_Predicate (@Employee as sysname)RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT 1 AS Row_Level_Predicate_ResultWHERE @Employee = USER_NAME();GOThis function works as the current user must have associated records in order to get any results. Notice that the functions does not have access to the rows itself.Furthermore the function can contain joins and lookup tables in the where clause.Now add a filter predicate to the table dbo. DepartmentSales:CREATE SECURITY POLICY UserFilterADD FILTER PREDICATE dbo. Row_Level_Predicate(Employee)ON dbo. DepartmentSalesWITH (STATE = ON);GONow you can test by querying data for Mike or Nick, any one of them as:EXECUTE AS USER = 'Mike';SELECT * FROM dbo. DepartmentSales;EXECUTE AS USER = 'Nick';SELECT * FROM dbo. DepartmentSales;This query will give you all rows, which are associated with only to Mike in the table. For more detail about Row-Level Security and limitations with RLS please read this MSDN document: https://msdn.microsoft.com/library/dn765131.aspx