Trigger to monitor successful logins in the database

By having a logon trigger in a database one can monitor or audit OS user, programs, DB users, time users logging into a database. It could also be used the change a behavior of a session by altering user’s session’s based on certain criteria.

CREATE TABLE sys.logon_audit
(
logon_time DATE,
username VARCHAR2(30), — DB user
osuser VARCHAR2(30), — OS user
machine VARCHAR2(64), — machine
program VARCHAR2(48 )
) TABLESPACE dba_tbs;

CREATE OR REPLACE TRIGGER sys.connection_audit
AFTER LOGON ON database
DECLARE
— use sys_context for 9i and above
— for 8i use userenv(‘sessionid’)
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session;
— WHERE audsid = sys_context(‘USERENV’,’sessionid’);
rec_c1 c1%ROWTYPE;

BEGIN
rec_c1 := NULL;

OPEN c1;
FETCH c1 INTO rec_c1;
— record will always be found, so need to check record not found
INSERT INTO user_audit(logon_time, username, osuser, machine, program)
VALUES (sysdate, user, rec_c1.osuser, rec_c1.machine, rec_c1.program);
CLOSE c1;
END;
/

Note: You may also implement a routine to delete data from the table as if the tablespace gets full users won’t be able to login.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s