Restrict DDL Operations on a Schema in Oracle Database

In order to ensure schema consistency, DBAs may want to streamline who can issue Data Definition Language commands. Certain users are not permitted to make structural changes to object definitions, which implies no DDL operations can be performed by certain group of users. In that case DBA can achieve his goal simply by making a trigger on the schema.

For instance we want the user Nathan not to be able to perform any DDL. Then create trigger as below:

SQL> conn nathan/n
Connected.

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

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

SQL> conn nathan/n
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