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.
