Implementing hierarchical row level security

[Former Member]

Hi

I'm implementing row level user security in universe with the help of @variable ('BOUSER').

I have user table and join to domain table on domain id.

That means let's say if user table has entries like

user id = 1

domain id = 101

then user name sysadmin who has id =1 has access to domain name = '/' having domain id =101

In user table i have 2 users 1. Sysadmin 2. Report

sysadmin is related to domain id 101 which is root.

and report is related to domain id 301 which is some sub domain hence report user has access to only part of the entire system ..

Since sysadmin has access to root that means he has access to entire system ..

how can i implement this using @variable (BOUSER)

User table

User ID Domain Id User Name

1 101 Sysadmin

2 301 Report

Domain Table

Domain id Domain Name Parent Domain id

101 / 101

301 Production 101

401 Development 101

501 QA 301

601 Sales 401

as per this data Report user will have access to Production and Sysadmin to /

However requirement is : Report user should have access to Production and its subdomains

with this rule sysadmin will have access to all the domains .

Please help.

Thanks

-p

[Former Member]

Hello Pranjal,

Can you mention how you wanted your output to come?

In the above example if "User Table" is linked with "Domain Table" with "DomainID".

and I wanted data for "User" --> "Report"

so I will define row level security as: -

Tools--> access restriction -->manage access restriction

1) Select Table "User table"

2) Click on Where button and select object "UserName" from table "UserTable"

UserTable.UserName = @variable('BOUSER')

do all the check and apply this restriction on any group (basically the user who are going to consume the reports)

So if user "Sysadmin" will login and query for objects "UserID", DomainID", "UserName", "Domain Name", "ParentDomianID"

He will see 1, 101, Sysadmin, /, 101

If this does not help you out, you have to figure how the tables are joined and use filters to get the data.

Thanks

[Former Member]

Hi Vivek,

Thanks for reply,

I will test the solution u provided

Could you also help to use @varible('BOUSER') insted of universe restriction ?

Thanks

-P

[Former Member]

Hi,

You can apply row level security by following steps by using @bouser:

1) Universe --> Tools --> Manage Security --> Manage Access Restrictions

2) Select u201CCreate New Restrictionu201D --> Rows > Add> Select the table on which you want to apply the restriction and in the where clause put the condition on the column on which you want to implement security=@variable(u2018BOUSERu2019)

3) Apply this restriction to the users/groups to which you want to impose and export the universe

4) For example if we have users with country name and if we login to the infoview with these user names and creates a report then we can see the data related to country with which we logged in

5) The query will be generated like below

SELECT

Country.country_id,

Country.country

FROM

Country

WHERE

( Country.country=@variable('BOUSER') )

76) During run time it replaces @variable(u2018BOUSERu2019) with the user who logged in to the infoview

Ex: if we login with UK user then query will be

SELECT

Country.country

FROM

Country

WHERE

( Country.country='UK' )

Note: With this approach the restriction will be applied to all the reports which are based on this table for the users to whom the restriction is applied