PREVENT USERS (SCHEMAS) FROM GETTING DROPPED

The below Trigger is used to prevent users and schemas from Getting Dropped Mistakenly,

Create OR Replace Trigger TrgDropUserRestrict
Before Drop On Database
Declare
Begin
     If Ora_Dict_Obj_Name In ('SH','OUTLN','SCOTT','user1')    Then
                     Raise_Application_Error(-20001,'Cannot Drop User
                    '||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !'); 
     End If;
End;

/


1. create a new user and grant privileges to the new user.


ORADB>create user user1 identified by laser default tablespace users;


ORADB>grant connect,resource,debug connect session,debug any procedure to user1;


Grant succeeded.

ORADB>select username from all_users where username = 'USER1';

USERNAME

------------------------------

USER1

2. Run the procedure to prevent the user to be dropped.

ORADB>Create OR Replace Trigger TrgDropUserRestrict

Before Drop On Database

Declare

Begin

     If Ora_Dict_Obj_Name In ('SH','OUTLN','SCOTT','USER1')    Then

                     Raise_Application_Error(-20001,'Cannot Drop User

                    '||ora_dict_obj_name||' Contact Your Database Administrator For Dropping This User !'); 

     End If;

End;

/  2    3    4    5    6    7    8    9   10  

Trigger created.

ORADB>

Note: You can specify N number of users in the Ora_Dict_Obj_Name which should not get dropped.

3. Try to drop the user and check if the user is dropped.

ORADB>drop user user1 cascade;

drop user user1 cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Cannot Drop User

user1 Contact Your Database Administrator For Dropping This User !

ORA-06512: at line 4

ORADB>drop user scott cascade;

drop user scott cascade

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Cannot Drop User

SCOTT Contact Your Database Administrator For Dropping This User !

ORA-06512: at line 4