Oracle User Audit- Logon & Log Off Trigger

Step1: Check audit is enabled or not .If not enabled it and bounce the database.

Audit can be done in two ways:  Database level or OS level.

SQL> show parameter audit

Check paramter audit_trail is =(DB|OS|db,extended | xml | xml,extended) means audit is enabled otherwise if it is NONE means audit trail is not enabled.

If audit_trail is NONE please make it enable using below steps (by default is disabled)

SQL>ALTER SYSTEM SET audit_trail=db or os SCOPE=SPFILE;
SQL>shu immediate;
SQL>startup;

"Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS.

SQL>@$ORACLE_HOME/rdbms/admin/cataudit.sql


P.N Oracle recommends that you use the OS setting, particularly if you are using an ultra-secure database configuration.

Step 2: Crating user audit table which hold the login and logoff information of the users.

sqlplus
connect / AS SYSDBA;

create table
   LOGIN_AUDIT_INFO_ALL
(
   user_id           varchar2(100),
   session_id           number,
   host              varchar2(1000),
   logon_day                 date,
   logon_time        varchar2(100),
   logoff_day                date,
   logoff_time       varchar2(10)
) tablespace USERS
;

Step3: Creating Logon Trigger

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into LOGIN_AUDIT_INFO_ALL values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
END;
/



If want  to exclude  login details of SYS,SYSTEM,DBSNMP and SYSMAN user.

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
if UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))
NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') -- list of users to ignore
THEN
insert into LOGIN_AUDIT_INFO_ALL values(
   SYS_CONTEXT('USERENV', 'SESSION_USER'),
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
END IF;
END;
/

STEP4: Creating LOGOFF trigger

create or replace trigger
   logoff_audit_trigger
before LOGOff ON DATABASE
BEGIN
insert into LOGIN_AUDIT_INFO_ALL values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
END;
/

If want to exclude the logoff details of SYS,SYSMAN,DBSNMP and SYSTEM user

create or replace trigger
   logoff_audit_trigger
before LOGOff ON DATABASE
BEGIN
if UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))
NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') -- list of users to ignore
THEN
insert into LOGIN_AUDIT_INFO_ALL values(
  SYS_CONTEXT('USERENV', 'SESSION_USER'),
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
END IF;
END;
/









Just to confirm:

Trigger name and created date 
select CREATED,OBJECT_NAME from dba_objects where OBJECT_TYPE='TRIGGER' and owner='SYS';
Table name and created date
SQL> select created,object_name from dba_objects where object_name='LOGIN_AUDIT_INFO_ALL' and object_type='TABLE';

Step5- Check the audit table after opening another session

COLUMN HOST FORMAT A45
COLUMN USER_ID FORMAT A18
SELECT * FROM SYS.LOGIN_AUDIT_INFO_ALL;

Step6- To disable and enable the trigger

ALTER TRIGGER SYS.LOGON_AUDIT_TRIGGER DISABLE;
ALTER TRIGGER SYS.LOGOFF_AUDIT_TRIGGER DISABLE;

ALTER TRIGGER SYS.LOGON_AUDIT_TRIGGER ENABLE;
ALTER TRIGGER SYS.LOGOFF_AUDIT_TRIGGER ENABLE;

Step7- To Purge Audit Data

TRUNCATE TABLE LOGIN_AUDIT_INFO_ALL;

Step8: To drop Trigger and table

DROP TRIGGER SYS.LOGON_AUDIT_TRIGGER;
DROP TRIGGER SYS.LOGOFF_AUDIT_TRIGGER;
DROP TABLE SYS.LOGIN_AUDIT_INFO_ALL;


Hope my blog help you !! Thanks and enjoy :) :)