Re: alter system triggers

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 9 Oct 2014 04:45:41 +0700
Message-ID: <CAP50yQ9fNxkwETw-fd8FJYxBnokkc_4r0XMkdOg8eM4g1vOu0g_at_mail.gmail.com>

Dont let users have the alter system privilege. Instead provide a package that offers the calls you need / allow.
On Oct 9, 2014 4:08 AM, "Joshua Collier" <jcoll1970_at_gmail.com> wrote:

> 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:45:41 CEST