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 which will prevent HR user to drop EMPLOYEES table:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE OR REPLACE TRIGGER trigger_prevent_drop BEFORE DROP ON DATABASE BEGIN IF ora_dict_obj_type = 'TABLE' AND ora_dict_obj_owner = 'HR' AND ora_login_user = 'HR' AND ora_dict_obj_name='EMPLOYEES' THEN raise_application_error (-20000, 'YOU CAN NOT DROP EMPLOYEES TABLE!'); END IF; END; |
When HR user tries to drop employees table, he’ll see an ORA-20000 error.
For more information and samples about DDL triggers: http://psoug.org/reference/ddl_trigger.html