Row level Security

[cognostechie]

Would anyone want to discuss and share ideas on different ways of applying security at the the row level?

I am working in an environment where the requirement is not just to filter the row based on a user-id or any other key but to do it in a way that the same row can be visible either to one person or multiple people. It is hierarchical. Like this:


    Region - Can have many zones.
    Zones  - Can have many Directors
    Director - Can have many Managers
    Manager - Can have multiple Salesreps working under him.

The Salesrep has the lowest level of access so he/she can see only his/her data. The Manager can see data of all Salesreps working under him. The Director can see data of all Managers under him and so on.

I know it can be done using a combination of Groups, dimension and fact tables using Keys etc. and I do have a solution in place. It is based on a combination of a Session Parameter capture the Login User-id, Parameter Maps , a User Dimension and a Fact table. I am trying to know other ways to understand if it can be done in a better way.

Posting the solution will require almost writing a book or at least a chapter so want to know if anybody has the will and time to discuss this? If yes, I would be very happy to take this further and post my solution.

Thanks !

[CognosPaul]

Can you make a table that has userid and permitted salesrep ID? If you can, import that table in, join onto the fact and add an embedded filter that filters the fact table by the security table. Then alias shortcut that security table and repeat that on the org hierarchy dim.

[cognostechie]

Can you make a table that has userid and permitted salesrep ID? If you can, import that table in, join onto the fact and add an embedded filter that filters the fact table by the security table. Then alias shortcut that security table and repeat that on the org hierarchy dim.

Not sure what you mean by this. The security table will be a Dim and joining that to Org Hierarchy Dim would be a Dim to Dim join which will have no connection to the Fact table because this will be an alias not using the security table that is joined to the Fact. Moreover, the Fact will be filtered for one User-id/Salesrep-ID only so how will the person at a higher level (Director) see the data for the entire region?

[bus_pass_man]

errr...no.

There's a fairly well defined best practice about data security and you might find it a worthwhile activity to look at stuff like this:

http://www-01.ibm.com/support/docview.wss?uid=swg21341786

[cognostechie]

Thanks, but that's the simple stuff when the record of the Fact table has to be filtered based on who is running the report.

In this case , what is required is this:

If a Salesrep runs the report then he/she can see his/her data only but when the Manager runs the report then he/she should be able to see every Salesrep's data who is working under him. At that time, the Login ID of the person running the report is the Manager's ID but the Fact table cannot contain his key value in the record because that value will be the Salesrep's ID otherwise the Salesrep won't be able to see the data.

So, when the Manager runs the report then the filter has to be applied to retrieve multiple rows containing an ID that does not belong to the Manager (the person running the report).

[CognosPaul]

In the scenario I'm suggesting you would have two fields, userid and salesrepid. It would be a very long table. Each userid has every possible sales rep that is permitted. So a sales rep would have a single row, and a manager would have multiple rows.

Once that table is created make a join between securitytable.salesrepid = fact.salesrepid and add an embedded filter to the fact with securitytable.userid = #sq($lookup)#

That embedded filter will force the join to the security table at all times.

You repeat the same procedure for the org hierarchy so any prompts will also only show the permitted sales reps.

The security table itself is never exposed in the model, it only exists to filter the fact and org dim.

[cognostechie]

So I guess you are saying that user-id would mean the person running the report and salesrep-id would be the person for whom the data will be visible to the user-id. So if a Manager can see data of 3 salesreps then the Dim table would be like this:

User-id                 Salesrep-id
--------                 -------------

101                         10001
101                         10002
101                         10003

In this case, is the user-id the ID of the Manager who is running the report? So if this Dim is joined to the Fact with Salesrep-id, won't that create many-to-many relationship between Dim and Fact? If so, the fact data will multiply.

[CognosPaul]

That output is exactly what I'm suggesting. The user-id would be the username or whatever lookup your authentication source is returning and the salesrep-id matches whatever is used in your model.

It would only create a many-to-many if the salesrep-id appears twice for a single user-id. Remember, in this model the query will always be filtered by a single user-id. The filter forces the join to that table, so you can even use the data security functionality to force the filter for specific groups. When the filter doesn't exist, it won't join to the dim and you won't have a crossjoin problem.

[cognostechie]

If the filter is on User-id then it will fetch all 3 rows in the Dimension because the User-id is the same. My authentication source will give me the User-ID which is what I think you also mean. If the filter is on Salesrep then the Manager running the report(User-ID) will not be able to see data for all of his salesreps.

What is it that I am not able to understand?

[cognostechie]

Oh I see ! Yes, it won't be a many-to-many. The join is on Salesrep-id which will have only one record in the Dim.

Ok, that works.

[cognostechie]

This is what I have done so far. It's a little different than your method and it requires having only one record for a User-id in the Dim table.

User Dim:

User-Id  Access_Method  Region_Key Team_Key
-----------------------------------------------------
101          Region              801             901
102          Team                801             901
103          User                 801             901

The user 101 is a Director so he can have access for the entire region (all salesreps data in that region)
User 102 is a Manager and ca have access to his team's data (all salesreps in his team only, not for other salesreps even if in the same region)
User 103 is a Salesrep and can have access to only his data.

Let's see how it goes.

Your method is definitely worth exploring.

[CognosPaul]

The solution I suggested wouldn't require a parameter map or a dynamic join, it would always be a straight join on salesrep-id.

Setting it up the way you have it presents a few options, one of which allows you to avoid making a join at all.

Filter the user-dim on user-id. user-id=#sq($account.personalInfo.userName)#

This will always return a single row.

Create 3 param maps, with User-id as the key for all of them, on Access_Method, Region_Key, Team_Key. The lookup will not be slow as it's pulling from an already filtered table.

I'm assuming you're using DQM, so you can do the following:

#case $accessMethod($account.personalInfo.userName)
when 'Region' then 'fact.region_key = ' + $regionKey($account.personalInfo.userName)
when 'Team' then 'fact.team_key = ' + $teamKey($account.personalInfo.userName)
when 'User' then 'fact.user_id= ' + sq($account.personalInfo.userName)
else '1=2'
end
#

[cognostechie]

Yes, the user-dim is filtered to get only one row, that of the user running the report.

The filter is working fine but I will try the macro using the case statement also.