Row-Level Security

Row-level security (RLS) gives database administrators and developers the ability to allow fine-grained access control over rows within tables. Rows can be filtered based on the execution context of a query. Central to this feature is the concept of a security policy where, via an inline table-valued function, you would write your filtering logic to control access with complete transparency to the application. Real-world examples include situations in which you would like to prevent unauthorized access to certain rows for specific logins, for example, only giving access to a super-user to view all rows within a sensitive table and allowing other users to see rows that only the super-user should see. The following example shows how simple it is to implement RLS via T-SQL. At a high level, access to a specific table called rls.All_Patient is defined by a column called GroupAccessLevel, which is mapped to two SQL logins called GlobalManager and General. As you can imagine, the General login will not be able to view the data that GlobalManager is authorized to see.

The following code is the T-SQL required to create the table-value function and the security policy with the state set to ON:

CREATE FUNCTION rls.fn_securitypredicate(@GroupAccessLevel AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @GroupAccessLevel = USER_NAME() OR USER_NAME() = 'GlobalManager'; 
GO
CREATE SECURITY POLICY UserFilter  
ADD FILTER PREDICATE RLS.fn_securitypredicate(GroupAccessLevel)
ON rls.All_Patient
WITH (STATE = ON);  
GO
GRANT SELECT ON RLS.fn_securitypredicate TO GlobalManager;  
GRANT SELECT ON RLS.fn_securitypredicate TO General  

Running the code as the GlobalManager user will return all rows within the table, in contrast with the General user, who will only see the rows that they are entitled to see:

EXECUTE AS USER = 'GlobalManager';  
  SELECT * FROM rls.All_Patient 
  ORDER BY AreaID
REVERT;

The following screenshot confirms the data that the General user can only see:

Figure 2.26: A table with access set to GlobalManager

Executing the following code switches the execution context to the General user:

EXECUTE AS USER = 'General';  
  SELECT * FROM rls.All_Patient 
  ORDER BY AreaID
REVERT;

Figure 2.27: The same table with access set to General

If you check the properties of the clustered index scan, you will see the predicate being evaluated:

Figure 2.28: Selecting properties of clustered index scan

Figure 2.29: The predicate evaulation dialog box

This type of predicate is called a filter predicate, but you also can create a block predicate to explicitly block write operations (such as AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE) that violate the predicate.

For administration purposes, you can query the following system tables to see the security policies and security predicates that have been defined:

SELECT * FROM sys.security_policies

Figure 2.30: Output of a security_policies query

SELECT * FROM sys.security_predicates

Figure 2.31: Output of a security_predicates query

To maintain the best performance, it is best practice to not involve many table joins within the predicate function, to avoid type conversions, and to avoid recursion.