Home » Developer & Programmer » Reports & Discoverer » Discoverer EUL Security
|
|
| Re: Discoverer EUL Security [message #300159 is a reply to message #300154] |
Thu, 14 February 2008 08:05   |
skooman
Messages: 913
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 |
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! 
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 |
skooman
Messages: 913
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  .
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 |
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 .
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!  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? 
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 |
skooman
Messages: 913
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
[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 |
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 |
skooman
Messages: 913
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.
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 04:44:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
