How Does Oracle remove the drop permission of some users!

Sometimes, during project development, database tables may be deleted by mistake. To avoid this problem, how to strictly control whether a user cannot perform the drop permission on his/her own database requires relevant settings. Because the default connect permission also has the table structure of the drop user, you can use product_user_profile to restrict only SQL * Plus operations, but not other third-party Oracle connection tools. The most effective method is to create a trigger under the System user.

The details are as follows:

This trigger is created under the System user, and several temp users cannot DOP their own objects. You can modify the trigger by referring to it.

Create or replace trigger "trg_deny_drop_ubisp" before
Drop on Database
Begin
If (ora_dict_obj_owner = 'temp _ ods 'or
Ora_dict_obj_owner = 'temp _ mid 'or
Ora_dict_obj_owner = 'temp _ DW 'or
Ora_dict_obj_owner = 'temp _ report' or
Ora_dict_obj_owner = 'temp _ web' or
Ora_dict_obj_owner = 'temp _ report_new ')
And ora_login_user <> 'temp _ admin' --- excluded users
And ora_dict_obj_type = 'table' and ora_dict_obj_name not like '% old' then -- tables that can be deleted by Restricted Users
Raise_application_error (-20010, 'You cannot create/delete any table under the temp _ % USER .');
End if;
End trg_deny_drop_ubisp;

Note: To restrict the drop operation on all objects for the current user, you can remove the attributes of ora_dict_obj_type and ora_dict_obj_name in the trigger.