Row Level Security in SQL Server 2016

Row Level Security (RLS) feature enables fine grained control over access to rows in a table. Access restriction is located and controlled in the database level rather than at the application tier. Any time the user access the table based on the restriction of RLS, the function is invoked and enforced by the security policy. This enables us to easily restrict the data based on the user identity or security context.


RLS supports two types of security predicates (conditions)

Filter predicates silently filter the rows available to read operations such as (SELECT, UPDATE, and DELETE). As the function is defined as an inline table-valued function, for filter predicates, there is no indication to the application that rows have been filtered from the result set.
Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE) that violate the predicate. For block predicates, any operations that violate the predicate will fail with an error.

Demo on how to configure row level security 


There are two basic steps to create row level security
      -  Create predicate function and write custom logic to control user access to every row.
      -  Create the security policy and apply it.

Step #1 Create sample database and table


Create table Customers
(
       ID int primary key not null identity (1, 1),
       FirstName varchar (20),
       LastName varchar (20),
       Product varchar (20),
       EmpSales varchar (20)
)

--Insert some data into a table
Insert into Customers
values
       ('john','dawson', 'refrigirator', 'Emp1'),
       ('bean','vic', 'computer', 'Emp1'),
       ('mac','shean', 'phone', 'Emp2'),
       ('ares','cole', 'ipad', 'Emp2'),
       ('jean','jackson', 'TV', 'Emp1'),
       ('tommy','mile', 'monitor', 'Emp3'),
       ('micheal','alfred', 'coffee_maker', 'Emp1'),
       ('lamb','tons', 'TV', 'Emp3'),
       ('colin','powell', 'laptop', 'Emp3'),
       ('white','bear', 'iwatch', 'Emp3'),
       ('messi','ronaldo', 'TV', 'Emp2')
GO

-- Verify data in a table
Select * from Customers


Step #2 Create users and assign permissions


--Create 3 employee user and 1 manager user
Use RLSDB
GO
Create user emp1 without login
Create user emp2 without login
Create user emp3 without login
Create user manager without login
GO

--Assign db owner role to manager so that he can access to all the data.
USE [RLSDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [manager]
GO

--Grant users access to the database
Grant select, update, delete, insert on customers to emp1
Grant select, update, delete, insert  on customers to emp2
Grant select, update, delete, insert on customers to emp3
GO

Step #3 Create separate schema for a function

Create an inline table valued function. Basically this function can be defined as if any user executes the query based on this table will be getting relevant data.

--Create schema security
Use RLSDB
Go
Create schema Security
Go

CREATE FUNCTION Security.fn_rowlevelsecurity(@EmpSales AS sysname) 
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
       SELECT 1 AS fn_rowlevelsecurity_result
       WHERE @EmpSales = USER_NAME()  -- it will apply filter while running the query
OR
       IS_MEMBER ('DB_OWNER') = 1
GO


Step #4 Create Security Policy or a function


Filter Predicate: It will filter the row or we can say exclude the rows which do not satisfy the predicate and stop further option like select, Update & Delete. In our example: Manager wants to restrict employee to see other employee`s sales data then in such cases we can apply filter predicate.
Block Predicate: This predicate helps in implementing policy by which insert, update and delete rows will prevent which violate the filter predicate. In other words, we can say it explicitly block write operation. In our example: If employee is not a part of the EmpSales columns that he is executing on and trying to execute something another employs data then it will prevent you from doing it resulting in a block error.

--Create a security policy
CREATE SECURITY POLICY ProductSalesFilter
ADD FILTER PREDICATE Security.fn_rowlevelsecurity(EmpSales) ON dbo.customers,
ADD BLOCK PREDICATE Security.fn_rowlevelsecurity(EmpSales) ON dbo.customers 
WITH (STATE = ON);

Step #5 Test the RLSDB with different users


--Emp1 user can view only his sales data
EXECUTE AS USER = 'emp1';
SELECT * FROM customers;
REVERT;


--Emp2 user can view only his sales data
EXECUTE AS USER = 'emp2';
SELECT * FROM customers;
REVERT;


--Emp3 user can view only his sales data
EXECUTE AS USER = 'emp3';
SELECT * FROM customers;
REVERT;


--Manager has complete control on data  sales data
EXECUTE AS USER = 'Manager';
SELECT * FROM customers;
REVERT;


--Updating or deleting rows of data not part of employee sales.
--Emp1 cannot insert, update or delete the data as laptop is someone else product
EXECUTE AS USER = 'emp1';
UPDATE dbo.CUSTOMERS SET PRODUCT = 'DESKTOP' WHERE PRODUCT = 'LAPTOP';
REVERT;
GO
EXECUTE AS USER = 'emp1';
DELETE FROM  dbo.CUSTOMERS WHERE PRODUCT = 'LAPTOP';
REVERT;


--Insert data failed due to block predicate applied on the table
EXECUTE AS USER = 'emp1';
Insert into dbo.customers values ('billy','smith','scooter','emp3')
REVERT;


Enable and Disable Security


--Disable security policy
ALTER SECURITY POLICY ProductSalesFilter
WITH (STATE = OFF)
REVERT;

--Enable security policy
ALTER SECURITY POLICY ProductSalesFilter
WITH (STATE = ON)
REVERT;


Use of RLS

  • Banks: restricting the banker to view only his or her accounts 
  • Hospitals: Restricting the Doctors AND Nurses to view only her patients

Limitations

  • RLS is incompatible with FileStream. 
  • RLS is incompatible with Polybase. 
  • Side-channel attacks might lead to users determining data values for rows for which they don’t have access. 
  • Cannot create an indexed view on top of a table that has a security policy defined. 
  • RLS is implemented via a function so Column-store Indexes batch mode might not be used.

No comments:

Post a Comment