Auditing User Login-Logoff activities using System Level Trigger


Oracle special system-level triggers, which can be created on system events that are supported on DATABASE and SCHEMA. These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers. The user logon/logoff triggers will accurately tell you the time of the user logon and logoff.

Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1007895

Here we are trying to design a single table for auditing user logon and logoff activities.

create table myuser_audit
(
sessionid     number,
username     varchar2(30),
osuser     varchar2(30),
process varchar2(24),
machine varchar2(64),
port    number,
terminal     varchar2(16),
module      varchar2(64),
action      varchar2(64),
client_info varchar2(64),
program varchar2(64),
host     varchar2(30),
ip_address     varchar2(64),
logon_date     date,
logoff_date     date
);

Following is the login trigger, which will create a row in myuser_audit table when a user gets login on database, it will record sid, username, host, ip_address and logon_date

create trigger user_logon_audit_trg
after logon on database
begin
insert into myuser_audit
(
sessionid,
username,
host,
osuser,
ip_address,
logon_date
)
values
(
sys_context('userenv','sessionid'),
sys_context('userenv', 'session_user'),
sys_context('userenv', 'host'),
sys_context('userenv', 'os_user'),
sys_context('userenv', 'ip_address'),
sysdate
);
end;
/

After a login trigger, we need a trigger on logoff event, which will simply update the information from v$session to our auditing table by using sessionid when user is getting logged off.

create trigger logoff_audit_trigger
before logoff on database
begin
update
myuser_audit
set
(
action,
process,
machine,
port,
terminal,
module,
client_info,
program,
logoff_date
)
= 
(
select 
action,
process,
machine,
port,
terminal,
module,
client_info,
program,
sysdate    
from 
v$session 
where     
audsid = sys_context('userenv','sessionid')
)
where
sessionid = sys_context('userenv','sessionid')
and logoff_date is null;
end;
/

Now a various kind of reports/informaction about user login and logoff activities can be retrived from myuser_audit table.



Related Post:
ORA-00604: error occurred at recursive SQL level string
http://nimishgarg.blogspot.in/2013/02/ora-00604-error-occurred-at-recursive.html

15 comments:

  1. Hey guys.....
    Each time i am executing any of the trigger in my database it is giving me the same error either i am executing the DDL TRIGGER or LOGON TRIGGER....


    The code is written bellow..
    -------------------------------
    SQL> create table DDL_LOG
    2 (username varchar2(20),
    3 change_date date,
    4 object_type VARCHAR2(20),
    5 object_owner VARCHAR2(30),
    6 database varchar2(20));

    Table created.

    SQL> CREATE OR REPLACE TRIGGER
    2 DDL_LOG_TRIG
    3 AFTER DDL ON DATABASE
    4 BEGIN
    5 INSERT INTO DDL_LOG
    6 (username ),
    7 change_date,
    8 object_type,
    9 object_owner,
    10 database
    11 )
    12 VALUES
    13 (ora_login_user,
    14 sysdate,
    15 ora-dict_obj_type,
    16 ora_dict_obj_owner,
    17 ora_daabase_name)
    18 END;
    19 /

    Warning: Trigger created with compilation errors.

    SQL> conn arvind/arvind
    Connected.
    SQL> create table xyz
    2 (name varchar2(10));
    create table xyz
    *
    ERROR at line 1:
    ORA-04098: trigger 'SYS.DDL_LOG_TRIG' is invalid and failed re-validation

    SQL> conn / as sysdba
    Connected.
    SQL> ALTER TRIGGER DDL_LOG_TRIG compile;

    Warning: Trigger altered with compilation errors.

    SQL> show errors trigger DDL_LOG-TRIG;
    No errors.
    SQL>
    --------------------------------------------
    Please help...

    ReplyDelete
    Replies
    1. Your trigger has compilation errors.
      use following

      CREATE OR REPLACE TRIGGER DDL_LOG_TRIG AFTER DDL ON DATABASE
      BEGIN
      INSERT INTO DDL_LOG (username ,change_date,object_type,object_owner,database)
      VALUES
      (ora_login_user, sysdate, ora_dict_obj_type, ora_dict_obj_owner, ora_database_name);
      END;
      /

      Delete
    2. Hey....
      Thanks a lot Nimish for correcting my silly mistakes...

      Delete
  2. Hey guys Can anyone help me in fine grained auditing...

    I am trying to create this policy but getting error.
    -----------------------------------------------------
    SQL> BEGIN
    2 DBMS_FGA.ADD_POLICY (
    3 object_schema => 'SCOTT',
    4
    5 object_name => 'FGA_TEST',
    6
    7 policy_name => 'FGA_TEST_POLICY',
    8
    9 audit_condition => NULL,
    10
    11 audit_column => 'ENAME,SAL',
    12
    13 handler_schema => 'FGA_HANDLER',
    14
    15 handler_module => 'sp_audit',
    16
    17 enable => true,
    18
    19 statement_types => `SELECT,INSERT,UPDATE,DELETE' );
    20
    21 end;
    22 /
    ERROR:
    ORA-01756: quoted string not properly terminated

    ReplyDelete
    Replies
    1. check your statement 19
      - statement_types => `SELECT,INSERT,UPDATE,DELETE'

      starting quote is not proper, convert it to following
      - statement_types => 'SELECT,INSERT,UPDATE,DELETE'

      also, plz post the problem related to the post plz. this is not a forum its a blog.

      Delete
  3. Nimish Garg: "trigger" keyword missing in create trigger (for logoff trigger)

    ReplyDelete
  4. Error(5,1): PL/SQL: SQL Statement ignored
    Error(31,7): PL/SQL: ORA-00942: table or view does not exist

    ReplyDelete
  5. Row# NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER

    1 LOGOFF_AUDIT_TRIGGER TRIGGER 1 28 7 PL/SQL: ORA-00942: table or view does not exist ERROR 0
    2 LOGOFF_AUDIT_TRIGGER TRIGGER 2 2 1 PL/SQL: SQL Statement ignored ERROR 0

    ReplyDelete
  6. Very shortly this site will be famous amid all blogging
    and site-building people, due to it's good articles

    ReplyDelete
  7. I don't unremarkablyy comment bbut I gotta state thank you for thhe post on this one :D.

    ReplyDelete
  8. Sir,
    Kindly share the blog to use of pragma and it's types-:

    Pragma serially reusable
    Pragma inline
    Pragma restrict Refrence
    Pragma autonomous transaction
    Pragma exception init

    Give with e.g

    ReplyDelete
  9. It's awesome desigtned for me to have a web site, which is
    good designed for my knowledge. thanks admin

    ReplyDelete