Restrict DDL on a Schema

Suppose 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 faruk/faruk
Connected.

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

SQL>conn system/a
Connected.

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

SQL> conn faruk/faruk
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