Row level security is one of the new feature introduced in Sql Server 2016, it provides a mechanism to control row level read and write access based on the user’s context data like identity, role/group membership, session/connection specific information (I.e. like CONTEXT_INFO(), SESSION_CONTEXT etc) etc.
This article explains this new feature with simple and extensive list of examples.
The logic to control the access to table rows resides in the database and it is transparent to the application or user who is executing the query. For example a database user executing a query SELECT * FROM Customers may feel that he has complete access to the Customers table as this query is returning the result without any exception, but with row level security in-place we can make the DataBase engine internally change the query something like for example: SELECT * FROM Customers Where AccountManager = USER_NAME().
[ALSO READ] Dynamic Data Masking in Sql Server 2016
This feature is very useful in scenarios like below:
- Shared Hosting/Multi-tenant scenario where common database used by multiple tenants to store the data. In such cases we want each tenant is restricted to access their data only.
- In a traditional database, where we want to control the access to rows based on user’s role.
Parts of Row-Level Security
Following are the three main parts of a Row-Level Security
- Predicate Function
A predicate function is an inline table valued schema bound function which determines whether a user executing the query has access to the row based on the logic defined in it. - Security Predicate
Security Predicate is the one which binds a Predicate Function to the Table.There are two types of security predicates
- Filter Predicate
It filters-out the rows from the SELECT, UPDATE or DELETE operation to which user doesn’t have access based on the logic in the Predicate function. This filtering is done silently without notifying or raising any error. - Block Predicate
It blocks user from INSERT, UPDATE or DELETE operation by explicitly raising the error if the row doesn’t satisfy the predicate function logic.There are four types of BLOCK predicates AFTER INSERT, BEFORE UPDATE, AFTER UPDATE and BEFOR DELETE.
- Filter Predicate
- Security Policy
Security policy is a collection of a Security Predicates which are grouped in a single new object called Security Policy.
[ALSO READ] New Features in Sql Server 2016
Enough theory let us jump on to the example which explains this theory in simple understandable term:
EXAMPLE: Let us take a scenario where we have a Cricket related database with Players table as shown in the below image. Assume that the players table has all the cricket playing countries team’s player data. When individual country cricket board application/user access this table data we want to return only the players belonging to that country.
Script to create demo CRICKET database and Players table with sample player’s data
CREATE DATABASE CRICKET
GO
USE CRICKET
GO
CREATE TABLE dbo.Players
(
PlayerId INT IDENTITY(1,1),
Name NVARCHAR(100),
Country NVARCHAR(50),
UserName sysname
)
GO
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Sachin Tendulkar', 'India', 'BCCI_USER'),
('Rahul Dravid', 'India', 'BCCI_USER'),
('Anil Kumble','India', 'BCCI_USER'),
('Ricky Ponting','Australia', 'CA_USER'),
('Shane Warne','Australia', 'CA_USER')
GO
Execute the following statement to retrieve all the records from the Players table
SELECT * FROM dbo.Players
RESULT:
Let us create three test user accounts by executing the following script
--Indian cricket board user CREATE USER BCCI_USER WITHOUT LOGIN -- Australian cricket board user CREATE USER CA_USER WITHOUT LOGIN --Admin user (International Cricket Council user) CREATE USER ICC_USER WITHOUT LOGIN
Let us execute the following script to grant read access on the Players table for the above three newly created users
GRANT SELECT ON Players TO BCCI_USER GRANT SELECT ON Players TO CA_USER GRANT SELECT ON Players TO ICC_USER
Let us create a Predicate function which returns 1 when user has access to the row by the following script. For this example we will use simple logic to determine the access to the row. The logic in this case is, if the name of the user who is executing the query matches with the UserName column value of that row or the name of the user who is executing the query is ICC_USER the user is allowed to access the row.
--Players predicate function <pre class="brush: sql; gutter: false"> CREATE FUNCTION dbo.PlayersPredicateFunction ( @UserName AS SYSNAME ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS AccessRight WHERE @UserName = USER_NAME() OR USER_NAME() = 'ICC_USER' GO
Let us create a Security Policy by executing the following script which is adding the above predicate function as a filter predicate on the Players table
--Security policy CREATE SECURITY POLICY PlayersSecurityPolicy --Security Predicate ADD FILTER PREDICATE dbo.PlayersPredicateFunction(UserName) ON dbo.Players WITH (STATE = ON)
Now execute the below statement to see whether I am (i.e. dbo owner) still able to fetch all the records from the Players table, which I was able to do prior to creating the security policy.
SELECT * FROM dbo.Players
RESULT
From the result it is clear that the filter predicate is filtering out all the rows as the SA user with which I am executing the query doesn’t have the access to any rows as per the predicate function definition. And below is the execution plan of this query:
From the execution plan we can see that the simple query SELECT * FROM dbo.Players is getting converted internally by the database engine as SELECT * FROM dbo.Players WHERE UserName = USER_NAME() OR USER_NAME() = ‘ICC_USER’ because of the Filter Predicate on the Players table
Execute the following query to see the rows to which the BCCI_USER user has access
EXECUTE AS USER = 'BCCI_USER' SELECT * FROM dbo.Players REVERT
RESULT:
From the result we can see that BCCI_USER with which the above query is executed can see only Indian cricket players as only these players UserName column value matches with the BCCI_USER.
Execute the following query to see the rows to which the CA_USER user has access
EXECUTE AS USER = 'CA_USER' SELECT * FROM dbo.Players REVERT
RESULT:
From the result we can see that CA_USER with which the above query is executed can see only Australian cricket players as only these players UserName column value matches with the CA_USER.
As per the filter predicate function definition the ICC_USER should be able to see all the Players rows. Let us confirm this by executing the following query.
EXECUTE AS USER = 'ICC_USER' SELECT * FROM dbo.Players REVERT
RESULT:
Grant the DML operations permission on the Players table to the above three newly created users by executing the following statement
GRANT INSERT, UPDATE, DELETE ON Players TO BCCI_USER GRANT INSERT, UPDATE, DELETE ON Players TO CA_USER GRANT INSERT, UPDATE, DELETE ON Players TO ICC_USER
Let us execute the following statement to see whether the user BCCI_USER use who doesn’t have access to the Australian players rows can insert an Australian Player
EXECUTE AS USER = 'BCCI_USER'
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Glenn McGrath', 'Australia', 'CA_USER')
REVERT
Let us execute the following statement to see whether the BCCI_USER can see the newly inserted Australian player record by him
EXECUTE AS USER = 'BCCI_USER' SELECT * FROM dbo.Players REVERT
RESULT:
From the result it is clear that BCCI_USER doesn’t have the access to the record Australian player record which he has inserted
Let us see whether the CA_USER can see the Australian player record which the BCCI_USER has inserted
EXECUTE AS USER = 'CA_USER' SELECT * FROM dbo.Players REVERT
RESULT:
From the result we can see that the CA_USER has access to the Australian Player record which BCCI_USER has inserted.
So from the above example we can see that a FILTER predicate is not blocking the user from INSERTING a record which after insert is filtered by it for that user for any operation.
Don’t worry to avoid such behavior, we have Block predicate at our disposal. Let us now understand the Block predicate with examples:
BLOCK PREDICATE
Let’s add the AFTER INSERT BLOCK predicate on the Players table to block user from inserting a record which after insert user doesn’t have access to it.
Execute the below statement to alter the above Security policy to add the AFTER INSERT BLOCK predicate.
ALTER SECURITY POLICY PlayersSecurityPolicy ADD BLOCK PREDICATE dbo.PlayersPredicateFunction(UserName) ON dbo.Players AFTER INSERT
Here for the AFTER INSERT BLOCK PREDICATE we are using the same predicate function which we have used to filter the records by the FILTER PREDICATE.
Basically, the AFTER INSERT BLOCK Predicate blocks user from inserting a record which after insert doesn’t satisfy predicate function. In other words from this example perspective the AFTER INSERT BLOCK predicate blocks the user from inserting a record which after insert user doesn’t have access to it.
Let us execute the following statement to see whether the user BCCI_USER who doesn’t have access to the Australian players rows can insert an Australian Player
EXECUTE AS USER = 'BCCI_USER'
INSERT INTO dbo.Players (Name, Country, UserName)
Values('Adam Gilchrist', 'Australia', 'CA_USER')
REVERT
RESULT:
Msg 33504, Level 16, State 1, Line 2
The attempted operation failed because the target object ‘CRICKET.dbo.Players’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.
From the result we can see that BLOCK Predicate is blocking a BCCI_USER user from inserting a record which after insert user doesn’t have access to it.
New Catalog Views/DMVs for the Row level security
Following are the two new catalog views introduced for Row level security in Sql Server 2016
- sys.security_policies
This catalog view returns all the Security Policies in the databaseExecute the following statement to get all the security policies in the database with important security policy attributes/columns
SELECT Name, object_id, type, type_desc, is_ms_shipped,is_enabled,is_schema_bound FROM sys.security_policies
RESULT:
- sys.security_predicates
This catalog view returns all the Security Predicates in the databaseSELECT * FROM sys.security_predicates
RESULT:
