RLS Filters

Question

Post reply

RLS Filters

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
March 16, 2016 at 10:55 pm

#72578

Comments posted to this topic are about the item RLS Filters
March 16, 2016 at 11:02 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

I think there's a typo:

CREATE SECURITY POLICY rls_CustomerFilter

ADD FILTER PREDICATE dbo.rls_customer_check(customerid)

ON dbo.CustomerAccessList;

GO

"customerid" instead of "username" passed as parameter to the inline function.

Anyway, the typo doesn't change the correct answer.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 2

CREATE SECURITY POLICY rls_CustomerFilter

ADD FILTER PREDICATE dbo.rls_customer_check(customerid)

ON dbo.CustomerAccessList;

GO

the above is not included "WITH (STATE=ON)". I thought security policy is not enabled yet and insert succeeds.

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 3

I don't understand how this can work, even if you do pass 'bsmith' rather than 5 into the check function (can '5' be cast as a sysname, anyway?).

The function returns 1 if the supplied name matches the value returned by USER_NAME(). If I run that on my system, I get 'dbo', which according to books online is what you get for a user that is a member of the sysadmin role.

Even if USER_NAME() returned 'bsmith' for that user, what does this achieve? It appears to have the effect of allowing users to only give themselves access to the specified customer. But it does not stop anybody from doing so, does it?

Am I missing something?

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 4

Bob JH Cullen (3/17/2016)
I don't understand how this can work, even if you do pass 'bsmith' rather than 5 into the check function (can '5' be cast as a sysname, anyway?).
The function returns 1 if the supplied name matches the value returned by USER_NAME(). If I run that on my system, I get 'dbo', which according to books online is what you get for a user that is a member of the sysadmin role.
Even if USER_NAME() returned 'bsmith' for that user, what does this achieve? It appears to have the effect of allowing users to only give themselves access to the specified customer. But it does not stop anybody from doing so, does it?
Am I missing something?

As from BOL, FILTER PREDICATE is active only on READ and not in WRITE. For "insert" o "update" you should use BLOCK.