9 Replies Latest reply: Feb 8, 2013 8:08 AM by user11972466 RSS

    OS User

      How do we capture the OS user for a discoverer report? For example if a windows user has requested a report we want to capture the OS user who is executing or requesting a report.?

        • 1. Re: OS User
          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...

          • 2. Re: OS User
            Michael Armstrong-Smith
            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
            Best wishes
            Michael Armstrong-Smith

            URL: http://learndiscoverer.com
            Blog: http://learndiscoverer.blogspot.com
            • 3. Re: OS User
              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.

              • 4. Re: OS User
                Michael Armstrong-Smith
                Hi Avinash
                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?
                • 5. Re: OS User
                  The users come through the Application server with a public connection, so how can the OS users be captured?

                  Also does the Discoverer server / Oracle application server capture the OS user somewhere?

                  The EUL5_QPP_STATS doesnt capture the OS user.

                  • 6. Re: OS User
                    I think I can use this:

                    Applies to:
                    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
                    ACC_DATE DATE,
                    sso_name varchar2(100)
                    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.

                    RETURN NUMBER AS


                    insert into EUL10.TRIG_TAB(acc_date) values(sysdate);

                    RETURN 0;

                    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.

                    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

                    RETURN number AS

                    ssouser varchar2(100);


                    ssouser := SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
                    insert into EUL10.TRIG_TAB values(sysdate, ssouser);

                    RETURN 0;

                    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.
                    • 7. Re: OS User
                      Michael Armstrong-Smith
                      Hi Avinash
                      Yes, that sounds feasible. I was just about to log on and suggest a trigger but you beat me to it. :-)

                      If you decide to try it out I would be interested in hearing how you get on.

                      Best wishes
                      • 8. Re: OS User
                        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
                        ) o268207;

                        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?
                        • 9. Re: OS User
                          Could you please help me in implementing VDP with discoverer report/ protal