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

2 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.

  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 *