Hello all!
So, I was attending a client who wanted to audit logon on database without Advanced Security pack. Complicated? Not at all.
Performatic? eehh, not really the best option, but Oracle is not really good for opening sessions anyway (this is why we have connection pools), so I consider this not bad…
Ok, how to do it?
A sequence for ID control:
create sequence sys.logon_capture_seq;
A Table for logon logging:
create table sys.logon_capture ( id number, capture_time date, authenticated_identity varchar2(30), authentication_method varchar2(30), identification_type varchar2(30), network_protocol varchar2(30), session_user varchar2(30), os_user varchar2(30), host varchar2(30), ip_address varchar2(30), program varchar2(30), module varchar2(30), action varchar2(30), service_name varchar2(30)) tablespace logon_capture;
* Here is an important point: Be always sure to have enough space on this tablespace, otherwise all new connections can be frozen.
Create the logon trigger:
create or replace trigger SYS.trg_capture_logons
after logon on database
when (SYS_CONTEXT ('USERENV', 'SESSION_USER') not in ('SYS'))
begin
insert into sys.logon_capture (id,capture_time,authenticated_identity,authentication_method,identification_type,network_protocol,session_user,os_user,host,ip_address,program,module,action,service_name)
select
sys.logon_capture_seq.nextval,
sysdate,
substr(sys_context('userenv','authenticated_identity'),1,30),
substr(sys_context('userenv','authentication_method'),1,30),
substr(sys_context('userenv','identification_type'),1,30),
substr(sys_context('userenv','network_protocol'),1,30),
substr(sys_context('userenv','session_user'),1,30),
substr(sys_context('userenv','os_user'),1,30),
substr(sys_context('userenv','host'),1,30),
substr(sys_context('userenv','ip_address'),1,30),
substr(program,1,30),
substr(sys_context('userenv','module'),1,30),
substr(sys_context('userenv','action'),1,30),
substr(sys_context('userenv','service_name'),1,30)
from v$session
where sid = sys_context('userenv','sid');
commit;
exception
when others then null;
end;
/
Know what is a good idea? a cleanup job, keeping only the last 6 month of “audit” data:
begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'SYS.PURGE_LOGON_CAPTURE', job_type => 'PLSQL_BLOCK', job_action => 'begin delete from sys.logon_capture where capture_time < add_months(sysdate, -6); commit; end;', number_of_arguments => 0, start_date => trunc(sysdate+1) + 23/24, repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=40;BYSECOND=0', enabled => false, auto_drop => false, comments => ''); end; / exec DBMS_SCHEDULER.ENABLE ( name => 'SYS.PURGE_LOGON_CAPTURE' ); select owner, enabled from dba_scheduler_jobs where job_name = 'PURGE_LOGON_CAPTURE'
Hope it helps you!