I have one universe on which i want to implement row level security for two groups of users.
One set of users that can see sales data based on the customer sector. We have created groups for users based on the sector. We then set the restriction on the rows in the universe based on the groups to restrict the data that user can see.
The other group of users can see data based on their items that they manage. For this group we have created a user security table that uses the user login and the mapping items that they can view.
If we have two separate universes then the above two options work fine as only one strategy of security is used. The challenge we are finding is trying to use the same universe for both groups of users.
Yes … for the second group of users we are using a dedicated security table joining on the BOUSER variable, as the number of users for that group is very minimal and is easy to maintain. The first group however it is easier to maintain by using row level security for the groups and including all these users in the security table would make it huge and would greatly impact performance.
Sorry for the terminology. I meant the same row-level restriction technique used in your #1. I haven’t tried it, but it seems you could enforce the security table join and @variable(‘BOUSER’) restriction that way, instead of literally within the universe.
We have found one soution but i fear it will have impact on performance for second group of users but so far it is not that noticeable as we have limited users on the system.
We removed the enforcing of the use of the security table within each measure object in the ‘Tables’ list and instead forced the main dimensions such as customer category and item tables to join them instead.
For the first group of users we created a restriction that contained filter on the customer category dimension table only and not on any fact tables as the customer category dimension table would be used in the generated query. An important consideration here was that the security should be implemented according to the current category of customer and business users agreed this is what they wanted. There could have been a need to enforce security on the fact tables too on customer category if business requirement was otherwise.
For the second group of users we created a restriction on the rows by implementing the following condition for all fact tables:
…
WHERE Sales.ItemCode IN (SELECT UserItem.ItemCode FROM UserItem WHERE UserItem.LoginID = @Variable(‘BOUSER’))
The maximum number of items for a user from the second group was about 100 !!!
This solution is working fine so far.
NOTE:
We did have to compromise in one situation though for this solution. For one fact table we had 3 summary tables thus we implemented aggregate awareness. One of the summary tables did not have Item so we could not implement the above condition on that summarised fact table. This table was intended for a large group of users who heavily look at total sales of salesman so there was no need to put the item in that particular summary table. If user from second group selected dimensions that the aggregate awareness engine would choose this particular table no row level restriction would be applied. It would be nice if the aggregate aware engine would also consider the restrictions before deciding which summary table to use. I guess we can send this to BO.
If anyone knows of any better solution please let us know or maybe we are doing something wrong that we had to compromise on the aggregate table?
on the same line I have one question though in XI R2 if I use this as a self join on the table, “view.user_id = @Variable(‘bouser’)” would this prompt the user to enter the value or is it seemless
and I have another queswtion too, can I have secuirty in different tables and can I use them in a universe, or all secuirty level should be in one table
No by using the variable @variable(‘bouser’) it would simply replace it with the loginID of the user during the generation of the SQL without prompting the user to enter it.
Yes you can use security on other tables without having to dedicate a user security table.
You would do this by creating a user group and then enforcing row level security by defining a restriction that would be applied to that group.
For example lets say you have a fact table inserted into your Universe:
You would create a row restriction on this table as:
SalesFactTable.CustomerCategory = 1
Yuo would then apply this restriction to the group lets say Supermarkets. All users under this group when using any measures from this SalesFactTable, it would automatically apply this restriction and filter accordingly.
(BOB member since 2006-02-19)
(BOB member since 2002-09-19)
(BOB member since 2002-09-19)