Development

SQL Server – Row Level security in SQL Server 2016 – Part 1

Hi friends, in SQL 2016 several new security features gets introduced which will help users to protect their data in many ways. Today, I will discuss new security feature Row Level Security (RLS) which implements the security inside the database itself, not at application level.

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

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. Create Predicate Function
  2. Security Predicates
  3. Create Security Policy
  1. Predicate function

A predicate function is an inline tabled valued function which checks whether a user executing a query has the access to data based on logic defined on it while creation.

  1. Security Predicates

It helps in binding the predicate function to the table.

RLS supports two types of security predicates:

  • Filter Predicate
  • Block Predicate

Filter Predicate: It filters the data silently without raising any error for SELECT, UPDATE, DELETE operations as logic defined in predicate function.

Block Predicate: It explicitly raise the error and blocks the user from AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE operation on the data which violated the logic as defined in predicate function.

  1. Security Policy

It creates a security policy for row level security adding security predicates that referenced the predicate function.

Now let’s see the demo on this which we discussed in theory:

I have created a Person table where multiple department information is stored and we want each user should access the information of their departments only.

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

Create_Table

Now table is created with some dummy records into it.

To retrieve all the person data execute the below query:

SELECT * FROM Person

Table_Data

As mentioned in User_Access column of Person table, we will create three different user login to access the information for a particular department only.

–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

Create_user

After the login gets created we will grant the SELECT permission to all new user login by execute 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

Grant_select

Now we will create a predicate function which will work like WHERE predicate of select statement but it is invisible to user who is executing a query.

—-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

Create_functionpredicate

This function is simply returning rows if current user who is executing the query matches with the parameter passing to the function and will return only those rows which is accessible to it.

After creating function predicate, security policy needs to be created and above created predicate function dbo.PersonPredicate is bind to it which will work as filter predicate on table Person to which information will return.

—Create Security Policy

CREATE SECURITY POLICY PersonSecurityPolicy

ADD FILTER PREDICATE

dbo.PersonPredicate(User_Access) ON dbo.Person

WITH (STATE = ON)

Create_Securitypolicy

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

We can the information about the security policy that we have created using below query:

SELECT Name, object_id, schema_id, type, type_desc,

            Is_enabled, is_schema_bound

FROM sys.security_policies

check_securitypolicy

Now, when I execute the below query to test that with the current login whether I am still able to fetch all the records as I getting previously before creating predicate function and security policy –

SELECT * FROM Person

EXEC_1

So, this query didn’t return any rows as the user with which I am executing the query don’t have access to any row as per the definition of predicate function.

Let’s execute the query for the different users for which we have data in Person table.

First we will execute the query for user User_CS and check what will be the output –

EXECUTE AS USER = ‘User_CS’

SELECT * FROM dbo.Person

REVERT

exec_2

After executing above query we will only get the rows for user User_CS only.

So 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 other user also –

EXECUTE AS USER = ‘User_EC’

SELECT * FROM dbo.Person

REVERT

exec_4

EXECUTE AS USER = ‘User_IT’

SELECT * FROM dbo.Person

REVERT

exec_3

After executing the query for different user we get only those rows which has access to it. That’s pretty cool features of SQL Server 2016 in terms of security.

I will continue more on it in my next blog.

Have a happy learning 🙂

Advertisements

2 thoughts on “SQL Server – Row Level security in SQL Server 2016 – Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s