If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

Hi all !
- When I look in the dba_sys_privs for the RESOURCE system privileges, I have :
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
- When I grant RESOURCE to a user and look in the dba_sys_privs for this user I have :
UNLIMITED TABLESPACE

Where does this privilege comes from ? It is not included in the RESOURCE role, so how can it be possible ?

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".

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

The system privilege 'UNLIMITED TABLESPACE' cannot be granted to a role.
However, the predefined 'RESOURCE' role contains the 'UNLIMITED TABLESPACE'
privilege.
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.