Row Level Security single role

[pgoum]

Hey all!

I have a report concerning sales and salesmen. There exist 3 roles let's say depending on which every user can see different rows from the sales table. If a user has flag=0 or null then he/she can view everything. Otherwise we look at Items table and examine whether that user appears there. If yes then the user can view the rows that have item_group_code & item_subgroup_code that apply. Alternatively we look at the Customers table and if he/she appears there the user can view his/her customers sales only. 

The desired RLS case is to create a single role and assign all users there so that we don't have to switch from one role to another manually.

So far I have tried this condition on the sales table but it doesn't work.

IF (
    [user_logged_condition] = "View All",
    1 = 1,
    IF (
        [user_logged_condition] = "Items",
        Sales_Fact_View[key]
            = LOOKUPVALUE ( items_access[key_sales], items_access[email], [user_logged] ),
        IF (
            [user_logged_condition] = "Customers",
            Sales_Fact_View[customer]
                = LOOKUPVALUE (
                    customers_access[cust_code],
                    customers_access[email], [user_logged]
                ),
            1 = 0
        )
    )
)

I also share the pbix file I have created: RLS_oneRole.pbix 

Thanks,

Panos

[d_gosbell · Accepted solution]

So your issue is that in both of your LOOKUPVALUE expressions that multiple values could be returned and this function will throw and error in this case. 

You could work around this as follows by adding a second lookup condition for the key_sales or cust_code respectively:

IF (
    [user_logged_condition] = "View All",
    1 = 1,
    IF (
        [user_logged_condition] = "Items",
        Sales_Fact_View[key]
            = LOOKUPVALUE ( items_access[key_sales]
                   , items_access[email], [user_logged] 
                   . items_access[key_sales], Sales_Fact_View[key]
              ),
        IF (
            [user_logged_condition] = "Customers",
            Sales_Fact_View[customer]
                = LOOKUPVALUE (
                    customers_access[cust_code],
                    customers_access[email], [user_logged],
                    customers_access[cust_code], Sales_Fact_View[customer]
                ),
            1 = 0
        )
    )
)

 But from a performance perpective I think a better approach would be to have 3 roles then setup 3 security groups and put one group in each role then add users to one of those groups. That way your role expressions can be simple (and therefore faster), they can be put on the smaller dim tables (and therefore will be faster) and you don't need to edit the roles, you just put users in the different security groups.