Re: How to audit/trigger "ALTER SESSION" Statements?

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Sun, 24 Apr 2005 17:08:37 GMT
Message-ID: <Xns964267301D0E6SunnySD@68.6.19.6>

"Michel Cadot" <micadot{at}altern{dot}org> wrote in news:426bd128$0$23350$636a15ce_at_news.free.fr:

> 
> "IANAL_VISTA" <IANAL_Vista_at_hotmail.com> a écrit dans le message de
> news:Xns96424FCC5441ESunnySD_at_68.6.19.6...

>| "Michel Cadot" <micadot{at}altern{dot}org> wrote in
>| news:426b32f3$0$18032$636a15ce_at_news.free.fr:
>|
>| >
>| > SQL> create or replace trigger mytrig
>| > 2 before ddl on database
>| > 3 declare
>| > 4 begin
>| > 5 dbms_output.put_line('Mytrig -> '||ora_login_user||'
>| > executes '||ora_sysevent); 6 end;
>| > 7 /
>| >
>| > Trigger created.
>| >
>| > -- Checking the trigger is OK
>| >
>| > SQL> set serveroutput on size 10000 format wrap
>| > SQL> create table ttt (col number);
>| >
>| > Table created.
>| >
>| > SQL> exec null;
>| > Mytrig -> TEST executes CREATE
>| >
>| > PL/SQL procedure successfully completed.
>| >
>| > SQL> drop table ttt;
>| >
>| > Table dropped.
>| >
>| > SQL> exec null;
>| > Mytrig -> TEST executes DROP
>| >
>| > PL/SQL procedure successfully completed.
>| >
>| > -- Checking with alter session
>| >
>| > SQL> alter session set cursor_sharing=exact;
>| >
>| > Session altered.
>| >
>| > SQL> exec null;
>| >
>| > PL/SQL procedure successfully completed.
>| >
>| > --> no message from trigger (maybe alter session is not a DDL)
>| >
>| > Regards
>| > Michel Cadot
>| >
>| >
>| >
>|
>| I'm not clear on why you ASSUMED that the DBMS_OUT would appear in
>| your session window.
>|

> 
> Reread my post SQL*Plus actually displays dbms_output messages for
> drop and create table statements (you see them after the "exec null"
> that just forces SQL*Plus to flush dbms_output buffer onto screen) but
> not for alter session so i assume that the trigger is not fired by
> alter session. 
> 
> Regards
> Michel Cadot
> 
> 
> 

Yes, but the trigger "fires in the DB's background context"; which really is separate & distinct from your session. Just because the DBMS_OUTPUT works for your "foreground" session, doesn't mean your session will see the output from a background session.

In any case I demonstrated that the DDL trigger does fire as expected. Received on Sun Apr 24 2005 - 12:08:37 CDT