PREVENT USERS (SCHEMAS) FROM GETTING DROPPED

The below is the Trigger which is used to prevent the users or schemas from getting dropped.

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;

User created.

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

Grant succeeded.

ORADB>    

ORADB>select username from all_users where username = ‘USER1’;

USERNAME

——————————

USER1

ORADB>

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.

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