Restricting Data

building semantic layer
1

I have a requirement to build in restrictions to several universes to ensure Company A sees only the data belonging to Company A and Company B sees only the data belonging to Company B, etc… The organisation code held on the tables will determine to which company the data belongs.
I realise this must be quite a common requirement and I am aware of the solution offered by Business Objects, i.e. place a restriction on every ‘sensitive’ table in the universe using the Supervisor to code the appropriate condition into the ‘where’ clause. My concern is that I have overlooked a simpler or more efficient solution. The condition in the ‘where’ clause has to look at the app_users table which is a reference table holding the user name and organisation code for each user. The Business Objects user name will be the same as the user name in the app_users table.
Does anyone have any experience of imposing this kind of restriction on universes or can anyone offer any advice on the best approach? The universes will be pointing to the application database so performance is an issue of particular concern.
Any ideas/help anyone can offer would be greatly appreciated. Many Thanks,
Ailie Forgie.

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

2

Forgie, Ailie J wrote:

I have a requirement to build in restrictions to several universes to ensure Company A sees only the data belonging to Company A and Company B sees only the data belonging to Company B, etc… The organisation code held on the tables will determine to which company the data belongs.

The condition in the ‘where’ clause has to look at the app_users table which is a reference table holding the user name and > organisation code for each user. The Business Objects user name will be the same as the user name in the app_users table.

I assume that you are using Oracle 7.3 as the RDBMS. In that case we can create joins between your “sensitive” tables and the “app_users” table. This will ensure that the users will always see correct data and in case some users need to see data from two companies, all you have to do is to add one more row for the particular user in the app_users table. This is how it works :

Assume you have the following data in the app_users table.

User_Name Company_Code Company_Name
USER1 A COMPANY-A
USER2 B COMPANY-B
USER3 C COMPANY-C
USER1 B COMPANY-B

I assume that your sensitive table Table_1 has data like this :

Company_code Column_1 Column_2
A 10 20
B 40 50
C 30 80

Now create the following join in BO :
Table_1.Company_code = app_users.Company_code AND app_users.User_Name = @variable(BOUSER)

Here BOUSER is a variable provided by BO which gives the current useranme.

In this way the user USER1 will see the data of Company_A and Company_B whereas USER2 will see Company_B only and the USER3 will see only the data of Company_C.

Similarly you can create joins between your other “sensitive” tables and app_users table.

Now if you want to give access to the USER3 the data of Company_A, insert the following row into the app_users table :

User_Name Company_Code Company_Name
USER3 A COMPANY-A

The universes will be pointing to the application database so performance is an issue of particular concern.

If you create index on the Table_1.Company_code column, the performance will be good.

Sorry for the lengthy answer. I just wanted to make my point very clear.

I hope this will help you.

Chandrashekar.S

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

3

Ailie J" at Jun 10, 98 01:24:24 pm

Ailie Forgie wrote:

I have a requirement to build in restrictions to several universes to ensure Company A sees only the data belonging to Company A and Company B sees only the data belonging to Company B, etc… The organisation code held on the tables will determine to which company the data belongs. [stuff deleted]

What database are you using? We use Oracle, and grant roles to users based on their organization codes. All the security is handled in Oracle rather than in Business Objects. We also use the Business Objects setup $USER$ & $PASS$ and have users sign on to Business Objects using their Oracle id and Oracle password.

I’m not a Designer user, so I don’t know much more than this, but I hope it gives you a useful lead.

–Lori

============================================================================ Lorraine A. Ratajczak Internet: ratajczak@isc.upenn.edu
Data Administration voice: (215) 898-5029
University of Pennsylvania fax: (215) 898-0386
3401 Walnut Street, Suite 265C
Philadelphia, PA 19104-6228

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

4

Lorraine,

If I understand the following correctly, do you have Business Objects working so that users can log into Oracle and the userid and password are passed through bus. obj. so that they don’t have to separately log in?

We’re looking into building a menuing system for reporting so that a user logs in once and depending on which report they choose the id and password are sent to that tool (Business Objects, Oracle Reports, FSG, etc.). Can you tell me how you did this exactly?

Thanks,

Julie

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

5

10, 98 02:06:00 pm

Julie wrote:

[stuff deleted]
We’re looking into building a menuing system for reporting so that a user logs in once and depending on which report they choose the id and password are sent to that tool (Business Objects, Oracle Reports, FSG, etc.). Can you tell me how you did this exactly? [stuff deleted]
in reply to my note:
[stuff deleted] We use Oracle, and grant roles to users based on their organization codes. All the security is handled in Oracle rather than in Business Objects. We also use the Business Objects setup $USER$ & $PASS$ and have users sign on to Business Objects using their Oracle id and Oracle password. [stuff deleted]

Julie, what we’re doing is not the single sign on system it sounds like you want. What we’re doing is bypassing BusObj security and passing the security work onto Oracle. Not all Data Warehouse users use BusObj, so having Oracle do the security keeps the data secure regardless of the query tool used.

Sorry for the confusion.

–Lori

============================================================================ Lorraine A. Ratajczak Internet: ratajczak@isc.upenn.edu
Data Administration voice: (215) 898-5029
University of Pennsylvania fax: (215) 898-0386
3401 Walnut Street, Suite 265C
Philadelphia, PA 19104-6228

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

6

Sorry for the late reply, I have been busy un-trashing our repository.

Ailie Forgie wrote,

I have a requirement to build in restrictions to several universes to ensure Company A sees only the data belonging to Company A and > Company
B sees only the data belonging to Company B, etc… The organisation code held on the tables will determine to which company the data belongs.

We have a similar situation with our HR system, and a solution which will probably give better performance.

In supervisor, we have created a separate group for each company, and added restrictions that apply to all members of that group. This only results in a where clause being added to your SQL.

We have one table that identifies where each employee works, and use this to control access. The main thing that you have to ensure is that the tables that have the restriction must be part of the query, else the security is bypassed.

This facility is hidden away on the ROWS tab of the PROPERTIES of the universe, within supervisor. You just simply add in the SQL condition that you wish to apply.

With this solution you do not need to maintain a separate table of users, and add new users to it, you just simply add them to the appropriate group in supervisor.

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