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 this post Share on Facebook0Share on Google+0Share on LinkedIn0Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

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 Comment

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