Results 1 to 10 of 10

Thread: Audit/Trace

  1. #1
    Join Date
    Oct 2005
    Posts
    61

    Unanswered: Audit/Trace

    Hi All,

    I need to audit the user connections to the Database(Oracle). I am trying to find a way by which I can get the i.p addrees or the hostname or the o.s username of the user connecting to the database. The problem here is that my application uses a single sign-on process i.e all the application users (users created in the application) connect to the database via a single account created in the database.

    I have tried using sys_context in a trigger but that does not give me the required details, I get the i.p address as blank. I have also tried setting the session_identifier variable but could not get what I wanted. Anyone have idea about how to get this done?

    Thanks,

    Srinivas

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Don't solve it at the database level, solve it at the application level.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2005
    Posts
    61
    Yeah...I know we can set the application context and could be done in the application. Now in my case since it's not packaged in the application just was curious to know if there is any workaround for this at the database level.

    Right now am not concerned about any performance issue or concurrency issue if I write a database level trigger to fire for each log on.

    Just want to know if there's any workaround for this at the database level.

    Thanks for the response!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DECLARE
    v_user_identifier varchar2(64);
    BEGIN
    SELECT SYS_CONTEXT('USERENV', 'OS_USER')
    ||':'||
    SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    INTO v_user_identifier
    FROM dual;
    DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
    END;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2005
    Posts
    61
    Please check in my first post, I have already tried this. The output of such a trigger is -

    OSUSER - The database user's (the single sign-on user's) osname is displayed.

    IP address - is blank.

    Thanks for the response!

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    For Oracle Forms, we call a database package first thing which sets a package spec variable = the application user name, then updates a table with the SID, logon time, and application user name of the individual. Then we have a VIEW which cross-references that table with V$SESSION so that we can map the application user to the database session.

    -Chuck

  7. #7
    Join Date
    Oct 2005
    Posts
    61
    Thanks for the response!

    We aren't using forms here. I was thinking of something like that but am not getting any clue about doing that without bundling anything within the application. I would prefer to add code in the database...without touching the application.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Not sure if it will work but in your logon trigger see if this sql returns the ip address.

    select sysdate, ora_login_user, ora_client_ip_address, userenv('TERMINAL'), SID, SERIAL#, MACHINE, PROGRAM
    FROM V$SESSION
    WHERE AUDSID = USERENV('SESSIONID');

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    What's wrong with building a procedure call into your application?
    -cf

  10. #10
    Join Date
    Oct 2005
    Posts
    61
    Alanp-
    Thanks for the response! But that doesn't work.

    Chuck forbes -
    We are not supposed to add/change anything in the application. Just tried to do something like that in the patch encvironment...but there are some in built synchronization process in the application...which rolls back any newly added code.

    Just was wondering if there was any other way out. Looks like it cannot be implemented in any other way.

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •