Hi all !
- When I look in the dba_sys_privs for the RESOURCE system privileges, I have :
- When I grant RESOURCE to a user and look in the dba_sys_privs for this user I have :
Where does this privilege comes from ? It is not included in the RESOURCE role, so how can it be possible ?
Thanks a lot in advance ...
I think UNLIMITED TABLESPACE is more of quota to enable object creation than a privilge .
though it shows in Privilege columns.
No, UNLIMITED TABLESPACE is perfectly normal system privilege.
How it is granted with RESOURCE role, although it is not part of a RESOURCE role? Obviously there is some kind of "hidden trigger" built in oracle that grants/revokes it as an explicit privilege to/from user whenever he is granted/revoked RESOURCE role.
The whole process is not externalized, you can't see it how it is implemented anywhere - that's why I said it must be a "hidden trigger".
I found out that this behavoir is referenced as a bug for Oracle :
The system privilege 'UNLIMITED TABLESPACE' cannot be granted to a role.
However, the predefined 'RESOURCE' role contains the 'UNLIMITED TABLESPACE'
If you grant the 'RESOURCE' role to any other role, the 'UNLIMITED TABLESPACE'
privilege will not be transfered to this other role.
For example, if you grant the'RESOURCE' role to another role, i.e., the
accounting role, the user enabling the accounting role will lose the
'UNLIMITED TABLESPACE' privilege, and the user will get an error when trying
to create an object.
This was filed as bug 172360, which is closed as 'not a bug'.
This is a good reason to not use the resource role.