DB-유

Row-Level Security in SQL Server 2016

· mssql, security, tsql

I don't trust a WHERE TenantId = @TenantId that has to be typed into every query by every developer. Somebody will forget it, and I don't want to be the one explaining the leak. Row-Level Security in SQL Server 2016 moves that filter into the database: define a predicate function once, attach it to the table with a security policy, and every SELECT, UPDATE and DELETE from any client is filtered.

Below is a working setup with a filter predicate, then a block predicate so users can't insert rows they couldn't see, then the SESSION_CONTEXT pattern for applications that share a single login. Any edition of 2016 or later works. You need a column identifying the owner of each row and some way to know who the caller is.

Sample table and users

CREATE TABLE dbo.Invoices (
    InvoiceId   int IDENTITY PRIMARY KEY,
    TenantName  sysname NOT NULL,
    Amount      decimal(10,2) NOT NULL
);
INSERT dbo.Invoices (TenantName, Amount) VALUES
    ('tenant_a', 100), ('tenant_a', 250), ('tenant_b', 75), ('tenant_b', 900);

CREATE USER tenant_a WITHOUT LOGIN;
CREATE USER tenant_b WITHOUT LOGIN;
CREATE USER app_reporting WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Invoices TO tenant_a, tenant_b, app_reporting;

Users without logins are convenient for testing with EXECUTE AS USER.

The predicate function

The predicate is an inline table-valued function that returns a row when access is allowed. It must be schema-bound and should be kept simple, because it is inlined into every query against the table:

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_TenantPredicate(@TenantName AS sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
       WHERE @TenantName = USER_NAME()
          OR USER_NAME() = 'dbo';

The dbo clause makes sure administrators still see everything. Without it, even db_owner members would be filtered, which surprises people.

The security policy

CREATE SECURITY POLICY Security.InvoicePolicy
    ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantName) ON dbo.Invoices
    WITH (STATE = ON);

A filter predicate silently hides rows from SELECT, UPDATE and DELETE. It does not prevent inserting rows for another tenant, which is what the next step is for.

Block predicates

ALTER SECURITY POLICY Security.InvoicePolicy
    ADD BLOCK PREDICATE Security.fn_TenantPredicate(TenantName) ON dbo.Invoices AFTER INSERT,
    ADD BLOCK PREDICATE Security.fn_TenantPredicate(TenantName) ON dbo.Invoices AFTER UPDATE;

AFTER INSERT rejects new rows whose TenantName would not pass the predicate. AFTER UPDATE rejects updates that would move a row to another tenant. BEFORE UPDATE and BEFORE DELETE also exist and check the row as it was before the change, which the filter predicate already covers in most designs.

Shared application login with SESSION_CONTEXT

Most web applications connect with one login and know the tenant themselves. Have the application set the tenant right after opening the connection:

EXEC sp_set_session_context @key = N'TenantName', @value = N'tenant_a', @read_only = 1;

@read_only = 1 prevents anything later in the session from changing it. Then extend the predicate:

ALTER FUNCTION Security.fn_TenantPredicate(@TenantName AS sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
       WHERE @TenantName = USER_NAME()
          OR (USER_NAME() = 'app_reporting'
              AND @TenantName = CAST(SESSION_CONTEXT(N'TenantName') AS sysname))
          OR USER_NAME() = 'dbo';

Altering the function requires that the policy be dropped or turned off first if it is schema-bound to it; in practice, ALTER SECURITY POLICY ... WITH (STATE = OFF), alter the function, STATE = ON.

Connection pooling is the trap here. sp_reset_connection clears SESSION_CONTEXT, so set the value every time a connection is taken from the pool, not once at startup. I'd put it in the data-access layer where nobody can skip it.

Testing it

EXECUTE AS USER = 'tenant_a';
SELECT * FROM dbo.Invoices;             -- 2 rows, only tenant_a
INSERT dbo.Invoices VALUES ('tenant_b', 1);   -- fails
REVERT;

EXECUTE AS USER = 'app_reporting';
EXEC sp_set_session_context N'TenantName', N'tenant_b';
SELECT * FROM dbo.Invoices;             -- 2 rows, only tenant_b
REVERT;

SELECT * FROM dbo.Invoices;             -- as dbo: all 4 rows

To see which policies exist and what they cover:

SELECT p.name, p.is_enabled, pr.predicate_type_desc, pr.operation_desc, OBJECT_NAME(pr.target_object_id) AS table_name
FROM sys.security_policies p
JOIN sys.security_predicates pr ON pr.object_id = p.object_id;

Errors and surprises

  • Msg 33504: The attempted operation failed because the target object 'dbo.Invoices' has a block predicate that conflicts with this operation. This is the block predicate doing its job: the row being inserted or updated fails the predicate.
  • Msg 33265: The security policy cannot be created because the predicate function is not schema-bound. Add WITH SCHEMABINDING to the function.
  • Msg 3729: Cannot ALTER 'Security.fn_TenantPredicate' because it is being referenced by object 'InvoicePolicy'. Drop or disable the policy before altering the function.
  • Everyone sees zero rows, including dbo. The predicate has no administrator clause, or it compares USER_NAME() against a login name instead of a database user name. SUSER_SNAME() returns the login, USER_NAME() the database user.
  • Performance drop after enabling. The predicate adds a join or filter to every query. Make sure the tenant column is indexed and the function contains nothing more complex than a comparison or a lookup in a small mapping table.

It doesn't replace application-level checks, but it turns a forgotten WHERE clause from a data leak into an empty result set. Keep the function tiny and always keep an admin bypass.