SQLServer, temporarily disabling row level security

Question

I have implemented row level security in SQLServer in Person table (to meet GDPR requirements) so that a basic user can see only subset of personal records. The Person table has some data (RFID tag) that must be unique in the system. So my app checks that there is no duplicate RFIDtag.

How to do this check when RLS is on, because the query only sees a subset of rows, but the RFID must be globally unique ? What could be the best way to run this query with temporarily disabling RLS ?

My first idea is to use a stored function, to perform the check. The function executed as 'sa' user sa could see all rows. Any other (simpler) ideas ?

There is never any need to execute as sa inside the database. EXECUTE AS is a solution here, but you're better off doing that as a specific user with only the permissions it needs (CREATE USER ... WITHOUT LOGIN). — Jeroen Mostert
Maybe I'm thinking too simple, but wouldn't a UNIQUE index also take care of things? You can catch the duplicate error to present a more meaningful error message to the user. As a bonus, that would also be safer than a check-before-insert pattern. — Jeroen Mostert

Doug Doug · Accepted Answer · 2018-03-06T17:42:52

The UNIQUE index is the safest approach, like said in @Jeroen's comment.

Disabling row level security can be needed in other contexts, though. When needed, my suggestion is:

Create an 'admin' user just for this (CREATE USER admin WITHOUT LOGIN;)

Include logic in your validation function to account for this user:

CREATE FUNCTION Security.fn_securitypredicate(@param as VARCHAR(100))  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS Result
    WHERE
        /* Your logic here */ 
        OR
        USER_NAME() = 'admin' ;

This way, when you are impersonating 'admin' user all records are accessible. Any other user, your row level security logic still applies.

To impersonate the user when running a query:

EXECUTE AS USER = 'admin';
SELECT * FROM MyTable;
REVERT; -- Returns to the original user context

SQLServer, temporarily disabling row level security

1 Answers