Row-Level Security: for only 1 user or table or view?

Question

Post reply

Row-Level Security: for only 1 user or table or view?

cs_source

Default port

Points: 1459
More actions
September 12, 2018 at 3:00 pm

#363631

Hello,
Looking at row level security i understand the concept and the example works great following the MS tutorial.
https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017
With that in mind I have an issue and I have tried 2 items:
Issue: How do I apply the security policy for only 1 user and leave all other users with full access?
Try 1: Add all the other users of the DB in the WHERE statement:
ALTER FUNCTION RowLeveSecurity_Brazil.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE (USER_NAME() = 'SPECIFIC USER 1' AND @ColumnToCheck = 'test') OR USER_NAME IN 'LIST ALL OTHER USERSNAMES?'
Try 2: Create a view and provide the 1 user access to the view only and apply the policy there. All other users will query the table.
CREATE SECURITY POLICY RowLeveSecurity_Brazil_SalesFilter
ADD FILTER PREDICATE RowLeveSecurity_Brazil.fn_securitypredicate(SalesRep)
ON [dbo].[Vw_Sec_Sales]
WITH (STATE = ON);
Please let me know which direction to take. I am using the example in the MS URL
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5296 More actions · Answer 1

WHERE USER_NAME() <> 'Specfic User' OR ColumnToCheck=1

should do the trick

cs_source Default port Points: 1459 More actions · Answer 2

Thank you andycadley, this is wonderful. So simple yet so overlooked..... 🙂

cs_source Default port Points: 1459 More actions · Answer 3

@andycadley
Can i ask that if i wanted to apply the policy to multiple tables (Vw_Sec_Sales and Vw_Sec_Orders), would the best way to this?

I'm sure it is in this command and i am trying to add another line for add filter or add an AND after the ON statement,

CREATE SECURITY POLICY RowLeveSecurity_Brazil_SalesFilter
ADD FILTER PREDICATE RowLeveSecurity_Brazil.fn_securitypredicate(SalesRep)
ON [dbo].[Vw_Sec_Sales]
WITH (STATE = ON);