Development

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

Hi friends, in my previous post we have learnt how to implement Row Level Security feature in our database. You can find the link of Part1 here:

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

In this post we will discuss more on Row Level Security and see what will happen if we give users DML operation rights other than SELECT operation what we have done till now.

As no block predicate filter has been applied to security policy lets test whether a user is able to perform any DML operation (INSERT, UPDATE, DELETE) for other users.

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’s execute the below query as user ‘User_IT’ and tried to insert the data for user ‘User_CS’.

/* To check other user have access to perform DML operation for other users */

EXECUTE AS USER = ‘User_IT’

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

SELECT ‘Soniya’, ‘CS’, 35000, ‘User_CS’

REVERT

exec_5

Ohhh…No error appears and query gets executed successfully.

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

EXECUTE AS USER = ‘User_IT’

SELECT * FROM dbo.Person

REVERT

We get only those rows which comes appears previously in output.

exec_2

So where that row gone…

Let’s execute the query for user ‘User_CS’ and see what outcome will appear –

–Rows will appear which is inserted by different user

EXECUTE AS USER = ‘User_CS’

SELECT * FROM dbo.Person

REVERT

exec_6

So friends, in this blog we have learnt that how to perform DML operation to made changes to the data of other user. In next blog we will learn how to restrict the user to perform such operation.

Have a great day!! 🙂

Advertisements

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

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