Re: alter system triggers
From: Yong Huang <yong321_at_yahoo.com>
Date: 06 Oct 2014 15:57:57 -0000
Message-ID: <592477583.401875.1412610938566.JavaMail.yahoo_at_jws10733.mail.gq1.yahoo.com>
Josh,
I tried an "after alter on database" trigger. Indeed "alter system" does not fire the trigger, neither does "alter session". "Alter system" and "alter session" statements are not considered as DDLs ("alter system set encryption" is an exception because Oracle is not sure what to say in this regard. Do an in-page search for "DDL" at http://docs.oracle.com/database/121/SQLRF/statements_2017.htm) That makes me wonder if the trigger only works for "alter" SQLs that must be DDLs. So I tried a few more and find that "alter trigger compile" (obviously on a trigger other than this one) does not fire this trigger either. "Alter trigger compile" *is* a DDL although it does not update last_ddl_time of the compiled trigger. Surprisingly, "drop table" (with no "purge") and "flashback table to before drop" also fire the trigger. Anyway, "audit alter system" may be the best solution, as Seth already said.
Date: 06 Oct 2014 15:57:57 -0000
Message-ID: <592477583.401875.1412610938566.JavaMail.yahoo_at_jws10733.mail.gq1.yahoo.com>
Josh,
I tried an "after alter on database" trigger. Indeed "alter system" does not fire the trigger, neither does "alter session". "Alter system" and "alter session" statements are not considered as DDLs ("alter system set encryption" is an exception because Oracle is not sure what to say in this regard. Do an in-page search for "DDL" at http://docs.oracle.com/database/121/SQLRF/statements_2017.htm) That makes me wonder if the trigger only works for "alter" SQLs that must be DDLs. So I tried a few more and find that "alter trigger compile" (obviously on a trigger other than this one) does not fire this trigger either. "Alter trigger compile" *is* a DDL although it does not update last_ddl_time of the compiled trigger. Surprisingly, "drop table" (with no "purge") and "flashback table to before drop" also fire the trigger. Anyway, "audit alter system" may be the best solution, as Seth already said.
Yong Huang> Does anyone know of a trigger that will reliably fire on alter system and
> capture the commands via the objects such as :
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 06 2014 - 17:57:57 CEST