Enable and disable auditing using trigger
AmitE.Mar 11 2013 — edited Mar 12 2013Hi All,
DB version - 11.2.0.3
OS - RHEL 5.6
I am trying to create a logon and logoff trigger whenever the APPS session is established from a particular host and then disable auditing when that session logsoff.
Here are the DDLs that i am using -
=============================
CREATE OR REPLACE TRIGGER SYS.LOGON_audit
AFTER LOGON ON DATABASE
WHEN (USER like 'APPS')
declare
v_machine varchar2(30);
BEGIN
SELECT SYS_CONTEXT('USERENV','HOST') INTO v_machine FROM dual;
IF v_machine IN ('WORKGROUP\P181VWCERT01')
THEN
EXECUTE IMMEDIATE 'audit insert any table,update any table,select any table by access';
END IF;
END LOGON_audit;
/
CREATE OR REPLACE TRIGGER SYS.LOGOFF_audit
BEFORE LOGOFF ON DATABASE
WHEN (USER like 'APPS')
declare
v_machine2 varchar2(30);
BEGIN
SELECT SYS_CONTEXT('USERENV','HOST') INTO v_machine2 FROM dual;
IF v_machine2 IN ('WORKGROUP\P181VWCERT01')
THEN
EXECUTE IMMEDIATE 'noaudit insert any table,update any table,select any table';
END IF;
END LOGOFF_audit;
/
=========================================
However while doing tests the auditing is enabled when APPS logs in from WORKGROUP\P181VWCERT01, however, when the session logs off the audit does not disable.
May i seek some help on where i am making mistake?
Regards
Amit