Edit

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

  • Article

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

Prerequisites

Create SQL table and data

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

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

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

    DROP TABLE IF EXISTS dbo.Revenues;

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

  3. Insert four sample book 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

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

    SELECT * FROM dbo.Revenues

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

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

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

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

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

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

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

  3. Start the Data API builder tool.

    dab start

  4. Navigate to the http://localhost:5000/api/revenue endpoint. Observe that no data is returned. This behavior occurs because the session context isn't set and no records match the filter predicate.

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 name claim to the static value Oscar.

    EXEC sp_set_session_context 'name', 'Oscar';

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

    SELECT * FROM dbo.Revenues;

Related content