Row Level Security (Best Practices ?)

[system]

BO : XI R3.1
DB : SQL server 2005 SP3

Happy New Year Everyone ,

We’re looking at ways to implement a security model on a new XI R3 platform whereby users can see their own data while accessing the same universe (so some form of row/class/uni level security is in place), I have already read this post, this post, this post, and that post (amongst others), but I still came off undecided and would really appreciate your feedback on best practices, and if starting from scratch, which would be the most ideal approach to implementing a user/group security model on BO.

Specifically, we have 52 Counties, and each county would have a resident report developer, and a number of report readers. There would be no universe designers or BO admins in any of the Counties (we would carry out those tasks), so 52 counties, with each county having one report developer and lots of people just reading the reports, all of them accessing the same universe, but getting their own county specific data out of it.

Now, I have looked at Dwayne’s wonderful Security For Mere Mortals PowerPoint presentation, and created profiles for report developers and report readers accordingly.

My main point of confusion, is how it all ties together. To keep it basic, let’s say we have only 1 universe, and we would need each County to see its own data from it, so the row level security implementation can be considered. However, it is a transactional set up, and thus has many tables, putting in stub joins, or defining filters is quite a task (we also have a lot of users within those counties so maintenance would be an issue), I looked at Dave Rathbun’s article on Class or Universe Level restriction, but it talks about “leverage the @variable(’BOUSER’) restriction to apply security to the entire universe”, but we don’t have a security table. Reading the above threads it would seem a good idea to build one, so if we do go ahead with building one…

Questions :

1. What does a security table normally consist of (all the BO users, and the key used to bring in the data relative to them ?) do we just look at all our users, create an entry for them in this table ? or is there some quick export way of doing this ?. Also, when we have a new user or someone leaves, would this table need manual maintenance ?.

2. When we build this table, with all the usernames, and say County numbers next to them, does this table join up to fact tables or dimension tables ? some posts said facts others said dimensions (or does it even matter ?).

3. (say we have different databases, different systems to report from) Does this mean we need a copy of the security table in every database we report from ?.

4. When we create a pre defined condition that is a Universe wide restriction, how does it get used ? Dave’s article states “I could create a hidden class in my universe, create any number of predefined conditions as universe filters, and leverage the @variable(’BOUSER’) restriction to apply security to the entire universe. No matter which objects a user selects for their query, my security would get applied 100% of the time.” can someone please explain further ? so we if we create predefined conditions in a hidden class, we wouldn’t need to put them in the report for them to be activated ?, or do they somehow work without needing to put them in the report ?

Thank you in advance for any feedback/tips/ideas on how best to implement row level type of security in a BO environment.

Regards,
Veronica

---

Veronica (BOB member since 2002-11-22)

[system]

@Veronica

1. What does a security table normally consist of (all the BO users, and the key used to bring in the data relative to them ?) do we just look at all our users, create an entry for them in this table ? or is there some quick export way of doing this ?. Also, when we have a new user or someone leaves, would this table need manual maintenance ?.

Should consist of all BO user IDs (column 1) and in column 2 the granted country IDs for example. And yes, you will have to maintain this table manually.
Example:

BO_USER_ID    CountryID
user1           USA
user1           UK
user1           GER
user2           IT
user3           FR
user3           CAN
user4           IRE
...

I can only recommend VPD (Virtual Private Databases) which Oracle offers, and I am sure other major DBMS offer this functionality as well.

2. When we build this table, with all the usernames, and say County numbers next to them, does this table join up to fact tables or dimension tables ? some posts said facts others said dimensions (or does it even matter ?).

It depends on what you want, if a user should not even see just all countries (even without the facts) then join it to the dimension table. Otherwise join it to the fact tables.

3. (say we have different databases, different systems to report from) Does this mean we need a copy of the security table in every database we report from ?.

Yep, or create a data warehouse as it should be

4. When we create a pre defined condition that is a Universe wide restriction, how does it get used ? Dave’s article states “I could create a hidden class in my universe, create any number of predefined conditions as universe filters, and leverage the @variable(’BOUSER’) restriction to apply security to the entire universe. No matter which objects a user selects for their query, my security would get applied 100% of the time.” can someone please explain further ? so we if we create predefined conditions in a hidden class, we wouldn’t need to put them in the report for them to be activated ?, or do they somehow work without needing to put them in the report ?

Just works beautifully, just try it out (requires BOE XI R3.1 or 3.2, I am not sure which one, do not forget to check the appropriate check box in the predefined condition as shown in Dave’s blog).

Note:
At a large telecommunication company overhead of implementing row-level security has been estimated at about 20% CPU time, when running queries.

---

Andreas (BOB member since 2002-06-20)

[system]

Thank you so much for your response Andreas, I created a dummy table, with a few users, tried the pre-defined condition and set it to universe level restriction and it worked !! I didn’t even need to put the condition in the report, so it’s great.

Now, what is the best way to extract the usernames of all of our BO users ? (XI R2 and XI R3.1 we have 2 environments both have the user list on them, so whichever method works I can apply).

I’m trying now to get a list of all the users, so I can create a script for the security table, but it would be great to just generate an extract of all the users as opposed to looking each one up then typing the name in an INSERT statement.

---

Veronica (BOB member since 2002-11-22)

[system]

You can refer to this excel file with a bit of VBA code to extract from a CMS the complete list of user and the list of groups each user is member of.
Topic name : BOXI UserList & Group Extraction …It is available in BOB’s Downloads section

---

Raghvendra Deshpande (BOB member since 2008-05-26)

[system]

At a large telecommunication company overhead of implementing row-level security has been estimated at about 20% CPU time, when running queries.

Thats shocking and noted for further reference, any idea of the amount of rows in the DB?

---

Mak 1 (BOB member since 2005-01-06)

[system]

@rdeshpande

You can refer to this excel file with a bit of VBA code to extract from a CMS the complete list of user and the list of groups each user is member of.
Topic name : BOXI UserList & Group Extraction …It is available in BOB’s Downloads section

Excellent, thanks for that, I’ll have a look

---

Veronica (BOB member since 2002-11-22)