Audit Connect :: Simplest Feature to Audit Oracle users logon / logoff

In the previous post - [ Click Here ], I outlined how we can audit database users logon and logoff activities using database triggers but here we will be using in-build database auditing feature for the same audit process hence no need to worry about coding database triggers for it.

Here we will be doing the simplest way to audit database users logon/logoff info using oracle database in-build feature - Audit Connect.

Login as sysdba and execute the following command to enable the oracle database users logon/logoff information.

SQL> audit connect;

Audit succeeded.

Let's connect with SCOTT user to check if it audits the logon.

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> !date

Sun May 15 19:25:16 IST 2016

SQL> conn /as sysdba

Connected.

Let's now fetch the records from DBA_AUDIT_TRAIL table for the user SCOTT.

SQL> select OS_USERNAME, USERNAME, USERHOST, EXTENDED_TIMESTAMP, DBID from dba_audit_trail where username='SCOTT';

OS_USERNAME     USERNAME        USERHOST                  EXTENDED_TIMESTAMP                             DBID

--------------- --------------- ------------------------- ---------------------------------------- ----------

aime            SCOTT           dadvfa1015                14-AUG-09 12.05.45.081543 PM +05:30      4023503584

aime            SCOTT           dadvfa1015                14-AUG-09 12.05.45.266236 PM +05:30      4023503584

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.08.01.824926 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.08.32.194633 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.08.39.830102 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.15.52.717458 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.15.54.017694 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.34.03.395507 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 03.34.38.646395 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 07.24.10.688440 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 07.24.12.903179 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 07.25.12.692897 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 07.25.14.894100 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 07.40.10.746310 PM +05:30       515168651

oracle          SCOTT           rac2.rajdbsolutions.com   15-MAY-16 07.40.21.921919 PM +05:30       515168651

Following information can be obtained from DBA_AUDIT_TRAIL:


SQL> desc dba_audit_trail;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 OS_USERNAME                                  VARCHAR2(255)
 USERNAME                                     VARCHAR2(30)
 USERHOST                                     VARCHAR2(128)
 TERMINAL                                     VARCHAR2(255)
 TIMESTAMP                                    DATE
 OWNER                                        VARCHAR2(30)
 OBJ_NAME                                     VARCHAR2(128)
 ACTION                              NOT NULL NUMBER
 ACTION_NAME                                  VARCHAR2(28)
 NEW_OWNER                                    VARCHAR2(30)
 NEW_NAME                                     VARCHAR2(128)
 OBJ_PRIVILEGE                                VARCHAR2(16)
 SYS_PRIVILEGE                                VARCHAR2(40)
 ADMIN_OPTION                                 VARCHAR2(1)
 GRANTEE                                      VARCHAR2(30)
 AUDIT_OPTION                                 VARCHAR2(40)
 SES_ACTIONS                                  VARCHAR2(19)
 LOGOFF_TIME                                  DATE
 LOGOFF_LREAD                                 NUMBER
 LOGOFF_PREAD                                 NUMBER
 LOGOFF_LWRITE                                NUMBER
 LOGOFF_DLOCK                                 VARCHAR2(40)
 COMMENT_TEXT                                 VARCHAR2(4000)
 SESSIONID                           NOT NULL NUMBER
 ENTRYID                             NOT NULL NUMBER
 STATEMENTID                         NOT NULL NUMBER
 RETURNCODE                          NOT NULL NUMBER
 PRIV_USED                                    VARCHAR2(40)
 CLIENT_ID                                    VARCHAR2(64)
 ECONTEXT_ID                                  VARCHAR2(64)
 SESSION_CPU                                  NUMBER
 EXTENDED_TIMESTAMP                           TIMESTAMP(6) WITH TIME Z
                                              ONE
 PROXY_SESSIONID                              NUMBER
 GLOBAL_UID                                   VARCHAR2(32)
 INSTANCE_NUMBER                              NUMBER
 OS_PROCESS                                   VARCHAR2(16)
 TRANSACTIONID                                RAW(8)
 SCN                                          NUMBER
 SQL_BIND                                     NVARCHAR2(2000)
 SQL_TEXT                                     NVARCHAR2(2000)
 OBJ_EDITION_NAME                             VARCHAR2(30)
 DBID                                         NUMBER

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.