Row Level Security in SQL Server 2016

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.

 

So let us take an example of Row Level Security that how it works:

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_Test
  GO
   
  USE Row_Level_Test
  GO
   
  CREATE USER Mike WITHOUT LOGIN;
  CREATE USER Nick WITHOUT LOGIN;
  GO
  Now 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
  )
  GO
   
  INSERT  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)
  GO
   
  GRANT SELECT ON dbo. DepartmentSales TO Mike
  GRANT SELECT ON dbo. DepartmentSales TO Nick
  GO
   
  Now create a filter predicate function as below:
   
  CREATE FUNCTION dbo.Row_Level_Predicate (@Employee as sysname)
  RETURNS TABLE
  WITH SCHEMABINDING
  AS
  RETURN SELECT 1 AS Row_Level_Predicate_Result
  WHERE @Employee = USER_NAME();
  GO
  This 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 UserFilter
  ADD FILTER PREDICATE dbo. Row_Level_Predicate(Employee)
  ON dbo. DepartmentSales
  WITH (STATE = ON);
  GO
   
   Now 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