Data Security in any application is vital. Different business or application  has their own security policy.  SQL Server 2016 provides three new security features which helps to protect data.

Below there are new security features added in SQL Server 2016.

  • Row-Level Security (RLS)
  • Dynamic Data Masking (DDM)
  • Always Encrypted

In this part of blog, we will see use case and implementation of Row-Level Security feature.

row level security

Predicate based access control is possible using . We can control access to rows in a table based on user characteristics. For example, a manager can only access employee data who are reporting to him.

It  supports two types of security predicate.

  • Filter predicates: it is transparent to user. It is applied for all read operation like SELECT, DELETE, UPDATE
  • Block Predicates: Explicitly block write operation if it violates the predicate. It affects all write operations like AFTER INSERT, AFTER UPDATE, BEFORE UPDATE and BEFORE DELETE.

We can implement RLS using Security policy and inline table valued function. Let’s put things into action and impalement RLS where a manager can only see employee data whoever reporting to him.

First create 3 users where 2 are having manager role and one having Senior manager role.Manager can only see data for its employee and Senior manager see all employee’s data.

manager 1

When we run the query against Employees as user Manager1, though we execute the query without any filter, SQL server finds there is security policy EmployeeCheck defined on table Employees so it applies the predicate filter check and will show only those employee rows who are reporting to manager1.   Manager1 will not realize any filter is applied and can only see data applicable.

Now run the same query as user Manager2. When you execute the same query by user Manager2, it applies the filter predicate value as Manager2.

 

 

manager2

If you see the filter predicate function checks if the user is SeniorManager it will return 1 for all rows resulting showing all rows when SeniorManager reads employees table.

senior manager

 

We can disable the security policy on Empolyees table using below query.

ALTER SECURITY POLICY EmployeeCheck  WITH (STATE = OFF);

Once you set the state of Security policy to off and user manager1 or manager2 run the same query, it will not apply the filter. User can see all rows.