I need help to write a trigger/audit/procedure to get the session details like module, program in for the DML/DDL,DCL performed by "SYS" or "SYSTEM" on a particular schema e.g. HR.
CASE1: This is the audit policy created to fetch all the actions of sys user. Unified_audit_trail doesn't have program
create audit policy sys_connection_log
actions all
when q'~ sys_context('userenv', 'session_user') = 'SYS' AND sys_context('userenv','IP_ADDRESS') is not null ~'
evaluate per statement
only toplevel;
CASE2: This is the after ddl trigger to fetch all the DDL performing session details in the current schema. This is not capturing the sys schema details
CREATE OR REPLACE TRIGGER sys_connection_log
AFTER DDL ON SCHEMA
declare
os_user varchar2(128);
v_sid number;
v_sessuser varchar2(128);
v_program varchar2(128);
v_module varchar2(128);
v_client_ip varchar2(128);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate
'select distinct sid from v$mystat' into v_sid;
execute immediate
'select osuser, program,module from gv$session where sid = :b1'
into os_user, v_program,v_module using v_sid;
select sys_context('userenv','SESSION_USER') into v_sessuser from dual;
select sys_context('userenv','IP_ADDRESS') into v_client_ip from dual;
insert into sys_connection_log values
(os_user, systimestamp,v_sessuser, v_client_ip, v_program,v_module);
commit;
end;
/
CASE3: This is the logon trigger to fetch all the operations performed by SYS except the background processes. This is not capturing the Module details as the module is getting set post the logon
create or replace trigger sys.log_sys_connections after logon on database
declare
os_user varchar2(128);
v_sid number;
v_sessuser varchar2(128);
v_program varchar2(128);
v_module varchar2(128);
v_client_ip varchar2(128);
begin
IF USER = 'SYS' AND sys_context('userenv','IP_ADDRESS') is not null
then
execute immediate
'select distinct sid from sys.v$mystat' into v_sid;
execute immediate
'select osuser, program,module from sys.gv$session where type <>''BACKGROUND'' and sid = :b1'
into os_user, v_program,v_module using v_sid;
select sys_context('userenv','SESSION_USER') into v_sessuser from dual;
select sys_context('userenv','IP_ADDRESS') into v_client_ip from dual;
insert into sys.sys_connection_log values
(os_user, systimestamp,v_sessuser, v_client_ip, v_program,v_module);
commit;
end if;
end;
/