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 :
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 :
SQL> insert into t1 values(101);
insert into t1 values(101)
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'BPM_DATA'