In Transact-SQL, you can write views, stored procedures, scalar and table-valued user-defined functions, and triggers. Views serve best as a layer for selecting data, although you can modify data through views as well. Views are especially useful for column and row-level security. You can grant column permissions directly; however, doing this means a lot of administrative work. You can create a view as a projection on the base table with selected columns only, and then maintain permissions on a higher granularity level (that is, on the view instead of on the columns). In addition, you cannot give row-level permissions through a predicate in the GRANT statement. Of course, you can use the same predicate in the WHERE clause of the SELECT statement of the view you are using as a security layer. You can use table-valued functions as parameterized views.

Stored procedures are appropriate for all update activity, and also for querying data. Maintaining security through stored procedures is the easiest method of administration; with stored procedures, you typically need to grant the EXECUTE permission only. You can use triggers and scalar functions for advanced security checking; for example, for validating users' input.

Programmable objects refer to base tables and to each other in a kind of chain. For example, a stored procedure can use a view that selects from a base table. All the objects in SQL Server have owners. As long as there is a single owner for all the objects in the chain, you can manage permissions on the highest level only. Using the previous example, if the stored procedure, view, and base table have the same owner, you can manage permissions for the stored procedure only. SQL Server trusts that the owner of the procedure knows what the procedure is doing. This works for any DML statement (SELECT, INSERT, UPDATE, DELETE, and MERGE).

If the chain of owners between dependent objects is broken, SQL Server must check the permissions for any objects where the chain is broken. For example, if the owner of the procedure from the previous example is different from the owner of the view, SQL Server will check the permissions on the view as well. If the owner of the table was different from the owner of the view, SQL Server will also check permissions on the base table. In addition, if you use dynamic T-SQL code, concatenate a T-SQL statement as a string, and then use the EXECUTE command to execute them, SQL Server checks the permissions on all the objects the dynamic code is using. This is logical because SQL Server cannot know which objects the dynamic code is going to use until it actually executes the code, especially if you concatenate a part of the dynamic code from user input. Besides the threat of code injection, this extra checking is another reason why you should not use dynamic string concatenation in T-SQL code in production.

To start testing the programmable-objects-based row-level security, let's create a new demo database and change the context to this database:

USE master; 
IF DB_ID(N'RLSDemo') IS NULL 
CREATE DATABASE RLSDemo; 
GO 
USE RLSDemo; 

The next step is to create four database users without logins. Three of them represent regular users from the Sales department, and the fourth one represents the Sales department manager.

CREATE USER SalesUser1 WITHOUT LOGIN; 
CREATE USER SalesUser2 WITHOUT LOGIN; 
CREATE USER SalesUser3 WITHOUT LOGIN; 
CREATE USER SalesManager WITHOUT LOGIN; 
GO 

The next piece of code creates a table for the employee data. This table needs row-level security.

CREATE TABLE dbo.Employees 
( 
 EmployeeId   INT          NOT NULL PRIMARY KEY, 
 EmployeeName NVARCHAR(10) NOT NULL, 
 SalesRegion  NVARCHAR(3)  NOT NULL, 
 SalaryRank   INT          NOT NULL 
); 
GO 

Now let's insert some data into the dbo.Employees table. The three rows inserted represent the three regular users from the Sales department. You can check the inserted rows immediately with a query. Note that the sales region for the first two users is USA, and for the third one it is EU.

INSERT INTO dbo.Employees 
(EmployeeId, EmployeeName, SalesRegion, SalaryRank) 
VALUES 
 (1, N'SalesUser1', N'USA', 5), 
 (2, N'SalesUser2', N'USA', 4), 
 (3, N'SalesUser3', N'EU', 6); 
-- Check the data 
SELECT * 
FROM dbo.Employees; 
GO 

The dbo.Customers table, created with the following code, will also need row-level security:

CREATE TABLE dbo.Customers 
( 
 CustomerId   INT          NOT NULL PRIMARY KEY, 
 CustomerName NVARCHAR(10) NOT NULL, 
 SalesRegion  NVARCHAR(3)  NOT NULL 
); 
GO 

Again, let's insert some rows into this table and check them. There are two customers from the USA and two from the EU.

INSERT INTO dbo.Customers 
(CustomerId, CustomerName, SalesRegion) 
VALUES 
 (1, N'Customer01', N'USA'), 
 (2, N'Customer02', N'USA'), 
 (3, N'Customer03', N'EU'), 
 (4, N'Customer04', N'EU'); 
-- Check the data 
SELECT * 
FROM dbo.Customers; 
GO 

None of the users have been given any permissions yet. Therefore, you can read the data only as the dbo user. If you execute the following five lines of code, only the first SELECT succeeds. For the other four EXECUTE commands, you get an error.

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesManager'; 

In the next step, the code creates a stored procedure that reads the data from the dbo.Employees table. It filters the rows for regular users and returns all rows for the Sales department manager.

CREATE PROCEDURE dbo.SelectEmployees 
AS 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeName = USER_NAME() 
OR USER_NAME() = N'SalesManager'; 
GO 

You must give the permission to execute this procedure to the database users:

GRANT EXECUTE ON dbo.SelectEmployees 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 
GO 

Users still cannot see the data by querying the tables directly. You can test this fact by executing the following code again. You can read the data as the dbo user, but will get errors when you impersonate other database users.

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesManager'; 
GO 

Try to execute the stored procedure, once as dbo and once by impersonating each database user:

EXEC dbo.SelectEmployees; 
EXECUTE AS USER = N'SalesUser1' EXEC dbo.SelectEmployees; 
REVERT; 
EXECUTE AS USER = N'SalesUser2' EXEC dbo.SelectEmployees; 
REVERT; 
EXECUTE AS USER = N'SalesUser3' EXEC dbo.SelectEmployees; 
REVERT; 
EXECUTE AS USER = N'SalesManager' EXEC dbo.SelectEmployees; 
REVERT; 
GO 

As the dbo user, you can execute the procedure; however, you don't see any rows because the filter in the query in the procedure did not take the dbo user into consideration. Of course, the dbo user can still query the table directly. The regular users see their rows only. The Sales department manager sees all of the rows in the table.

The next procedure uses dynamic SQL to read the data from the table for a single user. By using dynamic SQL, the procedure creates a broken ownership chain.

CREATE PROCEDURE dbo.SelectEmployeesDynamic 
AS 
DECLARE @sqlStatement AS NVARCHAR(4000); 
SET @sqlStatement = N' 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeName = USER_NAME();' 
EXEC(@sqlStatement); 
GO 

Give the users permission to execute this procedure:

GRANT EXECUTE ON dbo.SelectEmployeesDynamic 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 
GO 

Try to execute the procedure by impersonating different users:

EXEC dbo.SelectEmployeesDynamic; 
EXECUTE AS USER = N'SalesUser1' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 
EXECUTE AS USER = N'SalesUser2' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 
EXECUTE AS USER = N'SalesUser3' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 
EXECUTE AS USER = N'SalesManager' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 

When you execute it as the dbo users, the execution succeeds, but you don't get any data returned. However, when you execute the procedure when impersonating other users, you get an error because other users don't have permission to read the underlying table.

Using programmable objects for row-level security protects the sensitive data very well because users don't have direct access to the tables. However, implementation of such security might be very complex for existing applications that don't use stored procedures, and other programmable objects. This is why SQL Server 2016 includes predicate-based Row-Level Security (RLS). A DBA creates the security filters and policies. The new security policies are transparent to the application. RLS is available in Standard, Enterprise, and Developer editions. There are two types of RLS security predicates:

You define the predicates through a predicate function. In the body of this function, you can use other tables with the JOIN or APPLY operators. If the function is schema bound, no additional permission checks are needed. If the function is not schema bound, users need permissions to read the data from the joined tables. When a predicate function is schema bound, you cannot modify the objects it refers to.

A security policy adds an RLS predicate to a table using a predicate function. The policy can be disabled. If it is disabled, users see all of the rows. A security policy also filters and/or blocks the rows for the database owners (dbo user, db_owner database role, and sysadmin server role).

Before testing the SQL Server 2016 RLS, the users need permissions to read the data. The following code gives the users permissions to read data from both tables in the demo database:

GRANT SELECT ON dbo.Employees 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 
GRANT SELECT ON dbo.Customers 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 
GO 

To check the permissions, you can try to read from the dbo.Employees table by impersonating each of the users again. All of the users see all of the rows.

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesManager'; 
GO 

The following command creates a separate schema for security objects. It is good practice to move the security objects into a separate schema. If they are in a regular schema, a DBA might inadvertently give permission to modify the security objects when giving the ALTER SCHEMA permission to users for some other reason, such as allowing them to alter the procedures in that schema.

CREATE SCHEMA Security;   
GO   

The following predicate function limits the users to seeing only their own rows in a table. The Sales department manager role can see all rows. In addition, the predicate also takes care of the dbo users, enabling these users to see all of the rows as well.

CREATE FUNCTION Security.EmployeesRLS(@UserName AS NVARCHAR(10))   
RETURNS TABLE   
WITH SCHEMABINDING   
AS   
RETURN SELECT 1 AS SecurityPredicateResult   
 WHERE @UserName = USER_NAME() 
    OR USER_NAME() IN (N'SalesManager', N'dbo');   
GO 

The next step is to create the security policy. The security policy created with the following code adds a filter predicate for the dbo.Employees table. Note that the EmployeeName column is used as the argument for the predicate function.

CREATE SECURITY POLICY EmployeesFilter   
ADD FILTER PREDICATE Security.EmployeesRLS(EmployeeName)    
ON dbo.Employees   
WITH (STATE = ON);   
GO 

You can test the filter predicate by querying the dbo.Employees table again. This time, each regular user gets their own row only, while the Sales department manager and the dbo users see all of the rows.

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesManager'; 
GO 

Note that the users can still gain access to the sensitive data if they can write queries. With carefully crafted queries, they can conclude that a specific row exists, for example. The salary rank for the SalesUser1 user is 5. This user might be interested if another user with salary rank 6 exists. The user can execute the following query:

EXECUTE (N'SELECT * FROM dbo.Employees  
WHERE SalaryRank = 6') 
AS USER = N'SalesUser1'; 

The query returns zero rows. The SalesUser1 did not get any information yet. However, when a query is executed, the WHERE predicate is evaluated before the security policy filter predicate. Imagine that the SalesUser1 tries to execute the following query:

EXECUTE (N'SELECT * FROM dbo.Employees  
WHERE SalaryRank / (SalaryRank - 6) = 0') 
AS USER = N'SalesUser1'; 

When you execute this code, you get error 8134, divide by zero error encountered. Now SalesUser1 knows that an employee with salary rank equal to 6 exists.

Now let's create another predicate function that will be used to filter the rows in the dbo. Customers table. It applies a tabular expression to each row of the dbo.Customers table to include the rows with the same sales region as the sales region value for the user that is querying the tables. It does not filter the data for the Sales department manager and the dbo database user.

CREATE FUNCTION Security.CustomersRLS(@CustomerId AS INT)   
RETURNS TABLE   
WITH SCHEMABINDING   
AS   
RETURN  
SELECT 1 AS SecurityPredicateResult   
FROM dbo.Customers AS c 
 CROSS APPLY( 
  SELECT TOP 1 1 
  FROM dbo.Employees AS e 
  WHERE c.SalesRegion = e.SalesRegion 
    AND (e.EmployeeName = USER_NAME() 
         OR USER_NAME() IN (N'SalesManager', N'dbo'))) 
 AS E(EmployeesResult) 
WHERE c.CustomerId = @CustomerId;   
GO 

The next step is, of course, to add a security policy. Note that you need to use a column from the dbo.Customers table for the argument of the predicate function. This argument is a dummy one, it does not filter the rows; the actual filter i is implemented in the body of the function.

CREATE SECURITY POLICY CustomersFilter   
ADD FILTER PREDICATE Security.CustomersRLS(CustomerId)    
ON dbo.Customers   
WITH (STATE = ON);   
GO 

The following queries test the filter predicate:

SELECT * FROM dbo.Customers; 
EXECUTE (N'SELECT * FROM dbo.Customers') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Customers') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Customers') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Customers') AS USER = N'SalesManager'; 
GO 

The rows from the dbo.Customers table are filtered for the regular users. However, note that SalesUser1 and SalesUser2 see the same rows—the rows for the customers from the USA—because the sales territory for both of them is USA. Now let's give the users permissions to modify the data in the dbo.Customers table.

GRANT INSERT, UPDATE, DELETE ON dbo.Customers 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 

Try to impersonate the SalesUser1 user, and delete or update a row that SalesUser1 does not see because of the filter predicate. In both cases, zero rows are affected.

EXECUTE (N'DELETE FROM dbo.Customers WHERE CustomerId = 3') 
 AS USER = N'SalesUser1'; 
EXECUTE (N'UPDATE dbo.Customers 
 SET CustomerName =' + '''' + 'Updated' + '''' + 
  'WHERE CustomerId = 3') 
 AS USER = N'SalesUser1'; 

However, SalesUser1 can insert a row that is filtered out when the same user queries the data. In addition, the user can also update a row in such a way that the row would disappear from the user's scope. Check the following code:

EXECUTE (N'INSERT INTO dbo.Customers 
(CustomerId, CustomerName, SalesRegion) 
 VALUES(5, ' + '''' + 'Customer05' + '''' + ',' + 
  '''' + 'EU' + '''' + ');' 
 ) AS USER = N'SalesUser1'; 
EXECUTE (N'UPDATE dbo.Customers 
 SET SalesRegion =' + '''' + 'EU' + '''' + 
 'WHERE CustomerId = 2') 
 AS USER = N'SalesUser1'; 

Now try to read the data. The dbo user sees all of the rows, while SalesUser1 sees neither the row (s)he just inserted nor the row (s)he just updated.

SELECT * FROM dbo.Customers; 
EXECUTE (N'SELECT * FROM dbo.Customers') AS USER = N'SalesUser1'; 

You need to add a block predicate to block the inserts and updates that would move a row outside the scope of the user performing the write operation:

ALTER SECURITY POLICY CustomersFilter   
ADD BLOCK PREDICATE Security.CustomersRLS(CustomerId)    
ON dbo.Customers AFTER INSERT, 
ADD BLOCK PREDICATE Security.CustomersRLS(CustomerId)    
ON dbo.Customers AFTER UPDATE;   
GO 

Try to do similar data modifications while impersonating the SalesUser1 user again:

EXECUTE (N'INSERT INTO dbo.Customers 
(CustomerId, CustomerName, SalesRegion) 
 VALUES(6, ' + '''' + 'Customer06' + '''' + ',' + 
 '''' + 'EU' + '''' + ');' 
) AS USER = N'SalesUser1'; 
EXECUTE (N'UPDATE dbo.Customers 
SET SalesRegion =' + '''' + 'EU' + '''' + 
'WHERE CustomerId = 1') 
 AS USER = N'SalesUser1'; 

This time, you get an error for both commands. You can see that the block predicate works. Finally, you can clean up your SQL Server instance:

USE master; 
IF DB_ID(N'RLSDemo') IS NOT NULL 
   ALTER DATABASE RLSDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
   DROP DATABASE RLSDemo; 
GO