Re: Debug DDL trigger
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Wed, 24 Aug 2005 00:29:58 -0600
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@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@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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
