Oracle 11g quotas

Bonjour,
I have an instance of Oracle 11g created manually with only data dictionnary creation scripts executed.

When I create a user the later seems to have unlimited tablespace everywhere even though I did not gave any quota on any tablespace.

The user only have granted the "create session" and "create table" privileges.

If I try to revoke "unlimited tablespace" I get a message stating that the user does not have this privilege.

What do I miss to enforce quota limitation on this instance cause clearly it is not?

Thanks for your insights.

Regards.

Just found out that the user CAN create the table but CAN NOT add data to the table!!!!!!

Wonder what is the point in letting users create tables wherever they want even though they have no quota....!?

Hello everyone,
I hope I MAY ask these kind of questions here and that I won't be sent to Oracle University and still, hope no one is paid 650$/hour and don't have time to "spoon feed people" ... !

Sorry, I'm a bit angry and this has nothing to do with you guys....!

Well, my question is, how can I prevent a user from creating tables outside of tablespace where they have quotas. Even though the God of Oracle, the Most Honorable M. Burleson states here :

Quote:

---

Quotas

When a user has the CREATE TABLE privilege, the user can’t just create the table in any tablespace. He or she must have explicit privileges to create any type of stored object in a tablespace. This privilege is not given in a regular privilege granting statement, rather through a separate ALTER USER command as follows.

ALTER USER JUDY QUOTA 10K ON USER_DATA;

---

This is flawed unless I miss something (and yes, I read the links!!!)<- still my anger!

As soon as I give the "CREATE TABLE" privilege to a user, this one can go on and create tables wherever he wants to even on tablespace where he does not have any quota.

I know, quota and create table are DIFFERRENT but shouldn't they be related to each other?

As stated previously, the user as only two system privileges, CREATE SESSION and CREATE TABLE. He also have quota on tablespace USERS and nowhere else. Still that same user can create tables outside the USERS tablespace but can't insert into it without getting the following message :

Code:

---

SQL> insert into t1 values(101);
insert into t1 values(101)
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'BPM_DATA'

---

Anyone has any insight on that?

thanks,
regards,

William.

Oracle 10g and Oracle 11g don't seem to have the same behavior concerning the create table privilege.

Oracle 10g don't let the user create tables if they don't specifically have quota on the tablespace where they are trying to create the table.

Oracle 11g, so far I've tested two different instace, one created manually and the other using DBCA, and the create table privilege let create tables anywhere the user want to, except SYSYEM.

Hello Community,
hope this will help anyone of you asking the same question as I did.

Instead of trying to explain here why Oracle 11g behave as it does with regard to CREATE TABLE privilege and QUOTA, I will point out the link to where the answer is :

http://tkyte.blogspot.com/2011/02/de...-creation.html

Thanks