hi,
-
I tried to simulate something similar!
-
see below:
===========================================
yd@MYDB.MU> grant create session, create user, drop user to a identified by a;
Grant succeeded.
*
yd@MYDB.MU> grant create session to b identified by b;
Grant succeeded.
*
yd@MYDB.MU> create table app_users ( username varchar2(30) );
Table created.
*
yd@MYDB.MU> insert into app_users values ( 'B' );
1 row created.
*
yd@MYDB.MU> create or replace trigger drop_user_trigger
2 before drop on database
3 when ( user = 'A' )
4 declare
5 l_cnt number;
6 l_name varchar2(30);
7 begin
8 if ( ora_dict_obj_type = 'USER' )
9 then
10 l_name := ORA_DICT_OBJ_NAME;
11 select count(*) into l_cnt
12 from dual
13 where exists ( select null
14 from app_users
15 where username = l_name );
16 if ( l_cnt <> 1 )
17 then
18 raise_application_error( -20001, 'Insufficient privileges for DELETE or DROP' );
19 end if;
20 end if;
21 end;
22 /
*
Trigger created.
*
yd@MYDB.MU> @connect a/a
*
a@MYDB.MU> drop user scott;
drop user scott
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Insufficient privileges for DELETE or DROP
ORA-06512: at line 15
*
a@MYDB.MU> drop user b;
*
User dropped.
*
a@MYDB.MU>
*
Hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
