Create Any Table

This post looks at the Create Any Table privilege. It was tested on Oracle 9. First create a user called A with unlimited quota on tablespace TSA. This means that user A can take up all available space in tablespace TSA if he wants to. There is no particular reason for this. I just wanted to contrast it with the quota for user B, which is limited to 5 megabytes:

SQL> create user a identified by a

  2  quota unlimited on tsa

  3  /

 

User created.

SQL>

User A needs to be able to connect to the database and create tables:

 

SQL> grant create session, create table to a

  2  /

 

Grant succeeded.

SQL>

Now create a user called B. For the purposes of this example, he only needs some quota in a different tablespace:

 

SQL> create user b identified by b

  2  quota 5m on tsb

  3  /

 

User created.

SQL>

Connect to the database as user A and create a table in schema A in tablespace TSA:

 

SQL> conn a/a

Connected.

SQL> create table tab1 (col1 number)

  2  tablespace tsa

  3  /

 

Table created.

SQL>

Try to create a table belonging to user B. This fails:

 

SQL> create table b.tab2 (col1 number)

  2  tablespace tsa

  3  /

create table b.tab2 (col1 number)

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL>

User A needs the create any table privilege if he wants to create tables in other schemas. Grant this privilege and try it out. It still fails because although user A is creating the table, it belongs to user B and it is he who needs the quota in the tablespace:

 

SQL> conn / as sysdba

Connected.

SQL> grant create any table to a

  2  /

 

Grant succeeded.

 

SQL> conn a/a

Connected.

SQL> create table b.tab3 (col1 number)

  2  tablespace tsa

  3  /

create table b.tab3 (col1 number)

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'TSA'

SQL>

This time, user A creates the table in tablespace TSB. This works as user B owns the table AND has a quota in the tablespace: 

 

SQL> create table b.tab4 (col1 number)

  2  tablespace tsb

  3  /

 

Table created.

 

SQL>

With the create any table privilege, user A is now able to carry out a denial of service attack on user B. He can do this by creating a large table belonging to user B in tablespace TSB. This will use up all B's quota there and as soon as he runs any SQL which needs a new extent to be allocated, that SQL will fall over.