BusinessObjects Board

Report Security

I think so but restricting data from another column of this table.

Regards
Sebastien

Let me explain more in detail. Salary information coming from Same column/table for all users. My requirement is if user A pull this report he can view only his salary not for others. same goes for other users too. I understand that we can apply row level security for each column. But here data coming from same column. Is it possible?


Philip (BOB member since 2006-03-02)

You don’t apply ROW level security for colum. You select the column and use a where clause to restrict the records. The column will show up in the report, but not the rows that have been filtered. Does it make sense??


zack :us: (BOB member since 2007-08-02)

It meant I need to apply WHERE (where USER inlist(A, B, C, D, E) condition. Does it meant that when USER A open the report, report will display data only for USER A and ignore data for other users B, C, D & E?


Philip (BOB member since 2006-03-02)

Start here, please.


Andreas :de: (BOB member since 2002-06-20)

Thanks Andrea providing me PPT. But it does not work for me. My requirement is how to restrict to users to view others salary information from same report if user A open the report. Please take note that Salary information coming from same column for all users.


Philip (BOB member since 2006-03-02)

Before dismissing Andreas suggestion, did you go through the document?? You just spent a couple of minutes (at the most) reading the document.

You misunderstood my point.
Please go through this post:


zack :us: (BOB member since 2007-08-02)

Well, it should work, that is what almost everybody uses… worldwide.

Assume I have a Salary (fact table) defined like this:

Date,
Salary_Amount,
Recipient

Then you need a security table mapping the BOUserID, see @Variable(‘BOUSER’), to Recipient, for example:

BOUserID,
Allowed_to_See_Recipient

You must build this security table (via ETL).

Then join your salary (fact) table to this security table (on Recipient = Allowed_to_See_Recipient) and create a self-restrictive join along the lines:

My_Security_table.BOUserID = @Variable('BOUSER')

Note:
For this to work all reports must be set to refresh on open and SQL must not be editable.

As an alternative look at Virtual Private Databases (VPD) in Oracle, etc.


Andreas :de: (BOB member since 2002-06-20)

Thanks Zack. I had read it properly. I am not sure either you understand my requirement or not. I meant are you with me in same track?


Philip (BOB member since 2006-03-02)

Well, what is the exact structure of the Salary table? Are you working against a data mart or OLTP system. If OLTP, which one? PeopleSoft?

Again, row-level security is what almost everybody, worldwide is using when they have to secure the data on a per person basis, for example:
A) Sales representatives are only allowed to see data of clients they have been assigned to.
B) Department managers are only allowed to see the budget of their projects and not of projects belonging to other departments.
Etc.


Andreas :de: (BOB member since 2002-06-20)

Let me try your way Andrea and let you know. Appreciate your help.


Philip (BOB member since 2006-03-02)

Nope, I am not.

The same concept is explained in detail in the PPT document. :wink:
You are confused. Be patient and go throught the post(see above) to get yourself familiar.


zack :us: (BOB member since 2007-08-02)

What are the Recipient and Allowed_to_see_Recipient?


Philip (BOB member since 2006-03-02)

Philip, your requirement is basically to provide one report for all users. Each of the users will see different data on the report based on his/her access right. Is that correct?

If it is correct, you should consider setting up data security on the datamart. This is how we set up our data security. We have 20,000+ users in the field. Everybody is using the same set of reports. But when the manager of District A runs a report, he/she will see the data for his/her district only. And when the manager of District B runs the same report, he/she can see the data for his/her district only. It is all controlled in the datamart. Database tables are easy to maintain, backup, and modify. It is the BEST way in my humble opinion.


substring :us: (BOB member since 2004-01-16)

The “row-level restriction” approach works well … assuming the data has the proper tag (like employee in this example), and that the BusObj ID is also that same employee ID. Also, row-level restriction works well at RUN-TIME (meaning the SQL enforces the security). If the requirement is to have a single pre-run instance of the report, and have the security applied at VIEW-TIME, then you are out of luck. This is possible with Crystal Reports based on Business Views, but not for all other report types based on universes.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Hi Andres,
I had created a derived table (D1) for BO users information. Below are the fields on this table:
SSN
BO USERID

I had joined this derived table with Fact table, F1. (D1 .SSN=F1. SSN).
I had applied restriction in Derived Table where BO_USERID = @Variable(‘BOUSER’)
(Tools => Manage Security => Manage Access Restriction)

Then I export this universe in CMC.
Here are the out put:

  • I am getting all users salary information. (actually I am expecting my salary information only).
  • Query run time is 3 minutes once and applied above said logic in universe. It will take around 12 seconds in normal way (without implement above logic).
  • Please let me know what I am missing here.

Philip (BOB member since 2006-03-02)

Philip:
A) Implementing security especially using a derived table will cost you some performance.

B) Instead of implementing BO_USERID = @Variable(‘BOUSER’)
via Access Restrictions make BO_USERID = @Variable(‘BOUSER’)
a self-restricting join (a.k.a stub-join) on the security table (derived table in your scenario).

C) Make sure you apply a table hint on alll objects from your salary fact table to force a join to your security table (that is the derived table in your scenario). You must edit each universe objects from your salary fact table, there is a “Table…” button in the bottom right corner, there you can specify additional tables (use the CTRL button to select additional tables, make sure your fact table stays selected!).

Finally, post the SQL code generated by BusinessObjects, you should see the join to your security table there. Post the conetent of your derived table as well.

Note:
Just setting up an Access Restriction is not enough, you must map it to a BOE group as well (all done via Designer).

If you are still facing problems it might be worthwhile to get a consultant for 1-2 hours to help you get started.


Andreas :de: (BOB member since 2002-06-20)

Hi Andreas,
Many Many Thanks for your help. It is working now.
However I have an issue that is when I login as a user I can view only my salary informations only not for others. This is perfect. But when I log in as a administrator I can not view any things. Query returns no data. Is there any way as a BO admins I can view all data?


Philip (BOB member since 2006-03-02)

Hi,

Yes you need to manage this in your security table by adding all the values for the administrator account.

Regards
Sebastien

Administrator and developer both do not have any salary informations in fact tables. I can add both USER IDs on security table. Does it help? As as Administrator or developer, we need to view all users information.


Philip (BOB member since 2006-03-02)