Problem

Suppose we have critical data stored in a table, Person, with employee department and salary information. We want users to access the information for their respective departments only, but not be able to access the information of another department. Is there any security feature available in SQL 2016 which can meet this requirement?

Solution

Security has been one of the major focus area of Microsoft SQL Server and almost all releases either have an enhancement in existing features or the introduction of new security features. In SQL Server 2016, many new security features have been introduced like ‘Row Level Security’, ‘Always Encrypted’, ‘Dynamic Data Masking’, and ‘Enhancement of Transparent Data Encryption’ that will help users protect their data.

Today, I will discuss one of the security features, Row Level Security (RLS), which enables control over access to rows in a table. Row Level Security enables you to implement restrictions on the data based on the characteristics of the user who is executing a query. RLS helps in controlling access to data easily with complete transparency for different users.

Suppose we have some critical business data and want to limit access to the data on the basis of characteristics of the user. We want users to see the information that is tied to their respective departments only and not of any other department. For this, Row Level Security allows you easy control with complete transparency that only user specific data is visible.

From BOL: Row level security enables customers to control access to rows in a database based on the characteristics of the user executing a query.

To implement the RLS we need to consider three main parts:

1. Predicate Function
2. Security Predicates
3. Security Policy

Each of these items is described below.

Predicate function

A predicate function is an inline tabled valued function that checks whether a user executing a query has the access to data based on logic defined on it. This function will return a 1 for each row that a user is allowed to access.

Security Predicates

The Security Predicate helps in binding the predicate function to the table. RLS supports two types of security predicates: Filter Predicates and Block Predicates. The Filter Predicate filters the data silently without raising any error for SELECT, UPDATE, and DELETE operations as per the logic defined in the predicate function. The Block Predicate explicitly raises an error and blocks the user from AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE operations on data that violates the predicate function logic..

Security Policy

A security policy object gets created for row level security, grouping all the security predicates that referenced the predicate function.

Demo

We will create a table, Person, with sample data for 3 departments. At the end, we want each user to access the information of their own departments only. Below are the scripts:

Create table dbo.Person
(
            PersonId INT IDENTITY(1,1),
            PersonName varchar(100),
            Department varchar(100),
            Salary INT,
            User_Access varchar(50)
)
GO
INSERT INTO Person (PersonName, Department, Salary, User_Access)
SELECT 'Ankit', 'CS', 40000, 'User_CS'
UNION ALL
SELECT 'Sachin', 'EC', 20000, 'User_EC'
UNION ALL
SELECT 'Kapil', 'CS', 30000, 'User_CS'
UNION ALL
SELECT 'Ishant', 'IT', 50000, 'User_IT'
UNION ALL
SELECT 'Aditya', 'EC', 45000, 'User_EC'
UNION ALL
SELECT 'Sunny', 'IT', 60000, 'User_IT'
UNION ALL
SELECT 'Rohit', 'CS', 55000, 'User_CS'
GO

Now the table has been created with some dummy records. Execute the following statement to retrieve all the records from the Person table:

SELECT * FROM Person

The result displays all 7 sample records stored in the table as shown below:

As we see, information for all the three departments, CS, EC and IT,  and the User_Access column with their respective user groups has been displayed.

Let us create three test user accounts, one for each department by executing the following script:

--For CS department
CREATE USER User_CS WITHOUT LOGIN
--For EC department
CREATE USER User_EC WITHOUT LOGIN
-- For IT Department
CREATE USER User_IT WITHOUT LOGIN

After the creation of user groups, to grant read access for the above three newly created users, let's execute the below statement:

---Grant select permission to all new login users
GRANT SELECT ON Person TO User_CS
GRANT SELECT ON Person TO User_EC
GRANT SELECT ON Person TO User_IT

Now we will create a predicate function, which is invisible to the user who is executing the queries.

----Create function predicate
CREATE FUNCTION dbo.PersonPredicate
( @User_Access AS varchar(50) )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN  SELECT 1 AS AccessRight
    WHERE @User_Access = USER_NAME() 
GO

This function is simply returning rows. If the name of the user who is executing the query matches with the PersonName value corresponding to User_Access column, then the user is allowed to access the particular row.

After creating the predicate function, we create a security policy, which uses the above created predicate function, dbo.PersonPredicate, to work as a filter on table Person. Below is the script:

--Create Security Policy
CREATE SECURITY POLICY PersonSecurityPolicy
ADD FILTER PREDICATE dbo.PersonPredicate(User_Access) ON dbo.Person
WITH (STATE = ON)

The State should always be ON to enable the policy. In case, if you want to disable the policy you can change the state to OFF.

Now, after creation of the predicates and filters, we execute the below query again to test whether we are still able to fetch the same records as we were getting previously before creating predicate function and security policy or if the output has been changed.

SELECT * FROM Person

This time the query didn’t return any rows at all. This means as per the definition of predicate function and security policy creation, the user with which we have executed the query does not have access to any row.

Let’s execute the query for the different users for testing for which we have data in Person table. First we will execute the below mentioned query for user User_CS and check the output:

EXECUTE AS USER = 'User_CS'
SELECT * FROM dbo.Person
REVERT

After executing above query we see that only those rows that belong to User_Access group, User_CS, have being retrieved. Hence, it is clear that the filter predicate is filtering out all the rows as the CS group user with which we have executed the query doesn’t have the access to another user groups.

Once again, the process can be understood as when we execute the query, it calls the predicate function and db engine internally execute the query like this:

SELECT * FROM dbo.Person
 WHERE User_Name() = 'User_CS'

Let’s execute the query for another user as well and check if the predicates are working as expected.

EXECUTE AS USER = 'User_EC'
SELECT * FROM dbo.Person
REVERT

Here, when we executed the query from EC access group, only rows pertaining to this access group were retrieved.

For the last User_Access group:

EXECUTE AS USER = 'User_IT'
SELECT * FROM dbo.Person
REVERT

Similarly, when we executed for IT group, data only for persons from IT group was displayed.

So, we can see that after executing the query for various users, we get only those rows which have access to that particular user group. That’s pretty cool.

As of now, no Block Predicate Filter has been applied to security policy so let’s test whether a user from one user access group is able to perform any DML operation (INSERT, UPDATE, DELETE) for users of another group. Execute the below query to grant the DML operation access to all newly created users –

--Grant DML rights to all users
GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_CS
GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_EC
GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_IT

Let us execute the below query as user ‘User_IT’ and then try to insert the data for user ‘User_CS’.

EXECUTE AS USER = 'User_IT'
INSERT INTO Person (PersonName, Department, Salary, User_Access)
SELECT 'Soniya', 'CS', 35000, 'User_CS'
REVERT

Ohhh…No error appears and query gets executed successfully.

Let us execute the query now to check whether newly insert record is appearing for ‘User_IT’ or not.

EXECUTE AS USER = 'User_IT'
SELECT * FROM dbo.Person
REVERT

It’s strange, as we get only those rows which appeared in previous output and not this newly inserted record.

So where has that row gone?

Let’s execute the query one more time, now for user ‘User_CS’ and see if we can see the newly inserted record –

--Rows will appear which is inserted by different user
EXECUTE AS USER = 'User_CS'
SELECT * FROM dbo.Person
REVERT

From the above example we can see that the predicate filter is not blocking the user from inserting a record hence no error will appear. This is because, no block predicate is currently defined in security policy.

Let’s now add a block predicate and check if it explicitly raise an error or not after adding block predicate. There are 4 block predicates, AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE, available. We will opt to use the AFTER INSERT predicate for testing. This AFTER INSERT BLOCK Predicate blocks user from inserting a record which AFTER the INSERT doesn’t satisfy the predicate filter, i.e., it blocks user from inserting a record which the user doesn’t have access to view.

Now alter the security policy as per the below query:

--ADD block predicate
ALTER SECURITY POLICY PersonSecurityPolicy
 ADD BLOCK PREDICATE dbo.PersonPredicate(User_Access)
 ON dbo.Person AFTER INSERT

Now, execute the query as user ‘User_CS’ and try to insert the row for another user ‘User_IT’ using below query:

EXECUTE AS USER = 'User_CS'
INSERT INTO Person (PersonName, Department, Salary, User_Access)
SELECT 'Sumit', 'IT', 35000, 'User_IT'
REVERT

Oops! This time an error appeared and blocked the insert of any new rows for user ‘User_IT’. Hence we can say that after adding block predicate, DML operations are restricted for unauthorized users.

Note: In our case, a department consists of a single user. If in other cases, a department contains multiple users then we need to create separate logins for each of them and assign the permissions as necessary because our predicate function works on a user basis and security policy depends on predicate function.

Limitation and Restrictions in Row Level Security

There are a few limitations that apply to RLS.

1. The Predicate function must be created with WITH SCHEMABINDING. If function is created without Schemabinding and try to bind it to a Security Policy, an error is thrown.
2. Indexed views cannot be created on a table on which Row Level Security is implemented.
3. In-Memory tables are not supported for Row Level Security
4. Full text indexes are not supported.

Summary

With the Row Level Security feature in SQL Server 2016, we can provide security for records at the database level without making changes at the application level. Row Level Security can be implemented using a predicate function and new security policy feature alongside your existing code, without requiring changes to the various DML code in your database.