This content has been marked as final. Show 11 replies
$phinx19 wrote:Because RESOURCE is a role, not a privilege ....
DB:220.127.116.11 xpress edition
Here is the thing:
I have created a user using the below command:
create user xyz identified by xyz default tablespace users temporary tablespace temp;Now, I just grant him Connect Role and the privilege Create table as:
grant connect, create table to xyz;You must have noticed that I have not allocated the said user with any quota whatsoever.
Now, the above command will allow the user xyz to create a session and also allow him to create a table but I am getting the below error.
SQL> create table mandy (sr_no number, fname varchar2(10));Now when i query, the role Resource using:
create table mandy (sr_no number, fname varchar2(10))
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
WHERE grantee = 'RESOURCE';
and I just to test, I gave all the privileges one by one and see if it allows to create a table (which is a stupid move,as from the privileges one can at prima facie that it wont help the cause).
And it didn't!
When I simply fire:
grant resource to xyz;Oracle allows me to create the said table.
It means that it also have the unlimited table priv in resource, Just need to know, why the same is NOT reflected in the output of the dba_sys_privs query?
And it's not one you should give out lightly. Better to have granted said user some quota on said tablespace.
Edited by: EdStevens on Apr 25, 2013 3:46 PM
I meant, the output of the query:
WHERE grantee = 'RESOURCE';
This query should give me all the privileges within the role Resource (which should also include the unlimited quota on tablespace) or is there some other query.
Hope it is clear this time around
Granting the RESOURCE role includes an implicit grant of the UNLIMITED TABLESPACE privilege, you'll see it in dba_sys_privs. This is why you should never grant RESOURCE. The DBA role does the same thing.
I didnt see the Unlimited tablespace privilege in resource role. Please clarify
You didn't read what I said (or say "thank you"): granting the RESOURCE role includes an implicit grant of UNLIMITED TABLESPACE which you see if you query dba_sys_privs:
orcl> orcl> create user jw identified by jw; User created. orcl> grant resource to jw; Grant succeeded. orcl> select * from dba_sys_privs where grantee='JW'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- JW UNLIMITED TABLESPACE NO orcl>
good to know new thing, thanks John
So if no default tablespace has mentioned for user and we are granting resource role, will the unlimited tablespace be on SYSTEM by default?
That would be the default tablespace of database.
You can check the default tablespace from database_properties view.
To check database properties, for example: default tablespace type, various nls parameters, timezone etc run the following query:
SELECT * FROM DATABASE_PROPERTIES;
Thank you Bala.
what bala said is correct , we ll get to know the default tablespace by giving above qeury,
As per my concern For any user the default tablespace will be USERS.
No users should be given Qouta on SYSTEM tablespace;)