Cannot Drop a User

[Yogeeraj_1]

hi again,
*
could it be that you have a database BEFORE DROP trigger? YES - Most probably!!
*
Post the output of the following SQL query:
===========================================
select owner, substr(triggering_event,1,45)
from dba_triggers
where triggering_event like '%DROP%';
===========================================
*
revert
*
regards
Yogeeraj

[Yogeeraj_1]

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

[Scott Buckingham]

First of all, THANK YOU for sticking with this! I really appreciate it.

Now, Brian, here is the trace you requested. You'll see towards the end where the 'insufficient privilege' message appears. ABC_CODE is the first table under the M460QA817 user. Is there an issue with DUAL?

Yogeeraj, here is the output you requested from you select statement:

OWNER SUBSTR(TRIGGERING_EVENT,1,45)
------------------------------ ---------------------------------------------
SYS DROP
SYSTEM DROP
SYSTEM DROP
MDSYS DROP

[Scott Buckingham]

I got it! Thanks for the idea, Yogeeraj. After reading your posts on triggers, I did some investigating and found out that I did have a couple triggers in place that were preventing me from performing a drop. Problem solved!

[Brian Crabtree]

Great Answer Yogeeraj! :)

[Yogeeraj_1]

Thank you Brian. Glad to have helped! You do have some great replies too... Cheers