0

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;
/
pavankumar2028
  • 43
  • 4
  • No need to reinvent the wheel. The *SYS roles are generally exempt from normal auditing policies and such, but their actions are audited by default using the AUDIT_SYS_OPERATIONS init parameter (https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/AUDIT_SYS_OPERATIONS.html#GUID-58176267-238C-40B5-B1F2-BB8BB9518950). – pmdba Jul 26 '23 at 14:40
  • The `SYSTEM` account should generally *NOT BE USED.* It is a template/prototypical DBA-privileged account and should be locked/disabled; shared accounts should be discouraged in general, especially for privileged access. DBAs and other privileged users should have individually authenticated accounts. – pmdba Jul 26 '23 at 14:40

0 Answers0