Development

SQL Server – Row Level Security in SQL Server 2016 – Part 3

Hi friends, continuing from the previous post in this post we will learn how to add the blocking predicate to restrict the users to perform DML operation (INSERT, UPDATE, DELETE) for others users data.

If you want to read the previous posts on Row Level Security you can find those post here:

https://kapilsqlgeek.com/2016/05/03/sql-server-row-level-security-in-sql-server-2016-part-1/

https://kapilsqlgeek.com/2016/05/05/sql-server-row-level-security-in-sql-2016-part-2/

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, BEFORE DELETE available and we will test using AFTER INSERT predicate.

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

We can find the information about the predicates that we have added to the security policy using system dmv like this:

SELECT * FROM sys.security_predicates

As we have added a block predicate to the security policy so it adds another row for this as previously there was no block predicate added to it, only FILTER predicate was present.

check_securitypredicates

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

EXECUTE AS USER = ‘User_CS’

INSERT INTO Person (PersonName, Department, Salary, User_Access)

SELECT ‘Sumit’, ‘IT’, 35000, ‘User_IT’

REVERT

This time it will not insert any rows and raise an error.

‘The attempted operation failed because the target object dbo.Person has a block predicate that conflicts with this operation. In case 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 block predicate.’

exec_7

This time it will throw an error and will not insert any rows for user ‘User_IT’. Hence after adding block predicate DML operations are restricted for unauthorized users.

Here are the few limitation and restrictions in Row Level Security that are as:

Limitation and Restrictions in Row Level Security

  1. Predicate function must be created with WITH SCHEMABINDING. If function is created without Schemabinding and try to bind it to a Security Policy you will get an error.
  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.
Advertisements

One thought on “SQL Server – Row Level Security in SQL Server 2016 – Part 3

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