Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: Capture Login Name for Audit Log

    Hi,

    I need to work on a audit log procedure but I do not know how to capture the login name of the users when they try to insert,update or delete a table.

    My scenario is like this : I have a set of users each have a database login ID that can access to Table A where they can insert,update and delete the table records. I have create a trigger that will log all changes in Table A into a audit table as tracking, however I do not know how to capture the login id that execute the changes which is the most important info in this audit log procedure.

    Have been searching the web but can get the solution therefore decided to post it in forum, hope I can get the solution soon from the experts out there.

    THANKS A Billion in advance.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    SELECT S.Username, S.Machine, S.PROGRAM
    INTO v_username, v_machine, V_PROGRAM
    from V$PROCESS P, V$SESSION S
    where P.Addr = S.Paddr
    and S.Audsid = USERENV('SESSIONID');

    HTH
    Gregg

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    How about just using 'USER'?

    Kinda like:

    INSERT INTO AUDIT_TAB (USER, SYSDATE, 'The Message');

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jan 2003
    Location
    China
    Posts
    38
    I agree with LKBrwn DBA.What I want to say is you can realize this by using trigger.
    Fan Yi

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree, there are many ways to accomplish this type of auditing ... but
    all should be done inside a trigger ... you can use user_user table, v$session, v$process or userenv function, depending on what you want to trap ...

    Gregg

  6. #6
    Join Date
    Jun 2003
    Posts
    81

    Difference between OS-user and DB-user

    Hi!
    You can add S.osuser to the script, if there is a diffence on the database-user and the operating system user. Sometimes a common logon in the database is used and then the OS-user is better to use.

    Best regards
    Kaare Friis-Christensen

    Quote Originally Posted by gbrabham
    SELECT S.Username, S.Machine, S.PROGRAM
    INTO v_username, v_machine, V_PROGRAM
    from V$PROCESS P, V$SESSION S
    where P.Addr = S.Paddr
    and S.Audsid = USERENV('SESSIONID');

    HTH
    Gregg

Posting Permissions

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