Debug DDL trigger 2005-08-24 - By Wolfgang Breitling
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@(protected)
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@(protected)
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
|
|
