Blog on Implementing Row level Security based on a column in SQL SERVER

Row-level security in SQL Server is a feature that allows you to control access to rows in database tables based on user permissions and policies. This feature was introduced in SQL Server 2016 and is useful when restricting data access at a more granular level than traditional table-level security. It’s commonly used in multi-tenant applications, where different users or groups should only see their data.

Here’s an overview of how row-level security works in SQL Server:

• Security Predicates: Row-level security relies on security predicates, which are essentially filter conditions that are applied to tables. These predicates determine which rows a user can access based on their roles or permissions.
• Security Policies: Security policies define the rules for row-level security. You can create security policies for specific tables and associate them with one or more security predicates. Each policy is associated with a function that evaluates the predicate.
• Security Functions: A security function is a user-defined function that evaluates the security predicate for a specific row. This function takes user context into account, which means it can use session context or application-specific context to determine the filtering condition.
• Enforcing Security: When a user queries a table with a row-level security policy, the security function is automatically invoked for each row in the table to determine whether the user can access that row. If the security predicate evaluates to true, the row is returned; otherwise, it’s filtered out.

Use Case

Suppose you need to store employee information for various subsidiary companies of a larger corporation and grant access to users based on their association with specific subsidiary companies. In that case, you can structure the database to allow users to view employee details according to their respective company affiliations

For this blog we will be using 2 tables

1. User details ([company_identifier],[login_user]) : It store details of sql server users and company they are associate with.
2. Employee([EmployeeID],[ManagerID],[FirstName],[LastName],[FullName],[JobTitle],[OrganizationLevel],[MaritalStatus],[Gender],[Territory],[Country],[Group],[company_identifier]) :

This table show details about employee

Steps to implement RLS in SQL SERVER

1. Enable Row-Level Security:

Ensure that your SQL Server instance is at least SQL Server 2016 or newer.

Enable row-level security using the following command:

ALTER DATABASE [YourDatabaseName] SET ENABLE_ROW_LEVEL_SECURITY ON;

2. CREATE Login and user for the user present in user details table

              Login can be created in master database using the following command

              CREATE login niya with password = password@123′

              User can be created in actual database using the following command 

              CREATE user niya for login niya

       ALTER ROLE db_datareader add member niya

3. We then Create a Security schema using the following command:

CREATE SCHEMA Security

4. Once the schema is created, the subsequent action is to define security functions responsible for data filtering.

CREATE FUNCTION Security.fn_securitypredicate

         (@company AS nvarchar(500))

RETURNS TABLE

WITH SCHEMABINDING

AS

           RETURN ( SELECT 1 as Result

                     FROM [dbo].[user_details] f

            INNER JOIN [dbo].[Employee] s

                     ON s.[company_identifier] = f.[company_identifier]

            WHERE ( s.[company_identifier] = @company

                    AND f.[login_user] = USER_NAME() )

                   ) ;

This code verifies the login user’s name and matches it with the corresponding entry in the user details table. It then filters the rows in the Employee table based on the company identifier associated with the respective user.

5. The subsequent step involves the creation of a security policy. This is achieved by adding a filter predicate using the previously created security function.

CREATE SECURITY POLICY Employeefilter

ADD FILTER PREDICATE Security.fn_securitypredicate(company_identifier)

ON [dbo].[Employee]

WITH (STATE = ON); 

6. Finally we grant select access to the user using the following command

GRANT SELECT ON Security.fn_securitypredicate TO [user];

7. To validate the implementation of Row-Level Security (RLS), you can log in with the created user account to confirm if it is functioning correctly.

SELECT company_identifier,login_user from

user_details where login_user = USER_NAME()

SELECT * FROM Employee