This content has been marked as final. Show 9 replies
Hi. It's possible to retrieve os user with a simple sql query:
SQL> SELECT sys_context('USERENV', 'OS_USER') FROM dual;I believe you'll need a disco login trigger or something similar. I don't know, though, how you are going to return this value in a discoverer workbook...
Try to see if this information is not present in the discoverer EUL tables...
The SYS_CONTEXT function is embedded within the STANDARD package that is owned by SYS. If your Discoverer Administrator imports this function into Discoverer you will be able to make use of it in your reports. After import it will turn up in the database folder of when you click or select functions.
I hope this helps
Got it. A follow up question:
How do you keep track of the reports run by different users (OS users). We can keep track of reports being run by Oracle user by using Discoverer EUL audit features, but for public connections, the DB user is always the same, so we want to capture which OS user runs or ran which reports how many times.
I'm glad you were able to get this going. The only statistics that Discoverer keeps are to be found in the EUL5_QPP_STATS table that is owned by the EUL owner. If this does not have what you want it sounds like you will have to create some sort of trigger that whenever a create or update is done to EUL5_QPP_STATS you write the OS user to a file along with the QS_ID from the statistics table.
Does this help?
I think I can use this:
Oracle Discoverer - Version: 4.1 to 10.2
Information in this document applies to any platform.
To provide assistance in getting Discoverer EUL_TRIGGER$POST_LOGIN trigger to work.
The document will use the following settings that you must replace with your own environment settings:
The End User Layer(EUL) Owner Schema = EUL10
A Oracle Database Test User = DISCOTEST
Use the following steps to get the Trigger Working:
1. Login as EUL owner [EUL10]
Create a test table
CREATE TABLE eul10.TRIG_TAB
2. Create a function under the eul schema owner: eul_trigger$post_login
Do not pass any argument to the function.
Return a number from function.
CREATE OR REPLACE FUNCTION EUL10.EUL_TRIGGER$POST_LOGIN
RETURN NUMBER AS
insert into EUL10.TRIG_TAB(acc_date) values(sysdate);
3. Login with Discoverer Administrator to EUL=EUL10.
4. Check the Menu > Tools > Default EUL.
Make sure this is set to EUL10
If not then change the EUL10 and restart Discoverer Administrator.
5. Now Register the trigger in Discoverer Administrator
Menu >Tools > Register PL/SQL Function ...
6. Click import
7. Select the trigger EUL10.eul_trigger$post_login
|8. Make sure the display name is eul_trigger$post_login (MUST BE lowercase or it will not work)
Function name must be in uppercase: EUL_TRIGGER$POST_LOGIN
9. Make sure the the return type is number and that no arguments get passed.
10. Validate the Function by clicking on the Validate button.
11. Click ok
12. Connect with Discoverer desktop as EUL owner.
13. We see entries in the table trig_tab with the current date.
14. Now create a test user DISCOTEST.
Grant the user the normal database 'create session', etc. privileges
15. In discoverer Administrator Grant the user privileges to access Desktop and Plus
Menu > Privileges
16. Now grant the new user access to some business areas.
In discoverer administrator select Menu > Security
Assign some business areas to the test user and click ok.
16. The test user need some additional privileges and objects to work:
As the EUL owner EUL10 perform the following grants:
grant select on EUL10.EUL5_FUNCTIONS to DISCOTEST;
grant select on EUL10.EUL5_FUN_ARGUMENTS to DISCOTEST;
grant execute on EUL10.EUL_TRIGGER$POST_LOGIN to DISCOTEST;
17. Create the following views for the DISCOTEST user.
CREATE VIEW DISCOTEST.EUL5_FUNCTIONS AS SELECT * FROM EUL10.EUL5_FUNCTIONS;
CREATE VIEW DISCOTEST.EUL5_FUN_ARGUMENTS AS SELECT * FROM EUL10.EUL5_FUN_ARGUMENTS;
18. Now connect with Discover Desktop to EUL10 End User Layer and using the test user
Note that test table (trig_tab) gets updated
NOTE: Make sure the default EUL for the user is EUL10 and not some other EUL
otherwise the function will not be triggered.
19. Now change the trigger function to
CREATE OR REPLACE FUNCTION EUL10.EUL_TRIGGER$POST_LOGIN
RETURN number AS
ssouser := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
insert into EUL10.TRIG_TAB values(sysdate, ssouser);
20. Connect to discoverer viewer http://machine:port/discoverer/viewer
21. Authenticate with the SSO user. In my case I used the 'orcladmin; sso user and database user DISCOTEST.
22. Define a connection for the test user on the initial Discoverer Plus or Viewer page.
23. Use connection type "OracleBI Discoverer"
24. If you have multiple EULS then you will be prompted for the EUL you want to use.
Make sure to select the EUL where you created the EUL_TRIGGER$POST_LOGIN trigger.
In my case this is EUL10.
25. Now select from the table trig_tab.
You will get the date and the SSO username in the table.
So I see the current date and sso username orcladmin or your SSO username you used.
26. Now change the function to have it call your VPD procedure to setup the
user context that you have setup on the source table.
i am trying the different way: if DB knows your OS_USER, the only problem here is how to hand this to discoverer. created and registered a function that return sys_context('userenv','os_user').
i can't get it why discoverer is not returning value of custom folder with validated sql:
select av_os_user() from dual;
discoverer plus shows following sql for the report:
SELECT o268207."AV_OS_USER()" as E268211
FROM ( select av_os_user() from dual
code runs if copied and run directly in db, but it gets error ORA-00904: : invalid identifier when refreshing the discoverer report
where's the clue?