How to restrict some user getting drop accidentally

Step 1. Lets create an user first.


SQL> create user soumya IDENTIFIED BY soumya;

User created.

SQL> grant connect,resource to soumya;



Grant succeeded.

Step 2. Run the procedure to restrict the user to be dropped.

SQL> Create OR Replace Trigger UserDropRestrict
  2
  3  Before Drop On Database
  4
  5  Declare
  6
  7  Begin
  8
  9       If Ora_Dict_Obj_Name In ('HR','SCOTT','SOUMYA')    Then
 10
 11                       Raise_Application_Error(-20001,'Cannot Drop User'||ora_dict_obj_name||' Your are not allowed to drop this User ! Please contact DBA !');
 12
 13       End If;
 14
 15  End;
 16
 17  /

Trigger created.



Step 3. Now lets try to drop the users mentioned in above trigger.

SQL> drop user soumya cascade;
drop user soumya cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Cannot Drop UserSOUMYA Your are not allowed to drop this User !
Please contact DBA !
ORA-06512: at line 7


So this way we can prevent some important schemas to be dropped mistakenly.