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

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Sun, 24 Apr 2005 14:50:39 GMT
Message-ID: <Xns96424FCC5441ESunnySD@68.6.19.6>

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

>
> "DA Morgan" <damorgan_at_x.washington.edu> a écrit dans le message de
> news:1114284873.945136_at_yasure...

>| mschott14_at_hotmail.com wrote:
>|
>| > right, so product_profile_table is useless for me since it is only
>| > useful for SQL*Plus.
>| >
>| > But the point is, even granting the users ONLY "CREATE SESSION"
>| > they will be able to issue
>| > ALTER SESSION set cursor_sharing=xxxx!! Even explicitly revoking
>| > the ALTER SESSION Privilege they still will be able to do am ALTER
>| > SESSIONE set cusrsor_sharing=xxx!!!! But I need a way to PREVENT
>| > this. I still dont see a solution for this here in the above
>| > statements :( 
>| >
>| > Regards,
>| > Michael
>|
>| http://www.psoug.org
>| click on Morgan's Library
>| click on DDL triggers
>| -- 
>| Daniel A. Morgan
>| University of Washington
>| damorgan_at_x.washington.edu
>| (replace 'x' with 'u' to respond)

>
> The OP already said he tried DDL trigger and it did not work
> and i'm afraid he's right (9.2.0.4):
>
> 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.

SQL> create table audit_ddl (now date);

Table created.

SQL> create or replace trigger mytrig
  2 before ddl on database
  3 declare
  4 begin
  5 insert into audit_ddl values(sysdate)   6 end;
  7 /

Trigger created.

SQL> create table ttt (col number);

Table created.

SQL> insert into ttt values(1);

1 row created.

SQL> select count(*) from ttt;

COUNT(*)

SQL> commit;

Commit complete.

SQL> drop table ttt;

Table dropped.

SQL> select * from audit_ddl;

NOW

24-APR-05
24-APR-05 SQL> Received on Sun Apr 24 2005 - 09:50:39 CDT