RLS Filters

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719936

    Comments posted to this topic are about the item RLS Filters

  • This was removed by the editor as SPAM

  • Carlo Romagnano

    SSC-Insane

    Points: 21968

    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

    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

    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: 21968

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719936

    Correct, you need a BLOCK predicate to stop writes.

    Sorry for the typo, I'll correct things.

  • daniel.plocinik

    Hall of Fame

    Points: 3288

    Appreciate the question Steve.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thank you for the question.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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