{"id":1516,"date":"2015-04-27T05:40:06","date_gmt":"2015-04-27T10:40:06","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=1516"},"modified":"2026-02-07T14:58:53","modified_gmt":"2026-02-07T20:58:53","slug":"preventing-create-only-for-a-user","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2015\/1516\/","title":{"rendered":"Preventing CREATE (only) for a User"},"content":{"rendered":"

The PL\/SQL reference guide has a short section and example for the INSTEAD OF CREATE ON SCHEMA<\/a>\u00a0trigger.\u00a0Since 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\u00a0object.<\/p>\n

The code below shows a simple trigger that raises an error when\u00a0any\u00a0CREATE statement is used by the user.<\/p>\n

SQL> show user\nUser is BTHOMAS\n\nSQL> create or replace trigger prevent_new_objects\n  2   instead of create on schema\n  3   begin\n  4    raise_application_error(-20001, 'No new objects allowed in schema');\n  5   end;\nSQL> \/\n\nTrigger created.\n\nSQL> create table x1 (n1 number);\ncreate table x1 (n1 number)\n*\nERROR at line 1:\nORA-00604: error occurred at recursive SQL level 1\nORA-20001: No new objects allowed in schema\nORA-06512: at line 2\n\nSQL><\/pre>\n
If I connect as a different user, I can create table under BTHOMAS.<\/p>\n
SQL> connect system\/*\nConnected.\nSQL> create table bthomas.x1 (n1 number);\n\nTable created.\n\nSQL><\/pre>\n
The restriction is honored even if I use EXECUTE IMMEDIATE to create the object from a PL\/SQL unit.<\/p>\n
SQL> begin\n  2  execute immediate 'create table x1 (n1 number)';\n  3  end;\n  4  \/\nbegin\n*\nERROR at line 1:\nORA-20001: No new objects allowed in schema\nORA-06512: at line 2\nORA-06512: at line 2\n\nSQL><\/pre>\n
Things are not different even if I create the trigger under another user (you cannot create this type of trigger under SYS user!). \u00a0In the code below, SYSTEM user creates the trigger owned by BTHOMAS, but still SYSTEM user cannot create any object. BTHOMAS user is unaffected.<\/p>\n
SQL> connect system\/*\nConnected.\nSQL> create or replace trigger bthomas.prevent_new_objects\n  2   instead of create on schema\n  3   begin\n  4    raise_application_error(-20001, 'No new objects allowed in schema');\n  5*  end;\nSQL> \/\n\nTrigger created.\n\nSQL> create table x1 (x number);\ncreate table x1 (x number)\n*\nERROR at line 1:\nORA-00604: error occurred at recursive SQL level 1\nORA-20001: No new objects allowed in schema\nORA-06512: at line 2\n\n\nSQL> create table bthomas.x1 (n number);\ncreate table bthomas.x1 (n number)\n*\nERROR at line 1:\nORA-00604: error occurred at recursive SQL level 1\nORA-20001: No new objects allowed in schema\nORA-06512: at line 2\n\nSQL> show user\nUSER is \"SYSTEM\"\nSQL> connect bthomas\/*\nConnected.\nSQL> create table bthomas.x1 (n number);\n\nTable created.\n\nSQL>\n<\/pre>\n
Querying DBA_TRIGGERS will clarify this…<\/p>\n
SQL> select OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,\n  2  TABLE_OWNER, BASE_OBJECT_TYPE\n  3  from dba_triggers\n  4  where trigger_name = 'PREVENT_NEW_OBJECTS';\n\nOWNER                          TRIGGER_NAME                   TRIGGER_TYPE\n------------------------------ ------------------------------ ----------------\nTRIGGERING_EVENT\n--------------------------------------------------------------------------------\nTABLE_OWNER                    BASE_OBJECT_TYPE\n------------------------------ ----------------\nBTHOMAS                        PREVENT_NEW_OBJECTS            UNDEFINED\nCREATE\nSYSTEM                         SCHEMA\n\n\nSQL><\/pre>\n
The user BTHOMAS has CREATE TABLE privilege, thus he gets drop table and alter table privileges which are unaffected by this trigger.<\/p>\n
SQL> Show USER\nUser is SYSTEM\nSQL> create table d2 (n number);\ncreate table d2 (n number)\n*\nERROR at line 1:\nORA-00604: error occurred at recursive SQL level 1\nORA-20001: No new objects allowed in schema\nORA-06512: at line 2\n\nSQL> drop table d1;\n\nTable dropped.\n\nSQL><\/pre>\n
Finally example below shows, any CREATE statement is affected, not just CREATE TABLE.<\/p>\n
SQL> create synonym x for y;\ncreate synonym x for y\n*\nERROR at line 1:\nORA-00604: error occurred at recursive SQL level 1\nORA-20001: No new objects allowed in schema\nORA-06512: at line 2\n\n\nSQL><\/pre>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
The PL\/SQL reference guide has a short section and example for the INSTEAD OF CREATE ON SCHEMA\u00a0trigger.\u00a0Since 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 … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133,22],"tags":[63],"class_list":["post-1516","post","type-post","status-publish","format-standard","hentry","category-oracledb","category-scripts-tools","tag-security"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=1516"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1516\/revisions"}],"predecessor-version":[{"id":2954,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/1516\/revisions\/2954"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=1516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=1516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=1516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}