Database Oracle

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.