In this third level of the Stairway to Row-Level Security (RLS), we will cover the Security Policy, a new object in SQL Server that links a security predicate to a particular table object. We will cover the filter policies in this level and examine the block predicate in the next level.

The Security Policy

A security policy in SQL Server 2016+ is a new, first class object that has its own set of DDL to define and manage the settings. There are CREATE, ALTER, and DROP commands for policies, each of which has options to define how the policy is used. These are run just like other DDL statements with any options that configure the policy. The policy is esssentially a filter that potentially stops a user from accessing some of the data in a table.

A policy has two types of predicates: filter and block. The filter predicate is used to read operations and the block predicate is used to stop write operations. This article covers the ways in which read operations are blocked while the next level will cover writes. A filter predicate works for any type of read operation, which includes SELECT, UPDATE, and DELETE queries. Each of these operations requires that the user can read the data in the table to perform some action.

Predicates can be enabled or disabled, though when created they are enabled by default. The policy can apply a predicate to multiple tables in the same policy, which allows an administrator to turn on row-level security for a number of tables at the same time. Note that while multiple policies can target a single table, only one policy can be enabled at a time.

In order to create a security policy, a predicate function must already exist. We covered these in Level 2, and will continue to use the example functions in this level. The structure of the security policy during creation is:

CREATE SECURITY POLICY [schemaname].[securitypolicyname]
 ADD FILTER PREDICATE [tvf_schema].[tvf_name] ON [table_schema].[table_name]

The security policy object can exist in the same schema as other objects, or it can have its own schema. The recommendation is that all RLS objects exist in their own schema for administrative ease. This means that you can easily see which functions are applied for RLS among all the other functions that you might have in your database. I would say that it's fine to do this if you're comfortable with multiple schemas and assigning permissions. The schema used for the policy, the predicate function, and the table on which you are applying security can the same or different.

There is an optional WITH clause that can include the STATE, SCHEMABINDING, and NOT_FOR_REPLICATION options. As mentioned, the defaults are that the STATE is ON, SCHEMABINDING is needed, and NOT_FOR_REPLICATION is not applied.

In a practical sense, let's create a policy. In Level 2, we used the WideWorldImporters sample database and created an iTVF, called RLS.CheckSalesPersonForOrder, that takes a parameter of a PersonID. We have a number of columns in the Sales.Orders table that could be used to validate a PersonID. For example, we have a Sales.Order.SalesPersonID column that we might want to use to filter out orders to allow only the person that made the sale to view that order. In that case, we could apply our predicate to a new policy like this:

CREATE SECURITY POLICY [RLS].[SalesPerson_Orders_Policy] 
ADD FILTER PREDICATE [RLS].[CheckSalesPersonForOrder]([SalespersonPersonID]) ON [Sales].[Orders]
WITH (STATE = ON, SCHEMABINDING = ON)
GO

This will apply that policy against all queries that target the Sales.Orders table. For each row, the SalespersonPersonID is passed into our RLS.CheckSalesPersonForOrder function and a 1 is returned if the value in the column matches the query in the function. In this case, I've altered the function from Level 2 to look at the result of the USER_NAME() function and compare that to values in Application.People table. Here is the function:

ALTER FUNCTION [RLS].[CheckSalesPersonForOrder]
(
    @PersonID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT [Result] = 1
    FROM Application.People AS p
    WHERE p.PersonID = @PersonID
          AND p.LogonName = USER_NAME()
);

I have changed a few of the values in the Application.People table to match some users and created those users. This will allow me to test the RLS system without switching between lots of query windows. The code to alter these items is:

CREATE USER Amy WITHOUT LOGIN
CREATE USER Anthony WITHOUT LOGIN
GO
GRANT SELECT ON Sales.Orders TO Amy
GRANT SELECT ON Sales.Orders TO Anthony
GO
UPDATE Application.People
 SET LogonName = 'Amy'
  WHERE PersonID = 7
UPDATE Application.People
 SET LogonName = 'Anthony'
  WHERE PersonID = 8
GO
SELECT top 10
  LogonName, *
 FROM Application.People
 GO

Now, we can test our RLS policy. I'll issue a query as Amy and check the USER_NAME() function as well as the Sales.Orders table. As you can see, I get back only those orders with SalespersonPersonID = 7.

This returns 7276 rows for SalespersonPersonID = 7. Let's repeat this for Anthony (SalespersonPersonID = 8). This returns 7258 rows:

All of these results are SalespersonPersonID = 8, which matched up with the Application.People.LogonName of Anthony.

This can be confusing, but let's try to put this together. For our users, we entered data in a table that matches their username. This is the Application.People table, and we reference this tablei in the security predicate function, RLS.CheckSalesPersonForOrder. This function joins our user name from the USER_NAME() function with the data in that table, and also with a parameter passed in.

The parameter passed in is controlled by the security policy object, which binds the table (Sales.Orders) to the predicate function, and configures the policy to use the SalespersonPersonID column as the value. This is evaluated and where matches occur, a 1 is returned. This results in those rows being returned from Sales.Orders. The execution plan makes this clearer. Note that the Clustered Index Scan and Filter are applied to the Application.People table, which is really the security predicate function. This is then joined with Sales.Orders to produce results.

Applying the Policy to Multiple Tables

Let's extend this slighltly and see how this policy applies to other tables. We have a Sales.Invoices table, which contains a column, SalespersonPersonID. This is the same data element from Sales.Orders, just related to the Sales.Invoices tables. First, let's check what we see with the user, Amy.

Note: You may need to grant SELECT rights to users on your system.

Amy is PersonID = 7, as we see above. Currently she can see all of the Invoices for all the different SalespersonPersonID values. Let's alter our policy to include this table as well. Using the ALTER DDL, we can change the security policy to include a new FILTER predicate with the same function on a new table.

ALTER SECURITY POLICY RLS.SalesPerson_Orders_Policy
 ADD FILTER PREDICATE [RLS].CheckSalesPersonForOrder(SalespersonPersonID) ON Sales.Invoices
GO

We can now run the same query, and we see that Amy now only sees the invoices that are related to her ID value.

We can continue to add additional predicates and tables to our policy in a way that allows us to check access for users. Let's look at another example that shows the addition of another table to the policy, but with a different column choice.

There is a Warehouse.StockItemHoldings column. This column again contains data related to the Application.People table. In this case the LastEditedBy column contains a value that relates to Application.People.PersonId. We can add this to our policy, using the same predicate with this code:

ALTER SECURITY POLICY RLS.SalesPerson_Orders_Policy
 ADD FILTER PREDICATE RLS.CheckSalesPersonForOrder(LastEditedBy) ON Warehouse.StockItemHoldings
GO

Note that when I query this table as the user with Application.People.PersonId = 3, I get just that data.

This shows me a couple things. First, I can use different columns from different tables in the same policy. The name of the column is not a factor. I merely need to ensure the column I wish to pass into the function exists in the table to which I am applying the policy.

I can also use different functions in the same policy. Let's create a new function that relates the data in SESSION_CONTEXT() to data in a table. Suppose I want to allow the suppliers of goods (from Purchasing.Suppliers) access to see the various purchase orders that have been added into the system. Our application will assign a user a value in SESSION_CONTEXT with the key of "SupplierID" and a value. Our function will return a 1 for rows in the Purchasing.Suppliers table that have a matching value. The function will convert the value from the table, an integer, to a nvarchar(), which is what is stored in the variable.

CREATE OR ALTER FUNCTION RLS.CheckSupplierID( @SupplierID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT [Result] = 1
    FROM Purchasing.Suppliers AS ps
    WHERE ps.SupplierID = @SupplierID
AND CAST(@SupplierID AS NVARCHAR(10)) = SESSION_CONTEXT(N'SupplierID')
);
GO

We bind this function to the Purchasing.PurchaseOrders table in our policy, passing in the SupplierID from each row.

ALTER SECURITY POLICY RLS.SalesPerson_Orders_Policy
 ADD FILTER PREDICATE RLS.CheckSupplierID(SupplierID) ON Purchasing.PurchaseOrders
GO

We now query the table and get nothing. Since there is no matching value in SESSION_CONTEXT(N'SupplierID'), no results are returned.

If we set a value, then we get purchase orders matching that value.

If we reset the value, we get new data.

Now we have used two different functions, with three different columns in our policy with three tables.

Updates

We can also see that the FILTER PREDICATE, used with reading the table, affects updates. Let's first grant some rights to our user, Amy.

GRANT INSERT, UPDATE ON Sales.Orders TO Amy

Now we can change data in one of the orders. For example, let's set the PickerByPersonID for OrderID=3 to 7, Amy's ID. We see above that Amy can read this row, so the update succeeds.

Now let's try and update a different order. OrderID = 5 has a SalespersonPersonID = 3. Let's see if Amy can update this row.

No error occurs, but no update is made. Since Amy would need to read this row to update it, and the FILTER PREDICATE stops the read, this is like updating a nonexistant row. There is a similar behavior for DELETE operations.

There are a few other options that can be used with the CREATE SECURITY POLICY and ALTER SECURITY POLICY statements. These are described below.

The State of the Policy

A policy has a state setting, which can be ON or OFF. This is the enabled or disabled state of the policy, meaning it is being applied to queries or ignored. The default state is ON, if not specified. An administrator can change that as needed.

Here is the current state. The user, Amy, can see only her Orders.

If we run this code, the policy is turned off.

ALTER SECURITY POLICY RLS.SalesPerson_Orders_Policy
 WITH (STATE = OFF)
GO

Issuing the same query, we see different results.

To turn the policy back on, we can run this:

ALTER SECURITY POLICY RLS.SalesPerson_Orders_Policy
 WITH (STATE = ON)

Schemabinding

The security policy can be created with SCHEMABINDING, which will cause all functions used in as predicates be created with SCHEMABINDING. If functions are not created with schemabinding, they cannot be added as predicates to the policy.

The default is that SCHEMABINDING is enabled, and this is recommended. We do not want objects changed that are used in a security policy without the administrator being aware of this and explicitly disabling the predicates before the objects are altered.

Replication

There is a NOT FOR REPLICATION setting for policies. This allows an administrator to decide if the RLS policy is executed against objects read by the replication agent. This allows you to determine if all rows, or limited ones, are sent to subscribers in replication situations.

Removing Predicates and Policies

We will at times need to change our policies or alter the functions. In order to remove a table from a policy, we use the ALTER SECURITY POLICY DDL, though with slightly different syntax. We do not need to specify the function, as only one function can be bound to a table. To remove the last predicate applied above, we would use this statement.

ALTER SECURITY POLICY RLS.SalesPerson_Orders_Policy
 DROP FILTER PREDICATE ON Purchasing.PurchaseOrders
GO

Note that I do need to include the type of predicate as I may have BLOCK predicates on this table.

If I want to remove the policy, I can do that with DROP SECURITY POLICY without removing the predicates. I still have two predicates inside this policy, but the DROP statement will succeed.

Note that if you remove a policy, it is removed from the system and there are no records of this object. If you decide to remove a policy, make sure you script out the code before you drop the object.

Limitations

There are a few limitations that exist for security policies.

First, if memory optimized tables are being referenced, SCHEMABINDING is required and the WITH NATIVE_COMPILATION options need to be included.

Second, a table can only be referenced by one security policy at a time. This does not include disabled (STATE=OFF) policies, but limits enabled (STATE = ON) policies to one per table. There can be different predicates on the same table (FILTER and BLOCK), but only one of each type at a time can be enabled.

Lastly, if SCHEMABINDING is not used, then the user querying the table needs to have the proper permissions to the predicate function and the underlying table as well. If SCHEMABINDING is set, these permission checks are ignored.

Conclusion

The security policy is the key to your RLS security. This ensures that a table has a policy applied against to that determines which rows a user can access. The policy is a first class object in SQL Server 2016+ with CREATE, ALTER, and DROP DDL.

In this level we looked at the FILTER PREDICATES, which is a binding of the iTVF to a table, using column(s) from the table as parameters for the function. We only showed one column and parameter being used, but multiple ones can be chosen. The FILTER PREDICATE is used to limit reads of the data to those that satisfy the query in the iTVF and return a result. This means that SELECT, UPDATE, and DELETE statements are affected as these all require reading of the table.

In the next level, we will look at BLOCK PREDICATES in a security policy.