如何编写触发器/审计策略/存储过程捕获SYS/SYSTEM用户对指定Schema的操作会话详情

需求 #

编写触发器、审计策略或存储过程，获取SYS或SYSTEM用户对特定Schema（如HR）执行DML、DDL、DCL操作时的会话详情（含模块、程序等）。

现有方案及局限性 #

方案1：统一审计策略 #

作用：捕获SYS用户的所有操作
局限：Oracle统一审计日志表unified_audit_trail不包含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;

方案2：Schema级DDL触发器 #

作用：捕获当前Schema下DDL操作的会话详情（含模块、程序）
局限：无法捕获SYS用户的操作——SYS用户的Schema级操作不受该触发器约束。

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;
/

方案3：数据库级登录触发器 #

作用：捕获SYS用户（排除后台进程）的登录会话信息
局限：模块信息通常在登录后执行操作时才设置，因此无法获取实际操作时的模块详情。

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;
/

内容的提问来源于stack exchange，提问作者pavankumar