Hi,
This is regarding row level security again.I know this is often discussed in this forum but I’m stuck in the middle of the following way of implementing data restriction.
My universe connection uses a generic user name to access oracle db. Instead of the real table my universe is built on their corresponding views. The views are built on joining the security table and the real table. The security table has entries of user id and their restriction attribute.
As the universe connection uses a generic id and restriction is based on user id,I’m looking for some solution by which I can pass the BO user_id to oracle so that the view will take the user_id into account to filter data and my Universe can use that view to build query.
– you can pass the user id using @Variable(‘BOUSER’) in the universe connection name itself. (for both XI and earlier versions). There is also a Business Objects White Paper on this topic.
– you can also set the DBUSER and DBPASS in user data in CMC. (XI only)
i’m usine XIR2 and don’t want to create oracle user ids for all BO users. Just want the universe connect to db using a generic id but some how individual bo user id passed to oracle. could you please share the whitepaper.
If the security table contains the user access to his/her office/dept, then it can be joined with the fact table(s) along with the @variable(‘BOUSER’) function as
SECURITY_TBL.DEPTNO = FACT_TBL.DEPTNO AND SECURITY_TBL.USERID=@VARIABLE('BOUSER')
where userid in your security table is same as BOBJ user account
Force the join between security table and fact table(s) using tables button on each dimension objects of fact table.
If this is not the setup you have then give details
Which means that the value stored in @bouser should be passed to the view having the join at DB level?
So what columns are exposed in the view and the script used
Hi Haider …here with more details
suppose my main table has country_cd , sales_rev and security table has bo_user and country_cd …the view will be on the main table and security table for matching country_cd(main table.country_cd=security table.country_cd and bo_user = <here i want the @BOUSER value>).
Now the view is added to the universe and report is built on the view. on runtime i want @BOUSER passed to the view script in oracle to bring only the required country_cd data hence to the report.
The report query will be a simple select statement
select country_cd , sales_rev
from view.
I am looking for a way to pass the @BOUSER value to back end oracle view scrpt.
I hope your view has the following columns…
– country_cd
– bo_user
If it has bo_user column in view just create a condition in the universe as below and add it to your query.
VIEW.BO_USER = @VARIABLE('BOUSER')
So what is happening here is:
– you may be connecting to your source using a different user id/passwd in universe connection. (which may be a generic user id)
– And the other thing is you are controlling the report data display using VIEW and @BOUSER condition.
Thanks Bo_chief…
Actually I don’t want my view have the BO_USER column reason being i dont want that condition VIEW.BO_USER = @VARIABLE(‘BOUSER’) appear in my query as the users may edit the sql and remove the condition and run the report.hence i pushed the condition to the database side. Instead of creating a view i can put the security table in the universe itself. but i dont want taht also …by this user get a chance to remove the condition from sql… I want they are restricted to data as well as enjoy their edit sql previelege.
BO Chief - can you please post a link to the white paper of the BOBJ Universe security please? I am brand-new to the forum and the technology. BOBJ XI3.1 - Teradata 12 sp3.
If you are averse to creating views in the database for each user, then you can use the security table in the universe(as already said earlier) and give the user only view SQL privilege without editing it
And that will be the only way to go with your present requirement
(BOB member since 2006-06-26)
(BOB member since 2004-06-06)
(BOB member since 2005-07-18)