2

SQL Server 2016 supports row level security, which is great. However all of the samples assume that the username is inside the table you wish to apply security to (see example below- from MSDN https://msdn.microsoft.com/en-us/library/dn765131.aspx).

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) 
ON dbo.Sales
WITH (STATE = ON);

If I had a more normalised schema, my username would be in a separate table, using an ID as a foreign key. In this situation, how would I use row level security to check for a user name? Can I add a join to the filter predicate?

1 Answer 1

2

The function can have table access, just remember that this will add additional overhead to every single query.

CREATE FUNCTION Security.fn_securitypredicate(@SalesRepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
      FROM dbo.SalesReps AS sr
      WHERE sr.SalesRepID = @SalesRepID
       AND (sr.name = USER_NAME() OR sr.name = 'Manager');
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.