Auditing Oracle Database Logons

by voyageAnatolia.blogspot .com

I’m running a project at work at the moment to clean up some of our old version 8 and 9 databases. To do this I had to somehow track if anyone was logging into them and who. Luckly the team at Burleson consulting had some good information about Auditing and reporting Oracle user activity.

It consists of a table to store the information and a trigger that fires when anyone logs into the database.

create table stats$user_log
(
 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)
);
create or replace trigger logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
 user,
 sys_context('USERENV','SESSIONID'),
 sys_context('USERENV','HOST'),
 null,
 null,
 null,
 sysdate,
 to_char(sysdate, 'hh24:mi:ss'),
 null,
 null,
 null
);
END;
/

To view who has logged into the database you simply need to query the stats$user table like this.

select distinct user_id, logon_day from sys.stats$user_log;

I found it very helpful and was able to identify 4 databases that weren’t being used.

NOTE : I would be careful how long you leave the trigger in place. If the database is allot busier than you think it is, then the table could grow is size quite rapidlly.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s