solved - Row Level Security based on membership in AD Group or ServerRole

  • Hi,

    I'd like to implement row LevelSecurity.

    Users are mapped to one (or more!!!) Groups (either AD Groups or ServerRoles, does not matter which to use)
    Each Group should only see the SalesFacts for the corresponding Company.
    How can this be achived?

    I tried the following, but it did not workound because of binding Errors to sysObject Tables:

    use master
    --create new database
    drop database if exists  [ttt_RowLevelSecurity];
    create database [ttt_RowLevelSecurity];
    GO

    USE [ttt_RowLevelSecurity]
    GO

    --Create users (later, there will be more Users of course
    CREATE USER [UserCompanyA] WITHOUT LOGIN;
    CREATE USER [UserCompanyB] WITHOUT LOGIN;

    ALTER ROLE [db_datareader] ADD MEMBER [UserCompanyA]
    ALTER ROLE [db_datareader] ADD MEMBER [UserCompanyB]

    create role AllowCompanyA
    create role AllowCompanyB

    --add Users to roles (IMPORTANT: Later there will be users who belong to more than one Role!!)
    ALTER ROLE AllowCompanyA ADD MEMBER [UserCompanyA]
    ALTER ROLE AllowCompanyB ADD MEMBER [UserCompanyB]

    create table tbSales  (Company varchar(50), Turnover int)
    insert into tbSales values('CompanyA', 100)
    insert into tbSales values('CompanyB', 80);
    GO

    --- Here's what I tried, but it did not work because of binding Errors:

    -- Created a function to get all roles for the User
    -- due to the fact it uses Sys.objects schemabinding is not allowed
    create function dbo.GetGesellschaftFromRole()
    returns table

    as
    return SELECT right(r.name, 8) as CompanyOK
      FROM sys.database_role_members AS m
      INNER JOIN sys.database_principals AS r
      ON m.role_principal_id = r.principal_id
      INNER JOIN sys.database_principals AS u
      ON u.principal_id = m.member_principal_id
      WHERE u.name =  SYSTEM_USER and r.name in ('AllowCompanyA', 'AllowCompanyB')

      /*
    here comes the error:
    Msg 4513, Level 16, State 2, Procedure fn_securitypredicate, Line 5 [Batch Start Line 56]
    Cannot schema bind table valued function 'Security.fn_securitypredicate'. 'Security.GetGesellschaftFromRole' is not schema bound.
    */

    create FUNCTION Security.fn_securitypredicate(@Company AS sysname) 
        RETURNS TABLE 
    WITH SCHEMABINDING 
    AS 
        RETURN SELECT 1 AS fn_securitypredicate_result  
    WHERE @Company in (select CompanyOK from dbo.GetGesellschaftFromRole()) ; 

    --- this would be next if the above would work....
    CREATE SECURITY POLICY SalesFilter 
    ADD FILTER PREDICATE dbo.fn_securitypredicate(Company)  
    ON dbo.tbSales
    WITH (STATE = ON);

  • That part where you have RIGHT(r.name, 8) is likely the reason.   You can't schemabind to a function on a field.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • There are 2 issues
    1. You can not create a function with SCHEMABINDING which reference function which is not SCHEMABINDING. so function 2 cant be created since function 1 is not SCHEMABINDING.
    2. you cant create SCHEMABINDING function which is using system objects. so even if you change the first function to SCHEMABINDING, you cant create it

  • Avi1 - Wednesday, August 23, 2017 10:39 AM

    There are 2 issues
    1. You can not create a function with SCHEMABINDING which reference function which is not SCHEMABINDING. so function 2 cant be created since function 1 is not SCHEMABINDING.
    2. you cant create SCHEMABINDING function which is using system objects. so even if you change the first function to SCHEMABINDING, you cant create it

    yes, exactly the Point 2 is the Problem. is there any Workaround to get Information about roles /AD Groups of the current user without using system-objects or any other stuff that Permits schemabinding?

  • Andreas Michael - Thursday, August 24, 2017 12:45 AM

    Avi1 - Wednesday, August 23, 2017 10:39 AM

    There are 2 issues
    1. You can not create a function with SCHEMABINDING which reference function which is not SCHEMABINDING. so function 2 cant be created since function 1 is not SCHEMABINDING.
    2. you cant create SCHEMABINDING function which is using system objects. so even if you change the first function to SCHEMABINDING, you cant create it

    yes, exactly the Point 2 is the Problem. is there any Workaround to get Information about roles /AD Groups of the current user without using system-objects or any other stuff that Permits schemabinding?

    As per my understanding you can achieve only through system tables. there is a workaround, you can dump the data from that query in a table (may be couple of times a day) and use that table in you function.

  • As suggested by Avi1 I put the result of dbo.GetGesellschaftFromRole() to a Table wich can be accessed with schemabinding.
    Works, a littlebit complicated because of the need to refresh the table, but OK.

    After investigating, I found another way to solve my Problem.
    with the help of "is_rolemember"

    the function Security.fn_securitypredicate in the above example is replaced by the following; works fine:

    CREATE FUNCTION Security.fn_securitypredicate (@Company AS SYSNAME)
    RETURNS TABLE
     WITH SCHEMABINDING
    AS
    RETURN

    SELECT 1 AS fn_securitypredicate_result
    WHERE (
      @Company = 'CompanyA' AND IS_ROLEMEMBER('AllowCompanyA') = 1
      )
     OR (
      @Company = 'CompanyB' AND IS_ROLEMEMBER('AllowCompanyB') = 1
      )

  • For users who are members of db_owner group IS_ROLEMEMBER ignores the DB Role membership and falsely returns '0', so unfortunately it's useless in those cases. What if I want to apply Row-Level Security also for the db_owner users?

    USE [master]
    GO

    CREATE DATABASE [Test_IS_ROLEMEMBER]
    GO

    USE [Test_IS_ROLEMEMBER]
    GO

    CREATE ROLE [Test_Role]
    GO

    CREATE USER [DOMAIN\AdGroupName] FOR LOGIN [DOMAIN\AdGroupName]
    GO
    ALTER USER [DOMAIN\AdGroupName] WITH DEFAULT_SCHEMA=[dbo]
    GO
    ALTER ROLE [Test_Role] ADD MEMBER [DOMAIN\AdGroupName]
    GO


    EXECUTE AS LOGIN = 'DOMAIN\UserName'
    SELECT
    SUSER_SNAME()AS [SUSER_SNAME]
    ,IS_MEMBER('DOMAIN\AdGroupName') AS [AdGroupName]
    ,IS_ROLEMEMBER('db_owner') AS [db_owner] -- this shows 1
    ,IS_ROLEMEMBER('Test_Role') AS [Test_Role] -- this shows 0 even though I made DOMAIN\UserName a member of Test_Role (???)
    REVERT

Viewing 7 posts - 1 through 6 (of 6 total)

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