Search

After logon trigger - trace user sessions

Updated: Jan 28

-- Activate trace for sessions using after logon database trigger

--

-- Tables:

--

-- SYS.XX_LOGIN_TRACE_CONDITION - add here the conditions for activating trace

-- SYS.XX_LOGIN_TRACED_SESSIONS - contains the sessions for which the trace was

activated (with trace file name)

-- SYS.XX_LOGIN_TRACE_ERRORS - errors during execution of logon trigger

--

-- Trigger:

--

-- SYS.XX_LOGIN_TRACE_TRG

--

-- This script should be executed by SYS user

------------------------------------------------------------------------

-- SYS.XX_LOGIN_TRACE... tables

------------------------------------------------------------------------

create table SYS.XX_LOGIN_TRACE_CONDITION

(USERNAME VARCHAR2(30),

OSUSER VARCHAR2(30),

MACHINE VARCHAR2(100),

PROGRAM VARCHAR2(100),

MODULE VARCHAR2(100),

ACTIVE CHAR(1)

);


-- For Oracle Applications add the following grant:

grant select, insert, update, delete on SYS.XX_LOGIN_TRACE_CONDITION to apps with grant option;


create table SYS.XX_LOGIN_TRACED_SESSIONS

( SID NUMBER,

SERIAL# NUMBER,

USERNAME VARCHAR2(30),

OSUSER VARCHAR2(30),

MACHINE VARCHAR2(100),

PROGRAM VARCHAR2(100),

MODULE VARCHAR2(100),

START_DATE DATE,

TRACE_FILE CHAR(300)

);


-- For Oracle Applications add the following grant:

grant select, insert, update, delete on SYS.XX_LOGIN_TRACED_SESSIONS to apps with grant option;


create table SYS.XX_LOGIN_TRACE_ERRORS

(ERROR_TIME DATE,

ERROR_TEXT VARCHAR2(1000)

);


-- For Oracle Applications add the following grant:

grant select, insert, update, delete on SYS.XX_LOGIN_TRACE_ERRORS to apps with grant option;


------------------------------------------------------------------------

-- SYS.XX_LOGIN_TRACE_TRG after logon trigger

------------------------------------------------------------------------

create or replace trigger SYS.XX_LOGIN_TRACE_TRG after logon on database

declare

m_sid v$session.sid%type := 0;

m_serial v$session.serial#%type := 0;

m_sessionid v$session.audsid%type := 0;

m_err varchar2(1000) := '';

begin


if USER in ('SYS', 'SYSTEM', 'DBSNMP')

then

return;

end if;


select SYS_CONTEXT('USERENV','SESSIONID') into m_sessionid from dual;


begin

select s.sid, s.serial#

into m_sid, m_serial

from v$session s,

sys.xx_login_trace_condition c

where s.audsid = m_sessionid

and (upper(s.username) like upper(c.username) or c.username is null)

and (upper(s.osuser) like upper(c.osuser) or c.osuser is null)

and (upper(s.machine) like upper(c.machine) or c.machine is null)

and (upper(s.program) like upper(c.program) or c.program is null)

and (upper(s.module) like upper(c.module) or c.module is null)

and upper(c.active) = 'Y'

and rownum = 1;

exception

when no_data_found then

return;

when others then

m_err := SQLERRM;

insert into SYS.XX_LOGIN_TRACE_ERRORS (error_time, error_text)

values (sysdate, m_err);

return;

end;


begin

execute immediate 'alter session set MAX_DUMP_FILE_SIZE = UNLIMITED';

dbms_system.set_ev (m_sid, m_serial, 10046, 12, '');

exception

when others then

m_err := SQLERRM;

insert into SYS.XX_LOGIN_TRACE_ERRORS (error_time, error_text)

values (sysdate, m_err);

return;

end;


insert into sys.xx_login_traced_sessions (sid, serial#, username, osuser, machine, program, module, start_date, trace_file)

(select s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.module, sysdate, p.tracefile

from v$session s, v$process p

where s.paddr = p.addr

and s.sid = m_sid

and s.serial# = m_serial);


exception

when others then

m_err := SQLERRM;

insert into SYS.XX_LOGIN_TRACE_ERRORS (error_time, error_text)

values (sysdate, m_err);

end;

/


---------------------------------------------------------------------------------------

-- Example of inserts into SYS.XX_LOGIN_TRACE_CONDITION

-- for activating trace on discoverer sessions

---------------------------------------------------------------------------------------

/*

insert into SYS.XX_LOGIN_TRACE_CONDITION (username, osuser, machine, program, module, active)

values ('APPS', '', '', '', 'dis51usr.exe', 'Y');


insert into SYS.XX_LOGIN_TRACE_CONDITION (username, osuser, machine, program, module, active)

values ('APPS', '', '', '', 'dis51adm.exe', 'Y');


insert into SYS.XX_LOGIN_TRACE_CONDITION (username, osuser, machine, program, module, active)

values ('APPS', '', '', '', 'Disco%', 'Y');


commit;

*/

------------------------------------------------------------------------


/*

select * from SYS.XX_LOGIN_TRACE_CONDITION;

select * from SYS.XX_LOGIN_TRACE_ERRORS;

select * from SYS.XX_LOGIN_TRACED_SESSIONS;

*/




9 views0 comments

Recent Posts

See All

INCLUDE parameter of expdp

-- Export all public database links expdp user/password full=y \ include=db_link:"in (select db_link from dba_db_links where owner = 'PUBLIC')" -- Export one table expdp user/password include=table:"i

Multiplexing Redo Log and Control files

-- List redo log files select group#, member from v$logfile order by group#, member; -- Check redo log file status select group#, members, status from v$log; -- Adding redo log file to a group alter d

APEX - change user's password

sqlplus / as sysdba select owner, table_name from dba_tables where table_name = upper('wwv_flow_fnd_user'); -- Change schema name to whatever you want ALTER SESSION SET CURRENT_SCHEMA = APEX_050100; s