CODEWITHSUNDEEP
oracle-databaseoracle11g
Joseph
Joseph

Reputation: 97

Is "Create Table" or "Create Any Table" Privilages grant the user a quota implicitly?

I am working on our database users privileges, and I am facing a confusing issue. I first created a user (user1) without any quota and grant him the role CONNECT which have the system privilege (Create Session). After that I grant him the (Create Table) privilege. The user try to create a table and he succeeded to create a table in his tablespace!! my questions are:

  1. Based on my understanding, the user must have a quota to create any objects, how this user created the table?

  2. Is there any privilege or roles that give the user a quota implicitly?

Regards,

Upvotes: 2

Views: 2931

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

A quota isn't being granted implicitly. But creating a table doesn't necessarily require any storage, and so doesn't necessarily require a quota:

create table t42 (id number);

Table T42 created.

select segment_type, bytes from user_segments where segment_name = 'T42';

no rows selected

Adding data to the table does require storage, and therefore a quota:

insert into t42 (id) values (1);

1 row inserted.

select segment_type, bytes from user_segments where segment_name = 'T42';

SEGMENT_TYPE            BYTES
------------------ ----------
TABLE                   65536

If the owner doesn't have a quota on the tablespace then they will get an error when they try to insert; which is the case when they do have a quota and try to exceed it too of course (though the error will be different).

This behaviour is due to deferred segment creation; the default behaviour is controlled by an initialisation parameter. You can override that during table creation with the segment creation clause.

drop table t42 purge;

Table T42 dropped.

create table t42 (id number) segment creation immediate;

Table T42 created.

select segment_type, bytes from user_segments where segment_name = 'T42';

SEGMENT_TYPE            BYTES
------------------ ----------
TABLE                   65536

Read more in the documentation.

Incidentally, if you create a table with deferred segment creation then dbms_metadata.get_ddl shows that; if you then insert a row to force a segment to be created, dbms_metadata.get_ddl changes to SEGMENT CREATION IMMEDIATE. Which might not be expected. Truncating the table with the DROP ALL STORAGE clause will remove the segments, and revert the DDL to SEGMENT CREATION DEFERRED. Just something I noticed in passing.

Upvotes: 2

Related Questions