Mir Sayeed Hassan – Oracle Blog

Restrict a schema to drop a table from its own

SQL> sho user
USER is "SYS"

SQL> create user mir identified by mir;
User created.

SQL> grant connect,resource to mir;
Grant succeeded.

SQL> connect mir/mir;
Connected.

SQL>  create table test1(eno number(10));
Table created.

SQL>  insert into test1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST1

SQL> connect / as sysdba
Connected.

SQL> sho user
USER is "SYS"

SQL> create or replace trigger drop_mir_test1_trigger
before drop on mir.schema
begin
raise_application_error(
num => -20999,
msg => 'Objects cannot be dropped.');
end;
/
Trigger created.

Now the trigger is created, so let us try to drop the table from its owner schema mir

SQL> connect mir/mir;
Connected

SQL> drop table test1;

drop table test1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20999: Objects cannot be dropped.
ORA-06512: at line 2

You can observe that the above table we wanted to drop can’t be dropped by the schema owner (mir) because of the schema-level trigger.

SQL> connect / as sysdba
Connected.

SQL> alter trigger drop_mir_test1_trigger disable;
Trigger altered.

Or

SQL> drop trigger drop_mir_test1_trigger;
Trigger dropped.

or

We can drop the table by using the super privilege user sys or dba

SQL> connect mir/mir
Connected.

SQL> drop table test1;
Table dropped.

Hence we cannot drop the table in its own schema, if the trigger is created or enable in it, therefore disable or drop the trigger and drop the table.