Why Oracle User Can Create Tables in Unauthorized Tablespaces

Requirement Description

The task is to create a new user A with default tablespace TBS_1 in a shared Oracle database, while preventing this user from creating tables in another tablespace TBS_2 used by other schemas.

Step 1 – Create User A

create user a identified by a default tablespace tbs_1;
grant resource, connect to a;

This sets TBS_1 as the default tablespace and grants the RESOURCE and CONNECT roles.

Step 2 – Test Table Creation

SQL> create table t1(id number);
SQL> insert into t1 values(1);
SQL> commit;

The table t1 is created in TBS_1 without error.

Now test creation in TBS_2:

SQL> create table t2(id number) tablespace tbs_2;
SQL> insert into t2 values(1);
SQL> commit;

Creation succeeds because user A has the system privilege UNLIMITED TABLESPACE, granted via the RESOURCE role.

Step 3 – Revoke UNLIMITED TABLESPACE and Retest

revoke UNLIMITED TABLESPACE from a;

create table t1(id number);
insert into t1 values(1);
-- ORA-01950: no privileges on tablespace 'TBS_1'

create table t2(id number) tablespace tbs_2;
-- ORA-01950: no privileges on tablespace 'TBS_2'

After revoking, table creation still succeeds but any DML (insert) fails because the user no longer has quota on the tablespaces.

Step 4 – Grant Unlimited Quota on TBS_1 and Retest

alter user a quota unlimited on tbs_1;

create table t1(id number);
insert into t1 values(1);
commit;

create table t2(id number) tablespace tbs_2;
insert into t2 values(1);
-- ORA-01950: no privileges on tablespace 'TBS_2'

User A can now create and insert into tables in TBS_1, but still cannot insert into tables created in TBS_2.

Why the “Illusion” Occurs – Deferred Segment Creation

Oracle 11gR2 introduced the *deferred segment creation* feature. Physical storage for a table (or index, LOB) is not allocated at CREATE TABLE time; allocation is delayed until the first row is inserted. This makes it appear that a user can create a table in a tablespace without proper privileges, even though the subsequent insert will fail.

Version check:

SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Deferred segment creation is enabled by default. It can be disabled by setting the parameter deferred_segment_creation to FALSE.

Solution 1 – Global Setting

Execute once (as a privileged user):

alter system set deferred_segment_creation = FALSE scope = both;

This disables deferred segment creation for all users in the database.

Solution 2 – User‑Level Setting via Logon Trigger

If only user A should be restricted, create a logon trigger that disables the feature for that session:

create or replace trigger log_deferred
after logon on database
declare
  logon_user varchar2(10);
begin
  select user into logon_user from dual;
  if logon_user = 'A' then
    execute immediate 'alter session set deferred_segment_creation = FALSE';
  end if;
end;
/

When user A logs in, the session parameter is set to FALSE, preventing the creation of tables in unauthorized tablespaces.

Summary

Granting the RESOURCE role gives the UNLIMITED TABLESPACE privilege, which allows table creation in any tablespace.

Oracle 11gR2's deferred segment creation can make it look like a user has permission to create tables in a tablespace where they lack quota; the insert will fail.

Setting deferred_segment_creation = FALSE globally or via a logon trigger for specific users eliminates this illusion and enforces true tablespace restrictions.