Restrict DDL on a Schema

DBA wants that in the database certain user will not be able structural change. That is no DDL operation can be performed by the user. In that case DBA can achieve his goal simply by making a trigger on the schema.

Suppose we want user faruk will not be able to perform any DDL. Then create trigger as below.

SQL> conn shaik/abdul
Connected.

SQL> create table before_trigger(a number);
Table created.

SQL>conn system/manager
Connected.

SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_SHAIK
3 BEFORE DDL
4 ON SHAIK.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /
Trigger created.

SQL> conn shaik/abdul
Connected.

SQL> create table after_trigger(a number);
create table after_trigger(a number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -30900 is out of
range
ORA-06512: at line 2