Auditing Logon/Logoff of all users in Oracle Database using Triggers.

As per the monthly database health check activity you may be asked by your customer to provide an audit information of all database user's logon and logoff time.


Here I will be explaining two simple way to do it.




First of all, create a table audit_log(in my case) as follows.

SQL> create table audit_log(name varchar2(50), time date, action varchar2(50));

Table created.


-- Create database logon trigger --

SQL> create or replace trigger logon_trig
after logon on database
begin
   insert into audit_log values (user, sysdate, 'LOGON');
   commit;
end logon_trig;  2    3    4    5    6
  7  /

Trigger created.


-- Create database logoff trigger --


SQL> create or replace trigger logoff_trig
before logoff on database
begin
   insert into audit_log values (user, sysdate, 'LOGOFF');
   commit;
end logoff_trig;  2    3    4    5    6
  7  /

Trigger created.


Notes: 

- Beware of logon triggers. If they are not working, you may not logon to Oracle.

- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement 
DATABASE triggers.




After Creating Logon and Logoff triggers on database, I connected to database with scott user and disconnected it again so that logon and logoff activity gets catch in audit_log table by logon and logff triggers created above.

SQL> !date
Sun May 15 15:15:45 IST 2016

SQL> conn scott  
Enter password:
Connected.

SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> conn /as sysdba
Connected.

SQL> !date
Sun May 15 15:16:15 IST 2016



SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

Session altered.

SQL> select * from audit_log;

NAME            TIME              ACTION
--------------- ----------------- ------------------------------
SYS             15/05/16 15:14:14 LOGOFF
SYS             15/05/16 15:15:14 LOGON
SYS             15/05/16 15:15:14 LOGOFF
SYS             15/05/16 15:15:52 LOGOFF
SCOTT           15/05/16 15:15:52 LOGON
SCOTT           15/05/16 15:15:54 LOGOFF
SYS             15/05/16 15:15:59 LOGON
SYS             15/05/16 15:16:14 LOGON
SYS             15/05/16 15:16:14 LOGOFF


Now you can see scott user logon and logoff activity got captured in the audit_log table by logon and logoff triggers created above.