Home » Developer & Programmer » Reports & Discoverer » Discoverer EUL Security
Discoverer EUL Security [message #300154] Thu, 14 February 2008 07:42 Go to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
Hi all,

We are just going live with an extra set of books, operating unit, organization etc etc etc.. So its turning into a Multi-Org Environment..

We are using Discoverer v 4.1.43.

There was one EUL before the multi-org structure. Now there is two.

The original EUL was built back in about 01 and has been modified and extended ever since. Problem most of the EUL is built off Standard Table folders or Custom Folders off Standard Tables. Therefore no security is applied and both Org's Data is returned. Shocked Sad

The other EUL is 'brand new' and is built off of the secure views which apply org security against reports etc.

We need to secure the first EUL and time is running out! Re-writing the EUL isnt an option, and upgrading discoverer etc isnt.

Has anyone got any clever suggestions? After some research i found out that the org_id is normally stored in USERENV('CLIENT_INFO') with DBMS_APPLICATION_INFO.

Any thoughts?

Gary
Re: Discoverer EUL Security [message #300159 is a reply to message #300154] Thu, 14 February 2008 08:05 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Once you find the time, please please upgrade Discoverer (you're on a non-supported version now). Having said that...

Add the following where clause to each table that is named like %_ALL (so, ends with all). This is the standard multi org method in 11i, please be aware that this way of "multi org-ing" your EUL will not survive an upgrade to EBS 12, so investigate multi-org again by the time your company considers EBS 12.

Adding the clause to all %ALL tables can be done automatically in the EUL if you have someone on-site who is very, very experienced with Discoverer (please note, hacking in the EUL is not supported).


WHERE nvl(org_id
,nvl(to_number(decode(substr(userenv('CLIENT_INFO')
,1
,1)
,' '
,NULL
,substr(userenv('CLIENT_INFO')
,1
,10)))
,-99)) = nvl(to_number(decode(substr(userenv('CLIENT_INFO')
,1
,1)
,' '
,NULL
,substr(userenv('CLIENT_INFO')
,1
,10)))
,-99)
Re: Discoverer EUL Security [message #300165 is a reply to message #300159] Thu, 14 February 2008 08:25 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
skooman, thank you so much for a response..

skooman wrote on Thu, 14 February 2008 14:05
Once you find the time, please please upgrade Discoverer (you're on a non-supported version now). Having said that...

Add the following where clause to each table that is named like %_ALL (so, ends with all). This is the standard multi org method in 11i, please be aware that this way of "multi org-ing" your EUL will not survive an upgrade to EBS 12, so investigate multi-org again by the time your company considers EBS 12.


We are moving to OBIEE+ well before R12.. Just gone to 11.5.10 last October!

skooman wrote on Thu, 14 February 2008 14:05
Adding the clause to all %ALL tables can be done automatically in the EUL if you have someone on-site who is very, very experienced with Discoverer (please note, hacking in the EUL is not supported).


When you say 'in the EUL' do you mean in Disco Admin, or in Toad or something?

Im fairly competant with writing SQL and using Toad.


Quote:

WHERE nvl(org_id
,nvl(to_number(decode(substr(userenv('CLIENT_INFO')
,1
,1)
,' '
,NULL
,substr(userenv('CLIENT_INFO')
,1
,10)))
,-99)) = nvl(to_number(decode(substr(userenv('CLIENT_INFO')
,1
,1)
,' '
,NULL
,substr(userenv('CLIENT_INFO')
,1
,10)))
,-99)



So this is setting the 'org_id' to what is stored in the client info which is populated off the responsibility profile options? Is that correct? This means the table scripts will need updating with the above in the where clause?

Again thanks for your reply and look forward to another response! Razz

Gary

[Updated on: Thu, 14 February 2008 08:26]

Report message to a moderator

Re: Discoverer EUL Security [message #300167 is a reply to message #300165] Thu, 14 February 2008 08:36 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Okay, first step (I forgot, sorry) is to set your EUL to be used in Apps mode. Start Discoverer Administrator, log in, go to options and search there (please check the reference, I don't know the tab and option by heart).

Then about adding the where clause:
the basic idea it just to start Administrator, log in, open all Business Area, find the first table which name end with ALL, and add a condition on that table (with the content I posted).

If that action has to be done like thousands of times, you could consider doing it all at once by altering the contents of the EUL tables (using SQL*Plus or any tool you like). But like I said, this is very tricky and trust me, don't go there.

Instead, find some juniors, lock them in a room clicking for a couple of days and you're fine Wink.
It might even be possible to copy/paste the condition using ctrl-C, ctrl-V, I'm pretty sure that works but you have to test it.
Re: Discoverer EUL Security [message #300186 is a reply to message #300167] Thu, 14 February 2008 09:03 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
skooman wrote on Thu, 14 February 2008 14:36
Okay, first step (I forgot, sorry) is to set your EUL to be used in Apps mode. Start Discoverer Administrator, log in, go to options and search there (please check the reference, I don't know the tab and option by heart).

Then about adding the where clause:
the basic idea it just to start Administrator, log in, open all Business Area, find the first table which name end with ALL, and add a condition on that table (with the content I posted).

If that action has to be done like thousands of times, you could consider doing it all at once by altering the contents of the EUL tables (using SQL*Plus or any tool you like). But like I said, this is very tricky and trust me, don't go there.

Instead, find some juniors, lock them in a room clicking for a couple of days and you're fine Wink.
It might even be possible to copy/paste the condition using ctrl-C, ctrl-V, I'm pretty sure that works but you have to test it.


Hehe! Laughing I wish we had juniors, im the lead on making this 'work' ! Eek!

I had an idea of creating a database trigger in which when it detected a discoverer logon (in the user tables i imagine), to go and get the Org from the responsibility profile options, and then load that into the USERENV('CLIENT_INFO') with DBMS_APPLICATION_INFO.

Does my idea sound rubbish? Embarassed

Is it genuinely not worth thinking about doing a bulk update in the EUL tables?

Gary
Re: Discoverer EUL Security [message #300194 is a reply to message #300186] Thu, 14 February 2008 09:55 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Quote:
I had an idea of creating a database trigger in which when it detected a discoverer logon (in the user tables i imagine), to go and get the Org from the responsibility profile options, and then load that into the USERENV('CLIENT_INFO') with DBMS_APPLICATION_INFO.

You don't need to, apps takes care of that for you. So, the issue is to catch that correctly in Discoverer (and that's what that apps mode of the EUL does).

Details on all this are described on metalink (search on discoverer ebs or something).

Quote:
Is it genuinely not worth thinking about doing a bulk update in the EUL tables?

Only if you have done some major bulk updates of the EUL before, because this is really tricky to do in the EUL. Or hire someone to do it for you (sorry, I don't have time Wink

[Updated on: Thu, 14 February 2008 09:58]

Report message to a moderator

Re: Discoverer EUL Security [message #300369 is a reply to message #300194] Fri, 15 February 2008 04:05 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
skooman wrote on Thu, 14 February 2008 15:55
Quote:
I had an idea of creating a database trigger in which when it detected a discoverer logon (in the user tables i imagine), to go and get the Org from the responsibility profile options, and then load that into the USERENV('CLIENT_INFO') with DBMS_APPLICATION_INFO.

You don't need to, apps takes care of that for you. So, the issue is to catch that correctly in Discoverer (and that's what that apps mode of the EUL does).


So your saying it does collect the org_id from the resp? If apps mode collects the org_id from the responsibility, why doesnt it use it in discoverer?
Re: Discoverer EUL Security [message #300411 is a reply to message #300369] Fri, 15 February 2008 05:56 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Quote:
So your saying it does collect the org_id from the resp? If apps mode collects the org_id from the responsibility, why doesnt it use it in discoverer?


Actually, the org_id can be set on a number of levels (including user and responsibility, but also for example on application level, so no matter who I am and what responsibility I choose, if I use AP for example, my org_id is set to 101).

This org_id is then temporarily stored (? technically speaking not correct I assume, but for argument's sake...) in the standard database setting userenv('CLIENT_INFO'). As long as your select statement doesn't refer to that userenv, nothing chances. However, if you use it in the select statement, you can manipulate the result set based on org_id.

Perhaps it helps to understand it if you compare it to userenv('LANG'), the language in the session. It can be set from a number of places (ie tool, database, session, etc.), once it is set it doesn't do anything untill you for example use in a select statement:
where your_table.language = userenv('LANG')

then only the rows in the currently "required" language are shown.
(needless to say that this only works if there is a column like language in your_table.

Same thing for org_id: all rows are shown, unless you put in your select statement something like
where your_table.org_id = userenv('CLIENT_INFO')


(it's more complicated then that, see complete where clause is previous post, but just to explain the concept). All tables ending at %_ALL contain an org_id column.

If you want to know the exact details, I suggest you search for multi_org in eTRM, the explanation there is very clear.
Previous Topic: Runtime parameter
Next Topic: REP-0177 error while running in remote server
Goto Forum:
  


Current Time: Wed Dec 07 14:48:19 CST 2016

Total time taken to generate the page: 0.14919 seconds