Using programmable objects for RLS protects sensitive data very well because users don't have direct access to the tables. However, the implementation of such a security might be very complex for existing applications that don't use stored procedures, and other programmable objects. This is why SQL Server 2016 and 2017 include predicate-based RLS. A DBA creates the security filters and policies. The new security policies are transparent to the application. RLS is available in the Standard, Enterprise, and Developer editions. There are two types of RLS security predicates:
- Filter predicates that silently filter the rows the application reads. For these predicates, no application change is needed. Note that, besides reading, filter predicates also filter the rows when an application updates or deletes the rows; this is because the application again simply does not see the filtered rows.
- Block predicates that explicitly block write operations. You can define them for after-insert and after-update operations, when the predicates block inserts or updates that would move a row beyond the scope of the block predicate. After-insert block predicates also apply to minimally logged or bulk inserts. You can also define block predicates for before-update and before-delete operations, when they serve as filter predicates for the updates and deletes. Note that if you already use filter predicates, before-update and before-delete predicates are not needed. You might want to change the affected applications to catch additional errors produced by block predicates.
You define 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 (the dbo user, db_owner database, and sysadmin server roles).
Before testing SQL Server 2016 and 2017 RLS, users need permissions to read the data. The following code gives 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. The following code illustrates this process:
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 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. The following code illustrates this process:
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. The following code illustrates this process:
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 users can still gain access to 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 in the dbo.Customers table to include rows with the same sales region as the sales region value for the user who is querying the tables. It does not filter the data for the sales department manager and the dbo database user. The following code illustrates this process:
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, and does not filter the rows; the actual filter is implemented in the body of the function. The following code illustrates this process:
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 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 users permissions to modify the data in the dbo.Customers table, using the following code:
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. The code for this is given here:
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 it disappears from the users scope. The following code illustrates this process:
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, using the following code. 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