Prevent Truncate/Drop table from own schema

To prevent the truncation of tables in your own schema, write a trigger in your schema.

CREATE OR REPLACE TRIGGER PREVENT_TRUNCATE

BEFORE DROP OR TRUNCATE
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A Production Table Has Been Logged');
END save_our_db;
/

Now when you try to truncate a table in your own schema, you will receive the following errors:

ORA-00604: error occurred at recursive SQL level 1

ORA-20999:Attempt To Truncate A Production Table Has Been Logged

ORA-06512: At Line 2

Another way to prevent user from dropping objects from own schema is as below:

- Here we are not using any trigger
- This would prevent drop object for SQL*PLUS only
- We could configure it for other IDE tools like TOAD, SQLDEVELOPER etc

SQL> connect system/manager

SQL> insert into product_user_profile (product,userid,attribute,char_value) values('SQL*Plus','SCOTT,'DROP','DISABLED');
SQL> commit;

SQL> select * from product_user_profiles;

PRODUCT    USERID  ATTRIBUTE  CHAR_VALUE
---------  ------- ---------- ----------------
SQL*PLUS   SCOTT   DROP       DISABLED

If for example user SCOTT attempts to Drop an object, it will get this error:

SQL> drop table xyz;
SP2-0544: invalid command: drop.