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)
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)
WITH (STATE = ON);
Please let me know which direction to take. I am using the example in the MS URL