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:

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

3 Responses to “How to Prevent a User to Drop Own Objects”

  1. Where is the point of this?

    If you want a user who can connect and use objects but not drop them, then just create a “read/execute-only” user and grant him the necessary object privileges.

    I think that’s why Oracle doesn’t implement that kind of feature, because you already have that control with grants.

    • Found this useful as DBA perspective, to prevent user from dropping any table which is important. This gave an example and helped me creating a trigger as SYS user to prevent one of our important table to be dropped accidentally by any user who have full access to this table.

  2. thank you, this was very useful. we have a user who likes to think they are dba and will go into the database and alter tables without notice.

    I used your above example so that critical tables could not be ALTERED and will now be delivered the handy application error-message.

     

Leave a Reply

Your email address will not be published. Required fields are marked *