Wednesday, April 18, 2012

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.

No comments: