Re: Debug DDL trigger

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 24 Aug 2005 00:29:58 -0600
Message-Id: <6.2.3.4.2.20050824001902.0377bb30@pop.centrexcc.com>

Looks to me like it's firing:

scott                          August 24, 2005

00:15:17 ora92.scott> select * from dba_role_privs where grantee=user;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT                          DBA                            NO  YES
SCOTT                          CONNECT                        NO  YES
SCOTT                          SELECT_CATALOG_ROLE            NO  YES

3 rows selected.

00:16:16 ora92.scott> select sys_context('USERENV','CURRENT_SCHEMA') from dual 00:17:38 2 /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

---

SCOTT 1 row selected.

00:17:38 ora92.scott> CREATE OR REPLACE TRIGGER "LOGON_TRG"
00:17:51   2  after logon on schema
00:17:51   3  begin
00:17:51   4    execute immediate 'alter session set current_schema=sys';
00:17:51   5  end;
00:17:51   6  /

Trigger created.

00:17:52 ora92.scott> connect scott/tiger_at_ora92 Connected.
00:18:19 ora92.scott> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

---

SYS 1 row selected.

00:18:27 ora92.scott>

It also works as a database logon trigger:

sys                            August 24, 2005
00:25:58 ora92.sys> CREATE OR REPLACE TRIGGER "LOGON_TRG"
00:26:15   2  after logon on database

00:26:15 3 begin
00:26:15 4 dbms_application_info.set_client_info('Greetings from the trigger');
00:26:15 5 end;
00:26:15 6 /

Trigger created.

00:26:15 ora92.sys> connect scott/tiger_at_ora92 Connected.

00:27:06 ora92.sys> select sid from v$mystat where rownum=1;

        SID

---

         11

1 row selected.

00:27:19 ora92.sys> select client_info from v$session where sid=11;

CLIENT_INFO

---

Greetings from the trigger

1 row selected.

00:27:35 ora92.sys>

At 12:07 AM 8/24/2005, Jared Still wrote:
>I believe the triggers that Raj was thinking of were logon triggers.
>
>Those do not fire for accounts with the DBA privilege.
>
>Jared

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 24 2005 - 01:30:43 CDT