relatively new to Universe design. and am looking for general direction on implementing hierarchy security. The scenario follows
Employee A can see a1 budget center information but not b1,c1
Employee B can see b1 budget center information but not a1,c1
Employee C can see c1 budget center information but not a1,b1
Boss 1 can see a1 and b1 budget center but not c1
Boss 2 can see a1,b1 and c1 budget center information
Thanks Bryan
bthomps01 
(BOB member since 2007-01-09)
Hi Bryan,
One of the possible ways of how to implement such security is following:
Every reporting table where you need to apply the security needs to contain budget center information.
Then apply a following type of a self-restricting join on the reporting table:
reporting_table.budget_center IN
(SELECT budget_center
FROM budget_centers_access_rights
WHERE bo_username = @variable('BOUSER')
)
OR
99999 IN
(SELECT budget_center
FROM budget_centers_access_rights
WHERE bo_username = @variable('BOUSER')
)where budget_centers_access_rights is a table (with 2 columns) where you keep information about who can access what. So for instance in the scenario you outlined above, the budget_centers_access_rights table would contain these rows:
A…a1
B…b1
C…c1
boss1…a1
boss1…b1
boss2…a1
boss2…b1
boss2…c1
The first column is a BO username of a user and the 2nd column is a budget_center that the user can see.
If there is a BO user that can see everything, all budget centers, then just simply insert a row for him/her with the 2nd value=99999. The self-restricting join applied on the reporting table will not filter out any rows in this case.
You would need to manually maintain the budget_centers_access_rights table. So you have in it all users that should have some access to the budgets as well as you need to ensure that the budget centers users can see are correct.
Marek Chladny 
(BOB member since 2003-11-27)
Hi guys,
why don’t we use Row restiction concept over here?
I think Row restriction will work out here which is avialble in desiner itself after 6.5. why we need to add two more fields in the source table? It will create duplicate record in the source also. i think this is not a good design practice.
Thanks & Regards,
Pradeep
bopradeep 
(BOB member since 2009-08-03)
Hi Pradeep,
I have implemented this type of security in few BO/DWH installations and it all worked great. It gave BO/DWH admins better control over who can access which kind of information. And of course there were no duplicated records, otherwise it would have been not implemented.
Of course, you can use/implement row restriction concept, if you like.
Marek Chladny (BOB member since 2003-11-27)
Pradeep’s solution, though, is predicated on the assumption that each of the different user and boss types are in separate groups. GroupUserA would only have access to budget_center a1. GroupBoss1 would have access to budget_center a1 and b1. GroupBoss2 just wouldn’t have a row restriction.
If you have a low cardinality and static data (i.e., few budget centers, and the budget centers don’t change often) this can be a workable solution. However, if you have a large number of budget centers (like, say, store locations in a major retail chain), then the number of groups you need to manage grows tremendously. If the list of budget centers changed frequently, you will need to update both the list of groups each time the list changes, and go in and edit the row restrictions. This can get cumbersome.
Personally, I much prefer Marek’s solution. It is very extensible, very easy to hand the administrative duties off to another person, and allows you to keep your group structure a bit cleaner.
Lugh (BOB member since 2009-07-16)
And another advantage is that you set the access rights in the external table (budget_centers_access_rights in the above scenario) once and then you can use it in more universes.
On the other hand, row-level security needs to be set and then maintained in every universe individually.
Marek Chladny (BOB member since 2003-11-27)
Okay guys, What ever I given also one type of hadling your reqirement. But upto us to decide what solution is best for our project(requirement) bcz we only knows how the data flow in our database.
Thank you very much… 
bopradeep (BOB member since 2009-08-03)
I was thinking about this method with our current development requirements (Lots of external clients who are only allowed to see their own data and maintain their own access groups outside of BOBJ).
However a thought occured to me, and again because I don’t have BOBJ installed just yet, I have to go from memory.
Wouldn’t the method described above by Marek also require you to set permissions in the viewer to ensure that all clients are denied access to “Java Report Panel: Edit SQL”. Otherwise I’m thinking they could edit the SQL (which I believe will carry over the restricted join syntax) and modify it at whim. (I highly doubt anyone will be doing that, but all it takes is one client to figure it out for us to be involved in a lawsuit, we deal with payroll information). Or is the restricted join not part of the SQL built by the query panel?
If I’m correct… is there any other method where a savy client might figure out a way around that restricted join? Some other permission I might have to remember to lock down in the CMC?
JPetlev (BOB member since 2006-11-01)
You are correct. Someone with “Edit SQL” can, in fact, override the row restriction. I generally deny both Edit and View SQL for pretty much that reason (to the annoyance of a few of my users who are convinced that they are super SQL writers).
As far as I know, that is the only way to do so. Other than the obvious methods, of course, such as hacking the database to change their own permissions, or logging in as someone else. But, that’s handled on a whole different level.
Lugh (BOB member since 2009-07-16)