Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle 11g R2, CREATE TABLE and QUOTAS

WarluckMar 23 2011 — edited Nov 8 2012
Hello everyone,
I need some insight about a strange behavior I found out in Oracle 11gR2. Don't know if I do miss something or if I just found out a security issue with Oracle.

Oracle Version : 11.2.0.1.0

The problem is related with the CREATE TABLE privilege and the QUOTA on specific tablespace.

Please, try this on your systems (if you have some spare time and care to confirm the "bug").

Create a user, grant only two privileges, CREATE SESSION and CREATE TABLE.

Grant NO quota on any tablespace.

Try to create table on any tablespace (except SYSTEM) and tell everyone if it worked or not.

The oracle documentation states the following :

To create a relational table in your own schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege. Also, the owner of the schema to contain the table must have either space quota on the tablespace to contain the table or the UNLIMITED TABLESPACE system privilege.

[http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_7002.htm#SQLRF01402]

The fact is, so far, on two different instances of Oracle 11gR2, my users are not limited in creating tables only where they have quotas but wherever they want except SYSTEM.

The correct behavior would be to deny the table creation on tablespace where there is no quota but it does not.

My instance of Oracle 10g are behaving correctly and thus the table creation is denied on tablespace with no quota.

P.S1 Sorry if this a well known "bug/problem/issue". I've been ridicule on a well known forum for asking the same question. I am in no need to be "spoon filled" as stated on that famous website! I have read the documentation! I have googled a lot!

P.S2 Even though the table creation work on tablespace with no quota, you still can't insert data in it. So, big picture, the user can't filled the tablespace with irrelevant data but he can creates thousand of tables...!

Do I miss something?
Is there any "default" option I have to flag to prevent table creation where it should not?
?(?)
This post has been answered by Pierre Forstmann on Mar 23 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 6 2012
Added on Mar 23 2011
5 comments
1,965 views