How to implement row-level security in Azure SQL Database?

Question

What is row-level security in Azure SQL Database and how can I restrict data access based on user roles?

Answer 1

Hi @HimanshuSinha

Row-Level Security (RLS) in Azure SQL Database enables you to restrict access to specific rows in a table based on the executing user's identity, role membership, or application context. RLS is enforced by the database engine, providing centralized security without requiring changes to application queries.

Implementation Steps

1. Create a predicate function

The predicate function determines which rows a user can access.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_salesrep_access(@SalesRep sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 1 AS access_granted
    WHERE @SalesRep = USER_NAME()
       OR IS_ROLEMEMBER('ManagerRole') = 1
);
GO

2. Create a security policy

Attach the predicate function to the target table.

CREATE SECURITY POLICY SalesAccessPolicy
ADD FILTER PREDICATE Security.fn_salesrep_access(SalesRep)
ON dbo.Sales
WITH (STATE = ON);
GO

Once enabled, queries against dbo.Sales automatically return only rows permitted by the predicate.

3. Test the policy

EXECUTE AS USER = 'SalesRep1';

SELECT * FROM dbo.Sales;

REVERT;

A sales representative sees only their own rows, while members of ManagerRole can view all rows.

FILTER vs BLOCK Predicates

FILTER predicates silently filter rows from query results.

BLOCK predicates prevent unauthorized INSERT, UPDATE, or DELETE operations.

Multi-Tenant Applications

For SaaS applications, Microsoft commonly recommends using SESSION_CONTEXT() to store tenant information and enforce tenant-level isolation through RLS.

References

https://learn.microsoft.com/sql/relational-databases/security/row-level-security
https://learn.microsoft.com/sql/t-sql/statements/create-security-policy-transact-sql
https://learn.microsoft.com/azure/azure-sql/database/security-overview

Please let us know if you have any questions.

Answer 2

Row-level security (RLS) in Azure SQL Database is a feature that restricts which rows a user can access in a table based on the user’s identity or other characteristics (for example, group membership or execution context). It enforces fine-grained, per-row access control in the database engine, so applications do not need complex filtering logic.

To restrict data access based on user roles in Azure SQL Database using RLS:

1. Design the access rule
   • Decide which rows each role or user should see. For example, a Manager role can see all rows, while each SalesRep can see only rows where SalesRep = their username.
2. Create users and grant basic permissions
   • Create logins and users, then grant SELECT on the target table or view to those users/roles. RLS does not replace GRANT permissions; it adds an extra filter.
3. Create a predicate function
   • Implement an inline table-valued function that returns 1 only when the current user is allowed to see the row. For example:

      CREATE SCHEMA Security;
      GO
      
      CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
          RETURNS TABLE
      WITH SCHEMABINDING
      AS
          RETURN SELECT 1 AS fn_securitypredicate_result
          WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
   

   • This function allows access when the SalesRep column matches the current user name or when the current user is Manager.
4. Create the security policy
   • Bind the predicate function to the table using a security policy:

      CREATE SECURITY POLICY SalesFilter
      ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
      ON Sales.Orders
      WITH (STATE = ON);
   

   • STATE = ON enables the policy so that all queries on Sales.Orders are transparently filtered.
5. Test with different users
   • Execute queries under different execution contexts to verify behavior:

      EXECUTE AS USER = 'SalesRep1';
      SELECT * FROM Sales.Orders;
      REVERT;
      
      EXECUTE AS USER = 'SalesRep2';
      SELECT * FROM Sales.Orders;
      REVERT;
      
      EXECUTE AS USER = 'Manager';
      SELECT * FROM Sales.Orders;
      REVERT;
   

   • The manager should see all rows; each sales representative should see only their own rows.
6. Manage and disable policies when needed
   • Temporarily disable a policy:

      ALTER SECURITY POLICY SalesFilter
      WITH (STATE = OFF);
   

   • When disabled, users with SELECT permission see all rows again.

RLS is recommended as part of a broader authorization strategy in Azure SQL Database. Combine it with database roles and least-privilege grants so that users only have the minimal permissions required, and use EXECUTE AS where appropriate to further constrain execution context.

---

References:

• An overview of Azure SQL Database and SQL Managed Instance security capabilities
• Row-level security
• Secure your Azure SQL Database
• Azure Synapse Analytics security white paper: Access control