How to restrict to DROP/ALTER a table using Triggers

You can restrict users to drop/alter any table within the schema using system triggers.

Connect as sysdba and create following trigger

C:\Users>sqlplus / as sysdba
SQL> CREATE OR REPLACE TRIGGER SCHMEA_TRIGGER
  2  BEFORE  DROP
  3  ON SCOTT.SCHEMA
  4  DECLARE
  5  evt VARCHAR2(1000);
  6  BEGIN
  7  SELECT ora_sysevent INTO evt FROM DUAL;
  8  IF evt = 'DROP' THEN
  9  RAISE_APPLICATION_ERROR(-20900,'UNABLE TO DROP TABLE');
 10  END IF;
 11  END;
 12  /
Trigger created.
SQL>

Connect as scott and try to drop EMP2 table and see the error.

C:\Users>sqlplus scott/tiger
SQL> show user
USER is "SCOTT"
SQL> SELECT COUNT(*) FROM EMP2;
  COUNT(*)
----------
        14
SQL> DROP TABLE EMP2;
DROP TABLE EMP2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20900: UNABLE TO DROP TABLE
ORA-06512: at line 6

SQL> SELECT COUNT(*) FROM EMP2;
  COUNT(*)
----------
        14
SQL>

-Asifkhan P.