Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.
Row level security feature introduced in SQL Server 2016.
Once it is enabled, It applies the restrictions on data row access and records from the tables are displayed based on who the user is and to which records user has access to.
Lets understand when you can take the benefit of ROW-Level security feature.
Suppose, you have one table named UserData, and this table contains the data of different different user’s which may belongs to Sales, IT, Finance and HR department.
Following are the table scripts of creating a table and inserting a data into table.
CREATE TABLE dbo.UserData
(
RowId INT, 
UserOf VARCHAR(50), 
ID INT, 
NAME VARCHAR(50), 
DOB DATE
)

INSERT INTO dbo.UserData
(RowId, UserOf, ID, Name, DOB)
VALUES
(101, 'Finance', 401, 'Rozer Mac', '1989-06-10'),
(102, 'Finance', 402, 'Mark Harnery', '1990-07-10'),
(103, 'IT', 501, 'Rohit Singh', '1990-07-05'),
(104, 'IT', 502, 'Mark Harnery', '1980-07-18'),
(105, 'HR', 201, 'Sanjay Kumar', '1981-07-12'),
(106, 'HR', 202, 'Mahesh Rawat', '1991-10-19'),
(107, 'IT', 503, 'Ajay Kr', '1990-07-10'),
(108, 'Sales', 601, 'Atul Pandey', '1988-07-18'),
(109, 'Sales', 602, 'Romit', '1986-05-10'),
(110, 'Sales', 603, 'Sujoy Ghosh', '1990-01-09')
As this table is a common table which contains the records of Sales, IT, Finance and HR so there may be a highly chances that one user can access the data of another user. Therefore, you want user to restrict to access their data only, means when a specific user logs in, the Row-Level Security restrict the data and displayed the records based on who the user is and to which records user has access to.
For example, when the user IT logs in, only the rows belongs to IT will be displayed to user.
As following select statement fetches the data of specific user’s which belongs to Finance, HR, Sales, and IT.
SELECT * FROM dbo.UserData WHERE UserOf ='Finance'
SELECT * FROM dbo.UserData WHERE UserOf ='HR'
SELECT * FROM dbo.UserData WHERE UserOf ='Sales'
SELECT * FROM dbo.UserData WHERE UserOf ='IT'
This is the same case that you want to achieve using ROW-LEVEL security. User can see only rows which belongs to that user only.
Implementing the ROW-LEVEL Security
To implementing a ROW-LEVEL Security you have to gone through three major steps as defined below:
- Predicate Function: It is an inline table valued schema bound function which determines whether a user querying the table has access to the row based on the logic defined in function.
- Security Predicate: It binds a Predicate Function to the Table. There are two types of security predicates as follows:
- Filter Predicate : It filters the rows from the SELECT, UPDATE or DELETE operation to which user doesn’t have access based on the logic defined in the Predicate function.
- Block Predicate: It restricts user from performing INSERT, UPDATE or DELETE operation explicitly. It returns an error if the row doesn’t satisfy the predicate function logic. There are four types of BLOCK predicates:
- AFTER INSERT and AFTER UPDATE : These predicates restrict users from updating rows to values that violate the predicate. AFTER UPDATE and BEFOR DELETE.
- BEFORE UPDATE: This predicates restrict users from updating rows that currently violate the predicate.
- BEFORE DELETE : This predicates restricts delete operations.
- Security Policy: It is a collection of a Security Predicates which are grouped in a single new object called Security Policy.
Lets create a new user accounts for IT, Sales, HR and Finance. Using these account you can test the ROW-LEVEL security.
CREATE USER IT WITHOUT LOGIN;
GO
CREATE USER Sales WITHOUT LOGIN;
GO
CREATE USER Finance WITHOUT LOGIN;
GO
CREATE USER HR WITHOUT LOGIN;
GO
CREATE USER HOD WITHOUT LOGIN;
GO
Now we will provide the GRANT read access on the UserData table for these new users.
GRANT SELECT ON dbo.UserData TO IT;
GRANT SELECT ON dbo.UserData TO Sales;
GRANT SELECT ON dbo.UserData TO HR;
GRANT SELECT ON dbo.UserData TO Finance;
GRANT SELECT ON dbo.UserData TO HOD;
Now we will create a Inline table valued function, which check the user who has logged in and return the result set based on the login context of the user only.
CREATE FUNCTION dbo.fn_UserDataSecurity(@User AS SYSNAME)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Is_accessrights
WHERE @User = USER_NAME() 
OR USER_NAME() = 'HOD';
GO
Adding Filter Predicate in Security Policy
After that we will apply the specific security policy which enforce the filter predicate and pass it to the underlying query just like a where clause filter.
CREATE SECURITY POLICY UserData_SecurityPolicy
ADD FILTER PREDICATE dbo.fn_UserDataSecurity(UserOf) 
ON dbo.UserData
WITH (STATE = ON);
GO
As you can see, commands completed successfully.
Lets verify whether the security policies and security predicates that have been created in database or not. For this you can use systems catalog views as shown below.
Sys.security_policies: It returns a row for each security policy in the database.
SELECT Name, object_id, type, type_desc, is_enabled, create_date
FROM sys.security_policies
sys.security_policies: It returns a row for each security predicate in the database.
SELECT * 
FROM sys.security_predicates
As you can see the output of systems catalog views that confirms that Security policy and Predicate is created successfully in database.
Now to verify the implementation of ROW-LEVEL Security, Lets select the records from UserData table.
SELECT * FROM dbo.UserData
As you can see in below result, it does not return any records for you although you were able to see all the rows in table before implementing the ROW_LEVEL security.
It happens because the user account you have logged in to SQL Server is not defined in the filter predicate of the table-valued function.
Lets execute the same scripts under the context of the user Finance.
EXECUTE AS USER = 'Finance'
SELECT * FROM dbo.UserData
REVERT
You can see the result, user Finance can see only rows which belongs to finance.
Similarly, Lets executes the scripts under the context of the user Sales.
EXECUTE AS USER = 'Sales'
SELECT * FROM dbo.UserData
REVERT
As you can see the result, user Sales can see only the rows which belongs to Sales.
Only the user HOD will be able to see all the rows of UserData’s table as you have defined the logic in the filter predicate function definition that user HOD should be able to see all the rows of UserData table.
EXECUTE AS USER = 'HOD'
SELECT * FROM dbo.UserData
REVERT
Lets Grant the permission to users for DML operations INSERT, UPDATE, and DELETE.
GRANT INSERT, UPDATE, DELETE ON dbo.UserData TO IT;
GRANT INSERT, UPDATE, DELETE ON dbo.UserData TO Sales;
GRANT INSERT, UPDATE, DELETE ON dbo.UserData TO HR;
GRANT INSERT, UPDATE, DELETE ON dbo.UserData TO Finance;
GRANT INSERT, UPDATE, DELETE ON dbo.UserData TO HOD;
Lets see whether the user HR who doesn’t have access to the Sales rows can insert record for Sales in UserData table or not.
EXECUTE AS USER = 'HR'
INSERT INTO dbo.USerData(RowId, UserOf, ID, Name, DOB)
Values(111, 'Sales', 604, 'Ramesh Agarwal', '1991-02-08')
REVERT
You can see, it is inserted successfully that means user HR can insert records for Sales.
Lets verify whether the HR can also see the that newly inserted record for sales.
EXECUTE AS USER = 'HR'
SELECT * FROM dbo.USerData
REVERT
You can see, user HR can not see the newly inserted Sales record even after inserting the same, that means user HR does not have access to Sales Rows.
Lets verify whether user Sales can see newly inserted Sales record which has inserted by user HR.
EXECUTE AS USER = 'Sales'
SELECT * FROM dbo.USerData
REVERT
You can see, user Sales can see the Sales records which has inserted by user HR.
You will also notice that FILTER predicate is not restricting the user from inserting a records which after insert is filtered by it for that user for any operation.
So To restricting the user from inserting a record which after insert user doesn’t have access to it, you can add BLOCK PREDICATE for After Insert in security policy.
Adding BLOCK Predicate in Security Policy
Lets alter the security policy UserData_SecurityPolicy to add the AFTER INSERT BLOCK predicate.
It will restrict the user from inserting a record which after insert user doesn’t have access to it.
ALTER SECURITY POLICY UserData_SecurityPolicy
ADD BLOCK PREDICATE dbo.fn_UserDataSecurity(UserOf)
ON dbo.UserData AFTER INSERT
GO
Lets verify whether the BLOCK predicate is add to security policy or not using system catalog views.
You can see After Insert BLOCK predicate is added to security policy as shown below.
SELECT *
FROM sys.security_predicates
Lets see the implementation of BLOCK Predicate and check, whether the user HR who doesn’t have access to the Sales rows can insert record for Sales in UserData table or not.
EXECUTE AS USER = 'HR'
INSERT INTO dbo.USerData(RowId, UserOf, ID, Name, DOB)
Values(112, 'Sales', 605, 'Daniel Jr', '1988-02-08')
REVERT
As you can see, BLOCK Predicate restrict a user HR from inserting a record for Sales which after insert user HR does not have access to see it.
Disabling and dropping the security policy
To disabling the security policy, you need to alter the security policy and set STATE to OFF as shown below.
ALTER SECURITY POLICY UserData_SecurityPolicy
WITH (STATE = OFF);
To dropping the security policy, use drop command as follows.
DROP SECURITY POLICY UserData_SecurityPolicy;
Also Read..
Data Discovery and Classification in SQL Server
Pls help
About how to data export from a sql data table to excel or csv
You can refer this post – https://sqlskull.com/2021/03/04/export-data-from-sql-server-to-an-excel-file/