row-level security

security Supervisor (4.x - 6.x)
1

Hello. You can force a link between the GL_CODE_COMBINATIONS table and any
other required table. I have been doing this at an object level in
Designer. For example, say SEGMENT3 exists on the GL_CODE_COMBINATIONS and
SEGMENT5 exists on a table called GL_OTHER. Your row level restriction will
be WHERE GL_CODE_COMBINATIONS.SEGMENT3 = ‘0500’ as it currently is. In
Designer, select the object which corresponds to SEGMENT5 of GL_OTHER. In
the edit properties window, select the definition tab. Click on the tables
button at the bottom right. The GL_OTHER table should be highlighted. Hold
down the control key and also select the GL_CODE_COMBINATIONS table. This
will force the join between the GL_OTHER and GL_ CODE_COMBINATIONS tables
whenever SEGMENT5 and no object from the GL_CODE_COMBINATIONS table is
selected. I also don’t know if you are applying row restrictions from
within Designer (at the object level) or within Supervisor (at the class
level) but you will have more success in the long run if row restrictions
are applied using Supervisor. Hope this helps.

Best regards,

Crystal Golding
Senior Systems Analyst
Management Information Section
University of Queensland
Brisbane Australia 4072
E-mail: c.golding@mailbox.uq.edu.au
Web: www.mis.jdstory.uq.edu.au
Phone: +61 7 336 57289
Fax: +61 7 336 58202

Listserv Archives (BOB member since 2002-06-25)

2

Hello!

What is the most efficient way to implement row-level security, when retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is able to retrieve based on, say, the user’s position in the Organization, in the most efficient manner.

Thanks,
Saraswati

Listserv Archives (BOB member since 2002-06-25)

3

I don’t know about most efficient, but in our environment we use views that shift based on the user logging in… our connection from the universe uses BOUSER to connect, completes the link.

Just a thought,
Brent

Hello!

What is the most efficient way to implement row-level security, when retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is able to retrieve based on, say, the user’s position in the Organization, in the
most efficient manner.

Listserv Archives (BOB member since 2002-06-25)

4

There’s a paper that explains the BOUSER/BOPASS concept very well, from three of four different strategies. It’s written by Clive Brennan from The Imperative Group. I have a copy of it that was faxed to me - if anyone is interested I can forward the eFax attachment to you (*).

Paul

    • Please note - if you want the paper, please reply to me directly, not to the list. Thank you. :slight_smile:

Listserv Archives (BOB member since 2002-06-25)

5

Thanks to all of you who replied.
We are currently not set up to connect to Oracle(our database engine) as BOUSER/BOPASS. If that’s the only solution then I guess we will have to consider implementing it. I was hoping something could be done through BO Supervisor.

Thanks again for you responses. Any other ideas are welcome as well. Saraswati Naimpally
Information Technology Dept.
The Institute of Electrical and Electronics Engineers, Inc.

shaila
OM> cc:
Sent by:
Business Objects
Query Tool
<BUSOB-L@LISTSER
V.AOL.COM>

03/22/00 01:18
PM
Please respond
to Business
Objects Query
Tool

saraswati,
One more suggestion is that, this restriction could be applied at the database level and whenever user selects an object in the universe that goes against this table, he/she will see only the data that they need to see.
This will work if you are using bouser/bopass in the universe connection and the user’s id and pwd is same as his/her id and pwd at the database level.

hope this helps!

shaila

Hello!

What is the most efficient way to implement row-level security, when retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is able to retrieve based on, say, the user’s position in the Organization, in the most efficient manner.

Thanks,
Saraswati

Listserv Archives (BOB member since 2002-06-25)

6

Saraswati,

We have an ORACLE data warehouse with over 1700 users globally. The data is restricted by product grouping.

We have a security tables set up in ORACLE with the list of users (USERID) and what product group(s) they can view.

In the universes, this security table is joined to the fact tables via product group

Next, the security table has a join to itself via USERID (USERID= @Variable(‘BOUSER’)

Finally, in within the definition of each sensitive measure, using the “Table” button, we add the security table to be brought into the query whenever the measure object is selected.

This causes the following to be brought into the where clause:

WHERE fact_table.product_group = security_table.product_group And security_table.userid = @variable(‘bouser’)

So, in a data driven way, the user can only see the data for the product groups which he has access to.

Hello!

What is the most efficient way to implement row-level security, when
retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is able to
retrieve based on, say, the user’s position in the Organization, in the
most efficient manner.

If you have any questions about this, you can contact me at the number or e-mail below.

Thanks,
Bob Addington

R.L. Addington
Shell Services International
Information Management - Data Services
CHEMIST - Global Sales & Marketing Development OSP 1236 / (713) 241-4517

Listserv Archives (BOB member since 2002-06-25)

7

You can even avoid the modification of all your objects’ Table references by using a view.
The annoying problem with the “Tables” button is that every time you modify the SQL of any object, the list of tables is generated automatically and you lose your changes.
If you do not remember to change it back, you’ve lost security, at least for that object

See our technical tip on this subject at http://www.infosol-inc.com/infosol/bo_tt2.htm

Hope this helps

Jean-Francois Cayron
InfoSol Inc.
http://www.infosol-inc.com

[snip]
We have a security tables set up in ORACLE with the list of users (USERID) and what product group(s) they can view.

[snip]

Finally, in within the definition of each sensitive measure, using the “Table” button, we add the security table to be brought into the query whenever the measure object is selected.

[snip]

Listserv Archives (BOB member since 2002-06-25)

8

At 09:51 AM 3/22/2000 -0500, Saraswati wrote:

What is the most efficient way to implement row-level security, when retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is able to retrieve based on, say, the user’s position in the Organization, in the most efficient manner.

Some people use row-level restrictions in Supervisor.

We have a key value in our secured tables that identifies an “organization”, and we assign users Sybase logins to organizational groups. Some users (such as in the Controller’s group) have access to all rows, some users only have access to a subset of the data (such as their own school).

We have a set of security scripts that build a “security_org” table that identifies, for the row-restricted users, which “organizations” the userid is allowed to view. We run those scripts whenever the organizational “hierarchy” (a table) changes and/or when we add/move user logins.

So, we have one view of the fact table that includes all rows, and to which the non-row-restricted users have rights. We build the universe on that table.

We have another view of the fact table that does a join with the “security_org” table. This is the one to which the row-restricted users have rights. We include this “school” view in the universe, but do not include it in any joins, nor in any objects.

Then, in Supervisor, for each universe, we map the row-restricted view in place of the non-restricted view. Our row-restricted users are defined in a group called “Schools” at a high level of Supervisor, so that we can do the remapping easily at that high level.

So, that’s another way to consider doing it.

Regards,
Anita Craig
Stanford University

Listserv Archives (BOB member since 2002-06-25)

9

saraswati,

If u r not using BOUSER/BOPASS mechanism, u can try the following:

let’s say ur table has a column called UserID (which is BO User ID). u have to generate following sql to implement row-level security:

SELECT * FROM MY_TABLE WHERE UserID = ‘MyUserId’;

BO can generate,

SELECT * FROM MY_TABLE WHERE UserID = @Variable(‘BOUSER’) ;

where @Variable(‘BOUSER’) will be replaced by ur BO login id when query is sent to Oracle engine.

How can u generate this sql? Define row-level security in Supervisor. Goto supervisor, double-click on ur universe, select ‘Rows’ tab. Click on add button, select ‘MY_TABLE’ in tables. in where clause put UserID = @Variable(‘BOUSER’)

U can extend this logic to ur use.

hope this helps. let me know if u need any more help.

regards
Vasan
Baton Rouge International Inc
Data warehousing division

Listserv Archives (BOB member since 2002-06-25)

10

Jean-Francois writes:

You can even avoid the modification of all your objects’ Table references by
using a view.
The annoying problem with the “Tables” button is that every time you modify
the SQL of any object, the list of tables is generated automatically and you
lose your changes.
If you do not remember to change it back, you’ve lost security, at least for
that object

Yes, we quickly found the annoying problem early on. It’s now natural to make sure and hit the tables button whenever we change a measure. Again, we only do this for our measures as they are the only true sensitive objects.

Our fact tables have another similar layer of security. And, the fact tables are already complex. I was wondering if you have tried both methods (1 with view, 2 with tables reference) and found the view to have any better performance. That would be the only reason we would consider changing the way we are currently implementing our row level security.

Thanks,
Bob Addington

R.L. Addington
Shell Services International
Information Management - Data Services
CHEMIST - Global Sales & Marketing Development OSP 1236 / (713) 241-4517

Listserv Archives (BOB member since 2002-06-25)

11

saraswati,
One more suggestion is that, this restriction could be applied at the database level and whenever user selects an object in the universe that goes against this table, he/she will see only the data that they need to see.
This will work if you are using bouser/bopass in the universe connection and the user’s id and pwd is same as his/her id and pwd at the database level.

hope this helps!

shaila

Hello!

What is the most efficient way to implement row-level security, when retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is able to retrieve based on, say, the user’s position in the Organization, in the most efficient manner.

Thanks,
Saraswati

Listserv Archives (BOB member since 2002-06-25)

12

Bob,
Thanks for the detailed explanation. I guess the only way to achive row level security is through database restrictions and we have to use the BOUSER to log in.

Is there a way you ensure that the BOUSER’s password and the Oracle database password stay the same? Or do you not allow users to change their Oracle password?
Saraswati Naimpally
Information Technology Dept.
The Institute of Electrical and Electronics Engineers, Inc.

"Addington, Bob
<RA185181@MSXSSC. cc:
SHELL.COM>
Sent by: Business
Objects Query
Tool
<BUSOB-L@LISTSERV
.AOL.COM>

03/22/00 11:40 AM
Please respond to
Business Objects
Query Tool

Saraswati,

We have an ORACLE data warehouse with over 1700 users globally. The data is
restricted by product grouping.

We have a security tables set up in ORACLE with the list of users (USERID) and what product group(s) they can view.

In the universes, this security table is joined to the fact tables via product group

Next, the security table has a join to itself via USERID (USERID= @Variable(‘BOUSER’)

Finally, in within the definition of each sensitive measure, using the “Table” button, we add the security table to be brought into the query whenever the measure object is selected.

This causes the following to be brought into the where clause:

WHERE fact_table.product_group = security_table.product_group And security_table.userid = @variable(‘bouser’)

So, in a data driven way, the user can only see the data for the product groups which he has access to.

Hello!

What is the most efficient way to implement row-level security, when
retrieving data in a Business Objects/WebI report?

Meaning, how can I control the ‘records’ or ‘rows’ a user is
able to
retrieve based on, say, the user’s position in the Organization, in the
most efficient manner.

If you have any questions about this, you can contact me at the number or e-mail below.

Thanks,
Bob Addington

R.L. Addington
Shell Services International
Information Management - Data Services
CHEMIST - Global Sales & Marketing Development OSP 1236 / (713) 241-4517

Listserv Archives (BOB member since 2002-06-25)