Oracle Database Logon Trigger

The various security mandates these days required a lot of auditing on the database. Though there are a host of various third party tools and Oracle tools (Audit Vault) available for this. In this exercise we will design a simple login and logout capture mechanism for the database.

1. Create Auditing Table

create table
SYS.LOGIN_AUDIT_INFO_ALL
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

2. Create 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'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/

3. Create Logoff Trigger

create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/
4. Check the audit table after opening another session

SELECT * FROM SYS.LOGIN_AUDIT_INFO_ALL

Check for Logon Triggers in Oracle

Query to check if there is any logon triggers defined for your database. Logon trigger is a  trigger which fires an event based on login to a schema/user. It can be used either for auditing purpose or some other event.

 

select * from dba_triggers where triggering_event like ‘%LOGON%’;

Backup Controlfile to Trace : Automate using Triggers

PL/SQL Procedure to Automatically Backup ControlFile to trace whenever you are shutting down or starting up the database.

‘ALTER DATABASE BACKUP CONTROLFILE TO TRACE’ command at
database startup or shutdown.

For DBA’s requiring to take regular backup copies of the controlfile.It is highly recommended to systematically keep a backup of the controlfiles as soon as the structure of a database occurs.The triggers before or after events are appropriate to automate administrative tasks.

You can in theory use any commands that can be put in pl/sql
scripts, and automate the execution of them.

*******************************************************
*** Technical method
*******************************************************

1. Create the procedure that executes the ‘ALTER DATABASE
BACKUP CONTROLFILE TO TRACE’ statement.

CREATE OR REPLACE PROCEDURE proc_control_to_trace AS
 cursor1 INTEGER;
 BEGIN
 cursor1 := dbms_sql.open_cursor;
 dbms_sql.parse(cursor1, 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE',
 dbms_sql.native);
 dbms_sql.close_cursor(cursor1);
 END;
/

2. Test the Procedure

SQL> execute sys.proc_control_to_trace;
PL/SQL procedure successfully completed.

3.      Create Event Trigger

-- AFTER STARTUP TRIGGER
CREATE OR REPLACE TRIGGER db_startup_control_trace
AFTER STARTUP ON DATABASE
BEGIN
sys.proc_control_to_trace;
end;
/
-- BEFORE SHUTDOWN TRIGGER
CREATE OR REPLACE TRIGGER db_startup_control_trace
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.proc_control_to_trace;
end;
/

On older versions of Oracle you may get an error on BEFORE SHUTDOWN triggers.
Those versions are since long desupported. The workaround is to use an AFTER STARTUP trigger.

4. Shutdown or start the database to check that the trace file is generated.