How to Prevent a User to Drop Own Objects

An Oracle user is always granted to drop their own objects. To prevent a user to drop their own objects, we can use DDL triggers.

Here's sample trigger that will prevent MAHI user to drop EMP table:

connect as sys user

sql>CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = 'TABLE'
AND ora_dict_obj_owner = 'MAHI'
AND ora_login_user = 'MAHI'
AND ora_dict_obj_name='EMP'
THEN
raise_application_error (-20000, 'YOU CAN NOT DROP EMP TABLE!');
END IF;
END;
/

Trigger created.

then login as mahi user and try to drop emp table

SQL> conn mahi
Enter password:
Connected.
SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: YOU CAN NOT DROP EMPLOYEES TABLE!
ORA-06512: at line 7