Implement row-level security with session context in Data API builder

Use the session context feature of SQL to implement row-level security in Data API builder.

Important

Session context with SQL Server row-level security differs from Data API builder database policies. Database policies (for example, --policy-database "@item.owner eq @claims.user_id") are translated into WHERE clauses by Data API builder, while session context forwards claims to SQL Server so that SQL-native row-level security handles the filtering.

Prerequisites

• Existing SQL server and database.
• Data API builder CLI. Install the CLI

Note

Session context is supported in:

• SQL Server 2016 and later
• Azure SQL Database
• Azure Synapse Analytics (Dedicated SQL pool)
• Azure Synapse Analytics (Serverless SQL pool) isn't supported

Create SQL table and data

Create a table with fictitious data to use in this example scenario.

1. Connect to the SQL database using your preferred client or tool.

2. Create a table named Revenues with id, category, revenue, and accessible_role columns.

   DROP TABLE IF EXISTS dbo.Revenues;
   
   CREATE TABLE dbo.Revenues(
       id int PRIMARY KEY,  
       category varchar(max) NOT NULL,  
       revenue int,  
       accessible_role varchar(max) NOT NULL  
   );
   GO
   

3. Insert four sample rows into the Revenues table.

   INSERT INTO dbo.Revenues VALUES
       (1, 'Book', 5000, 'Oscar'),  
       (2, 'Comics', 10000, 'Oscar'),  
       (3, 'Journals', 20000, 'Hannah'),  
       (4, 'Series', 40000, 'Hannah')
   GO
   

   In this example, the accessible_role column stores the role name that can access the row.

Tip

Common session context use cases:

• Role-based filtering (shown here) using roles
• Multitenant isolation using tenant_id
• User-specific filtering using user_id

1. Test your data with a simple SELECT * query.

   SELECT * FROM dbo.Revenues
   

2. Create a function named RevenuesPredicate. This function will filter results based on the current session context.

   CREATE FUNCTION dbo.RevenuesPredicate(@accessible_role varchar(max))
   RETURNS TABLE
   WITH SCHEMABINDING
   AS RETURN SELECT 1 AS fn_securitypredicate_result
   WHERE @accessible_role = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   

3. Create a security policy named RevenuesSecurityPolicy using the function.

   CREATE SECURITY POLICY dbo.RevenuesSecurityPolicy
   ADD FILTER PREDICATE dbo.RevenuesPredicate(accessible_role)
   ON dbo.Revenues;
   

Note

The WITH SCHEMABINDING clause is required for functions used in security policies so underlying schema changes do not invalidate the predicate.

(Optional) Create a stored procedure

This section shows a simple "hello world" pattern for using session context values directly in T-SQL.

1. Create a stored procedure that reads the roles session context value and uses it to filter results.

   CREATE OR ALTER PROCEDURE dbo.GetRevenuesForCurrentRole
   AS
   BEGIN
       SET NOCOUNT ON;
   
       DECLARE @role varchar(max) = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   
       SELECT id, category, revenue, accessible_role
       FROM dbo.Revenues
       WHERE accessible_role = @role;
   END
   GO
   

Run tool

Run the Data API builder (DAB) tool to generate a configuration file and a single entity.

1. Create a new configuration while setting --set-session-context to true.

   dab init \
       --database-type mssql \
       --connection-string "<sql-connection-string>" \
       --set-session-context true \
       --auth.provider Simulator
   

   When session context is enabled for SQL Server, Data API builder sends authenticated user claims to SQL by calling sp_set_session_context (for example, roles). Enabling session context also disables response caching for that data source.

Warning

When set-session-context is enabled, response caching is disabled for the data source. For high-traffic scenarios, consider testing performance, indexing the predicate column, or using Data API builder database policies when they meet your needs.

1. Add a new entity named revenue for the dbo.Revenues table.

   dab add revenue \
       --source "dbo.Revenues" \
       --permissions "Authenticated:read"
   

2. Start the Data API builder tool.

   dab start
   

3. Query the endpoint without specifying an effective role. Observe that no data is returned because:

   • The effective role defaults to Authenticated.
   • No rows have accessible_role = 'Authenticated'.
   • The security policy filters results when the role doesn't match.

   curl http://localhost:5000/api/revenue
   

4. Query the endpoint while setting the effective role to Oscar. Observe that the filtered results include only the Oscar rows.

   curl -H "X-MS-API-ROLE: Oscar" http://localhost:5000/api/revenue
   

5. Repeat using the Hannah role.

   curl -H "X-MS-API-ROLE: Hannah" http://localhost:5000/api/revenue
   

Test with GraphQL

Session context also works with GraphQL queries.

query {
    revenues {
        items {
            id
            category
            revenue
            accessible_role
        }
    }
}

Pass the role header:

curl -X POST http://localhost:5000/graphql \
    -H "Content-Type: application/json" \
    -H "X-MS-API-ROLE: Oscar" \
    -d '{"query": "{ revenues { items { id category revenue accessible_role } } }"}'

What Data API builder sends to SQL Server

When session context is enabled, Data API builder sets session context values on every request before executing your query.

EXEC sp_set_session_context 'roles', 'Oscar', @read_only = 0;
-- Then executes your query
SELECT * FROM dbo.Revenues;

All authenticated user claims are sent as key-value pairs. Common claims include roles, sub or oid, and any custom claims from your identity provider.

Test in SQL

Test the filter and predicate in SQL directly to ensure it's working.

1. Connect to the SQL server again using your preferred client or tool.

2. Run the sp_set_session_context to manually set your session context's roles claim to the static value Oscar.

   EXEC sp_set_session_context 'roles', 'Oscar';
   

3. Run a typical SELECT * query. Observe that the results are automatically filtered using the predicate.

   SELECT * FROM dbo.Revenues;  
   

4. (Optional) Query the table using the stored procedure.

   EXEC dbo.GetRevenuesForCurrentRole;
   

Clean up resources

If you want to remove the sample objects, run:

DROP SECURITY POLICY IF EXISTS dbo.RevenuesSecurityPolicy;
DROP FUNCTION IF EXISTS dbo.RevenuesPredicate;
DROP PROCEDURE IF EXISTS dbo.GetRevenuesForCurrentRole;
DROP TABLE IF EXISTS dbo.Revenues;

Troubleshooting

• No results returned: Verify the security policy is active (SELECT * FROM sys.security_policies), check the session context value (SELECT SESSION_CONTEXT(N'roles')), and confirm --set-session-context true is set in your Data API builder configuration.
• All rows returned: Confirm the security policy isn't disabled (WITH STATE = OFF) and that the predicate returns 1 only for authorized rows.
• Performance issues: Index the predicate column (accessible_role), and consider temporarily disabling the policy to isolate performance impact.

Related content

• Database-specific features
• Use environments