This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to publish about The Row Level Security which is now a common feature of all new version of RDBMSs like SQL Server 2016, PostgreSQL 9.5.
With the concept of Row Level Security, we can restrict the user for a particular set of records.
Previously, we are doing this kind of restriction using by creating filtered VIEWS and assign related VIEWS to relevant Users.
SQL Server 2016 has this Row Level Security feature which extends the overall security policy of the SQL Server.
Please check the below full demonstration on this.
Create a test database:
|
1 2 3 4 |
CREATE DATABASE CheckRowSecurity; GO USE CheckRowSecurity; GO |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE tbl_employeeSalary ( empcode VARCHAR(10) ,empname VARCHAR(10) ,empsalary BIGINT ,[month] INT ) GO INSERT INTO tbl_employeeSalary VALUES ('Anv','Anvesh',150000,1) ,('Anv','Anvesh',120000,2) ,('Anv','Anvesh',160000,3) ,('Anv','Anvesh',170000,4) ,('Mar','Martin',90000,1) ,('Mar','Martin',100000,2) ,('Mar','Martin',120000,3) ,('Mar','Martin',130000,4) GO |
Create two test users and assign SELECT permission on the table:
|
1 2 3 4 5 6 7 |
CREATE USER Anv WITHOUT LOGIN; CREATE USER Mar WITHOUT LOGIN; GO GRANT SELECT ON tbl_employeeSalary TO Anv GRANT SELECT ON tbl_employeeSalary TO Mar GO |
Create filter predicate function:
|
1 2 3 4 5 6 7 |
CREATE FUNCTION rowLevelSecurity(@userName as sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS rowLevelSecurityResult WHERE @userName = USER_NAME(); GO |
Add filter predicate to the table:
|
1 2 3 4 5 |
CREATE SECURITY POLICY UserFilter ADD FILTER PREDICATE dbo.rowLevelSecurity(empcode) ON dbo.tbl_employeeSalary WITH (STATE = ON); GO |
Select table without any user:
You will get a blank result set because we alter the security policy of this table.
|
1 |
SELECT * FROM tbl_employeeSalary |
Check for User ‘Anv’:
|
1 2 3 4 |
EXECUTE AS USER = 'Anv' SELECT * FROM tbl_employeeSalary REVERT GO |
Result:
|
1 2 3 4 5 6 |
empcode empname empsalary month ---------- ---------- -------------------- ----------- Anv Anvesh 150000 1 Anv Anvesh 120000 2 Anv Anvesh 160000 3 Anv Anvesh 170000 4 |
Check for User ‘Mar’:
|
1 2 3 4 |
EXECUTE AS USER = 'Mar' SELECT * FROM tbl_employeeSalary REVERT GO |
Result:
|
1 2 3 4 5 6 |
empcode empname empsalary month ---------- ---------- -------------------- ----------- Mar Martin 90000 1 Mar Martin 100000 2 Mar Martin 120000 3 Mar Martin 130000 4 |
