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
Loading ...
Total Users : 1823282