How do I stop the use of TRUNCATE

Posted by

Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state.  So often we’d like to bar the use of it.  Obviously, to truncate a table in another schema you need DROP ANY TABLE which one would hope very very very few people, aka none 🙂 would be granted, so really this is all about connections as the owner of a table.

Ideally, the solution is part of the application implementation, namely, that people never connect as the schema owner – that’s just good security practice for applications even without considering truncate. A common technique is to disable the ability to connect as the schema owner, so that all access must come from another schema which only has insert, update, delete, select access to the tables, or even better, the data access is entirely held behind a PL/SQL layer, and execute on those PL/SQL modules is all that is given.

But … out in the wild of the real world, the threat is often still present.  So you can use a trigger as a stopgap measure.

SQL> create or replace trigger ddl_trigger
  2  before truncate on scott.SCHEMA
  3  declare
  4      l_sysevent varchar2(25);
  5  begin
  6      select ora_sysevent into l_sysevent from dual;
  8      if ( l_sysevent in ('TRUNCATE') )
  9      then
 10          raise_application_error(-20000,'no no no no');
 11      end if;
 12  end;
 13  /

Trigger created.

SQL> truncate table t;
truncate table t
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: no no no no
ORA-06512: at line 8

One comment

  1. if ( l_sysevent in (‘TRUNCATE’) )

    Is this check necessary ? We have the trigger based on TRUNCATE only.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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