Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Enable and disable auditing using trigger

AmitE.Mar 11 2013 — edited Mar 12 2013
Hi 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 9 2013
Added on Mar 11 2013
9 comments
1,055 views