vicker313 tech blog

April 8, 2011

Setup Customized User Access Trace Log in Oracle Database

Filed under: Oracle — Tags: , , — vicker313 @ 2:26 pm

Here is a simple way to trace user access (log in and log out) in Oracle, by using trigger and table.

  1. Create a trace log table. For example:
    CREATE TABLE TRACELOG
    (
       SESSION_ID        NUMBER(20),
       SESSION_USER      VARCHAR2(30 BYTE),
       OS_USER           VARCHAR2(30 BYTE),
       HOST              VARCHAR2(30 BYTE),
       IP_ADDRESS        VARCHAR2(30 BYTE),
       TERMINAL          VARCHAR2(30 BYTE),
       MODULE            VARCHAR2(30 BYTE),
       LAST_ACTION       VARCHAR2(32 BYTE),
       LAST_ACTION_DATE  DATE,
       LOGON_DATE        DATE,
       LOGOFF_DATE       DATE
    )
  2. Grand privilege to public to update the TRACELOG table
    GRANT INSERT, UPDATE, SELECT ON TEMP_TRACE TO PUBLIC;
  3. Create Log On Trigger. For example:
    CREATE OR REPLACE TRIGGER on_logon_trigger
    after logon on database
    begin
       insert into tracelog(session_id, session_user, os_user, host, ip_address, terminal, module, logon_date)
       SELECT sys_context('USERENV', 'sessionid'),
       user,
       sys_context('USERENV', 'os_user'),
       sys_context('USERENV', 'host'),
       sys_context('USERENV', 'ip_address'),
       sys_context('USERENV', 'terminal'),
       sys_context('USERENV', 'module'),
       sysdate
       FROM dual;
       commit;
    end;
  4. Create Log Off Trigger. For example:
    CREATE OR REPLACE TRIGGER on_logoff_trigger
    before logoff on database
    begin
       update tracelog
       set logoff_date = sysdate
       where session_id = sys_context('USERENV', 'sessionid')
       and logoff_date is null;
       commit;
    end;

Now you can trace user access by querying the TRACELOG table.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: