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:

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

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

3 Comments

  1. d.vega

    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. Denni A

    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 to d.vega Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.