SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
cs_source
cs_source
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1075 Visits: 262
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
andycadley
andycadley
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1668 Visits: 1374
WHERE USER_NAME() <> 'Specfic User' OR ColumnToCheck=1

should do the trick
cs_source
cs_source
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1075 Visits: 262
Thank you andycadley, this is wonderful. So simple yet so overlooked..... Smile
cs_source
cs_source
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1075 Visits: 262
@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);
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search