Posted in 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

Author:

I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on http://www.sqlservercentral.com/blogs/kapil-blogs/

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s