Re: alter system triggers

From: Joshua Collier <jcoll1970_at_gmail.com>
Date: Wed, 8 Oct 2014 14:06:32 -0700
Message-ID: <CAMiRvoHGaJ-W4rPvw62J+9VVkpewSJ6Yy2mj8MvNoDYJtFy-GA_at_mail.gmail.com>



what i am looking for is a way to intercept alter system calls, and let some through and stop others. auditing doesn't do that.

On Mon, Oct 6, 2014 at 8:55 AM, Yong Huang <yong321_at_yahoo.com> wrote:

> 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-l
Received on Wed Oct 08 2014 - 23:06:32 CEST

Original text of this message