Keeping Data Safe: Row-Level Security

In this tip, we want to go over the fundamentals of Row-Level-Security for a database table. Don’t let your data be vulnerable to data breaches. It makes sense to secure the data all the way down to the row level. Common real-world examples include multi-tenant applications, sensitive data, and data that could be broken down into territories or regions.

How Do Does Row-Level Security Work?

This is a great question. It works off of a table value function to layout the security check and a policy that implements the security function for a table.

Let’s check it out! First, we will create two tables. The Sales table that holds the sales data and the SalesReps table which holds the Many to Many relationships on who can see whos sales. In this example, the Manager will see all rows, SalesLead will see SalesLead and Sales1 rows.

Note: In this example by default, Sales3 won’t be able to see its own data. Neither would sysadmin or database owner. Therefore you will need to make sure your security function allows a failsafe for seeing data for the people who should be able to touch all the data for the table.

USE [master]
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name LIKE 'RLS_Demo')
BEGIN
	DROP DATABASE RLS_Demo
END

CREATE DATABASE RLS_Demo
GO

USE [RLS_Demo]
GO

/* Create database users for testing */
CREATE USER Manager   WITHOUT LOGIN;  
CREATE USER SalesLead WITHOUT LOGIN;  
CREATE USER Sales2    WITHOUT LOGIN; 
CREATE USER Sales3    WITHOUT LOGIN; 

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(30),  
    Qty int  
    ); 
	
	/* Define who can more than one SalesRep's orders */
CREATE TABLE SalesResp
( SalesRepLead sysname,
  SalesRep sysname,
  PRIMARY KEY (SalesRepLead, SalesRep)
  ) 

  /*Sales1 is TeamLead*/
  INSERT INTO SalesResp (SalesRepLead, SalesRep)
  VALUES ('SalesLead', 'SalesLead')
    INSERT INTO SalesResp (SalesRepLead, SalesRep)
  VALUES ('SalesLead', 'Sales2')
      INSERT INTO SalesResp (SalesRepLead, SalesRep)
  VALUES ('Sales2', 'Sales2')

  /* Note no records for Sales3 in our Many to Many lookup */

INSERT INTO Sales VALUES (1, 'SalesLead', 'Pirates Hat', 5);
INSERT INTO Sales VALUES (2, 'SalesLead', 'Terrible Towel', 2);
INSERT INTO Sales VALUES (3, 'SalesLead', 'Clemente Jersey', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Pirates Hat', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Clemente Jersey', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Terrible Towel', 5);
INSERT INTO Sales VALUES (4, 'Sales3', 'Pirates Hat', 1);
INSERT INTO Sales VALUES (5, 'Sales3', 'Clemente Jersey', 1);
INSERT INTO Sales VALUES (6, 'Sales3', 'Terrible Towel', 2);

GRANT SELECT ON Sales TO Manager;  
GRANT SELECT ON Sales TO SalesLead;  
GRANT SELECT ON Sales TO Sales2;  
GRANT SELECT ON Sales TO Sales3; 
/* RLS not in place. Should see all 9 rows */
SELECT * FROM Sales;

Now let’s look at the meat and potatoes of Row-Level Security. This would be the function and the policy that bounds the row-level security to a table.

/* Keep all your Row Level Security in its own securable schema */
CREATE SCHEMA Security;  
GO  
/* Note no failsave for sysadmin and Sales3 doesn't exist in SalesResp table either */ 
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep IN (SELECT SalesRep FROM dbo.SalesResp WHERE SalesRepLead = USER_NAME() )
OR USER_NAME() = 'Manager';  

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales  
WITH (STATE = ON);  

GRANT SELECT ON security.fn_securitypredicate TO Manager;  
GRANT SELECT ON security.fn_securitypredicate TO SalesLead;  
GRANT SELECT ON security.fn_securitypredicate TO Sales2;  

You should notice there is no logic to help sysadmin users see the data in the table. Therefore just like Sales3 now any sysadmin wouldn’t see any data.

/* DB owner shows no rows due to RLS */
EXECUTE AS USER = 'dbo'
SELECT * FROM Sales;
REVERT; 

EXECUTE AS USER = 'SalesLead';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;
REVERT;  

/* Notice no rows as security function returns zero rows from lookup table */
EXECUTE AS USER = 'Sales3';  
SELECT * FROM Sales;
REVERT; 

In this scenario, to allow Sales3 to see its own sales data we just need to insert a record into the SalesReps table as shown below and we are good to go.

 INSERT INTO SalesResp (SalesRepLead, SalesRep)
 VALUES ('Sales3', 'Sales3')

EXECUTE AS USER = 'Sales3';  
SELECT * FROM Sales;
REVERT; 

Now if you want to remove row-level security or make some modifications to the function used in the policy all you have to do is drop the security policy then the function.

/* Clean up */
DROP SECURITY POLICY SalesFilter 
DROP FUNCTION Security.fn_securitypredicate

Your Homework!

Figure how to implement row-level security to benefit your business! Also, go ahead and figure out how to add a failsafe so sysadmins can still see all the data like they normally would.

Got more Row-Level Security Questions?