Preventing CREATE (only) for a User

The PL/SQL reference guide has a short section and example for the INSTEAD OF CREATE ON SCHEMA trigger. Since the description was so little and could not find much detail on the internet, tried some examples as below. This feature is good to prevent any CREATE operation for an user, giving him/her privilege to alter or drop existing object.

The code below shows a simple trigger that raises an error when any CREATE statement is used by the user.

SQL> show user
User is BTHOMAS

SQL> create or replace trigger prevent_new_objects
  2   instead of create on schema
  3   begin
  4    raise_application_error(-20001, 'No new objects allowed in schema');
  5   end;
SQL> /

Trigger created.

SQL> create table x1 (n1 number);
create table x1 (n1 number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No new objects allowed in schema
ORA-06512: at line 2

SQL>

If I connect as a different user, I can create table under BTHOMAS.

SQL> connect system/*
Connected.
SQL> create table bthomas.x1 (n1 number);

Table created.

SQL>

The restriction is honored even if I use EXECUTE IMMEDIATE to create the object from a PL/SQL unit.

SQL> begin
  2  execute immediate 'create table x1 (n1 number)';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20001: No new objects allowed in schema
ORA-06512: at line 2
ORA-06512: at line 2

SQL>

Things are not different even if I create the trigger under another user (you cannot create this type of trigger under SYS user!). In the code below, SYSTEM user creates the trigger owned by BTHOMAS, but still SYSTEM user cannot create any object. BTHOMAS user is unaffected.

SQL> connect system/*
Connected.
SQL> create or replace trigger bthomas.prevent_new_objects
  2   instead of create on schema
  3   begin
  4    raise_application_error(-20001, 'No new objects allowed in schema');
  5*  end;
SQL> /

Trigger created.

SQL> create table x1 (x number);
create table x1 (x number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No new objects allowed in schema
ORA-06512: at line 2


SQL> create table bthomas.x1 (n number);
create table bthomas.x1 (n number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No new objects allowed in schema
ORA-06512: at line 2

SQL> show user
USER is "SYSTEM"
SQL> connect bthomas/*
Connected.
SQL> create table bthomas.x1 (n number);

Table created.

SQL>

Querying DBA_TRIGGERS will clarify this…

SQL> select OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,
  2  TABLE_OWNER, BASE_OBJECT_TYPE
  3  from dba_triggers
  4  where trigger_name = 'PREVENT_NEW_OBJECTS';

OWNER                          TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------
TABLE_OWNER                    BASE_OBJECT_TYPE
------------------------------ ----------------
BTHOMAS                        PREVENT_NEW_OBJECTS            UNDEFINED
CREATE
SYSTEM                         SCHEMA


SQL>

The user BTHOMAS has CREATE TABLE privilege, thus he gets drop table and alter table privileges which are unaffected by this trigger.

SQL> Show USER
User is SYSTEM
SQL> create table d2 (n number);
create table d2 (n number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No new objects allowed in schema
ORA-06512: at line 2

SQL> drop table d1;

Table dropped.

SQL>

Finally example below shows, any CREATE statement is affected, not just CREATE TABLE.

SQL> create synonym x for y;
create synonym x for y
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No new objects allowed in schema
ORA-06512: at line 2


SQL>

Related