0

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 ?

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
Heikki W
  • 1
  • 1
  • 1
    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 Mar 06 '18 at 13:43
  • 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 Mar 06 '18 at 13:46

1 Answers1

0

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
Doug
  • 6,322
  • 3
  • 29
  • 48
  • UNIQUE index might be the best approach here, since I cannot impersonate 'admin' working. If user admin has the necessary rights to do the query without RLS, the basic user cannot execute as admin ! – Heikki W Mar 14 '18 at 08:18