Oracle Logon Logoff functionality

The Oracle auditing functionality does not always give you everything you want, or as easily as you want it.
This is a script I created that produces a user logon/logoff tracking system in your Oracle database.

I recently pulled this out of the cobwebs (as you can see in the comments) to use for a system migration. It was great for showing the application team where all the logins came from so they could open networks and firewalls for the new environment.

Note: This does make changes in the SYSTEM account and you should test carefully. If for some reason the trigger/procedure fails it can prevent users from logging in. So,
be sure to TEST and monitor carefully after installing on a production system.

 

-- This script creates the logon_history table and logon/logoff triggers
-- for tracking logins

-- Created 2011/01/11 Jed S. Walker
-- Updated 2015/03/18 Jed S. Walker -- added instance to support RAC

prompt You should run cr_dbam_tablespace.sql first
prompt You must run cr_dbam_log.sql first
prompt Hit Enter to continue
accept continue

set echo on

spool cr_logon_history.lst

connect / as sysdba

set termout off
column dbtime new_value dbtime
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dbtime from dual;
column dbname new_value dbname
select name dbname from v$database;
set termout on

prompt Provide the DBAM user password to continue
whenever sqlerror exit
connect dbam/&dbam_password
whenever sqlerror continue

create table logon_history
(
 sessionid number not null, --note, not v$session.sid
 instance number not null,
 username varchar2(30) not null,
 sid number not null,
 client_id varchar2(64),
 os_username varchar2(30),
 proxy_username varchar2(30),
 proxy_sid number,
 host varchar2(54),
 host_ip varchar2(40),
 terminal varchar2(20),
 isdba varchar2(30),
 program varchar2(48),
 action varchar2(32),
 module varchar2(48),
 logon_time date,
 logoff_time date,
 elapsed_seconds number
)
;
create index logon_history_idx1 on logon_history (sessionid, instance);
create index logon_history_idx2 on logon_history (username, instance, sid, logon_time);
create index logon_history_idx3 on logon_history (logon_time);
comment on column logon_history.sessionid is 'Auditing session ID (not session SID).';
comment on column logon_history.instance is 'Instance this session is/was on'';
comment on column logon_history.username is 'Username from v$session';
comment on column logon_history.sid is 'SID from v$session';
comment on column logon_history.client_id is 'CLIENT_ID if used';
comment on column logon_history.os_username is 'OS username of logged in user';
comment on column logon_history.os_username is 'Proxy username if user proxied in via another user';
comment on column logon_history.os_username is 'Proxy SID (v$session) if user proxied in via another user';
comment on column logon_history.host is 'Host of machine being logged in from';
comment on column logon_history.host is 'IP (if available) of machine being logged in from';
comment on column logon_history.terminal is 'Terminal type at OS being logged in from';
comment on column logon_history.isdba is 'Does user logon have DBA privileges';
comment on column logon_history.program is 'Program being used at logoff';
comment on column logon_history.action is 'Last action performed before logoff';
comment on column logon_history.module is 'Last module used before logoff';
comment on column logon_history.logon_time is 'Time of logon';
comment on column logon_history.logoff_time is 'Time of logoff';
comment on column logon_history.elapsed_seconds is 'Number of seconds the session was maintained';


-- procedure for cleaning up from the table
-- going on the cheap here for quickness, changes can just be made and recompiled
create or replace procedure logon_history_clean
as
begin
 delete 
 from logon_history 
 where logon_time < (sysdate - interval '1' year - interval '3' day);
 dbam_log_insert('INFO','logon_history_clean','Clean Info','Cleaned ' || SQL%ROWCOUNT || ' rows.');
 commit;
exception
 when others then
 dbam_log_insert('FAIL','logon_history_clean','logon_history_clean failed',SQLERRM);
 commit;
end;
/

-- job to run the dbam_log_clean procedure regulary
begin
 dbms_scheduler.create_job(job_name=>'JOB_LOGON_HISTORY_CLEAN', 
 job_type=>'STORED_PROCEDURE',
 job_action=>'logon_history_clean',
 start_date=>sysdate,
 repeat_interval=>'FREQ=Daily;BYHOUR=01;BYMINUTE=01',
 enabled=>true
 );
end;
/


prompt Now switching to SYSDBA to create logon triggers
connect / as sysdba

create or replace trigger dbam_logon_history_logon
AFTER LOGON ON DATABASE
BEGIN
 insert into dbam.logon_history values
 (sys_context('USERENV','SESSIONID'),
 sys_context('USERENV','INSTANCE'),
 sys_context('USERENV','SESSION_USER'),
 sys_context('USERENV','SID'),
 sys_context('USERENV','CLIENT_IDENTIFIER'),
 sys_context('USERENV','OS_USER'),
 sys_context('USERENV','PROXY_USER'),
 sys_context('USERENV','PROXY_USERID'),
 sys_context('USERENV','HOST'),
 sys_context('USERENV','IP_ADDRESS'),
 sys_context('USERENV','TERMINAL'),
 sys_context('USERENV','ISDBA'),
 (select program from v$session 
 where sys_context('USERENV','SID') = sid
 and sys_context('USERENV','SESSIONID') = audsid
 and rownum = 1
 -- rownum=1 because SYS connections can't find a unique row due to duplicated sessionid
 ),
 null,
 null,
 sysdate,
 null,
 null
 );
 commit;
EXCEPTION
 when others then 
 dbam.dbam_log_insert('FAIL','DBAM_LOGON_HISTORY_LOGON','Exception', SQLERRM); 
 commit;
END;
/

create or replace trigger dbam_logon_history_logoff
BEFORE LOGOFF ON DATABASE
DECLARE
 v_session_user v$session.username%type;
 v_session_id v$session.sid%type;
 v_program v$session.program%type;
 v_action v$session.action%type;
 v_module v$session.module%type;
 v_step number;
BEGIN
 -- we don't store logoff for SYS because there is not a way to uniquely identify the rows due
 -- to duplicated sessionid in the user environment
 v_step:=1;
 select sys_context('USERENV','SESSION_USER') into v_session_user from dual;
 v_step:=2;
 -- !SYS connections because can't find a unique row due to duplicated sessionid
 if v_session_user not in ('SYS','SYSMAN') then
 v_step:=3;
 select program, action, module
 into v_program, v_action, v_module
 from v$session -- no need for instance here since using local instance v$ instead of gv$
 where sid = sys_context('USERENV','SID')
 and audsid = sys_context('USERENV','SESSIONID');
 v_step:=4;
 update dbam.logon_history
 set program = v_program,
 action = v_action,
 module = v_module,
 logoff_time = sysdate
 where sid = sys_context('USERENV','SID')
 and sessionid = sys_context('USERENV','SESSIONID')
 and instance = sys_context('USERENV','INSTANCE');
 v_step:=5;
 update dbam.logon_history
 set elapsed_seconds = round((logoff_time - logon_time)*86400)
 where sid = sys_context('USERENV','SID')
 and sessionid = sys_context('USERENV','SESSIONID')
 and instance = sys_context('USERENV','INSTANCE');
 commit;
 end if;
EXCEPTION
 when others then
 dbam.dbam_log_insert('FAIL','DBAM_LOGON_HISTORY_LOGOFF','Exception', 'step=' || v_step || ':' || v_session_user || ':' || SQLERRM); 
 commit;
END;
/


show user


spool off
Advertisements