Re: CONNECT Role Privileges

In article <1117604758.138385_at_yasure>, DA Morgan says...
>
>Mark Bole wrote:
>> DA Morgan wrote:
>>
>>> Holger Baer wrote:
>>>
>> [...]
>>
>>> To the best of my knoweldge no change was made to RESOURCE although I
>>> made plea for that change in 10gR3 should there be one. And if not 10gR3
>>> in 11. The security risk created by these three default roles exceeds
>>> any possible value they might contain.
>>>
>> [...]
>>
>> Any idea when Oracle will fix the following problem?
>>
>> A user granted the RESOURCE role automatically gets the UNLIMITED
>> TABLESPACE system privilege. Roles technically can't be grantees for
>> system privileges, but this behavior is hard-coded (an "anomaly" is what
>> Tom Kyte called it).
>>
>> -Mark Bole
>
>In 10gR1 v 10.1.0.4
>
>select privilege from dba_sys_privs
>where grantee = 'RESOURCE';
>
>PRIVILEGE
>-----------------
>CREATE TYPE
>CREATE TABLE
>CREATE CLUSTER
>CREATE TRIGGER
>CREATE OPERATOR
>CREATE SEQUENCE
>CREATE INDEXTYPE
>CREATE PROCEDURE
>
>And that is all. Now you'd think CREATE VIEW would be more useful than
>CREATE INDEXTYPE. But basically these roles, in the real world, are
>badly misused and need to be stuck with a fork.
>
>At least the role's name should be changed to DEVELOPER to help
>discourage its misuse.

Resource and DBA are "special", their very names are special. UNLIMITED TABLESPACE is a privilege that cannot be granted to a role,

ops$tkyte_at_ORA10G> grant unlimited tablespace to connect; grant unlimited tablespace to connect
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role

and the RESOURCE and DBA roles were designed to have this privilege so the very act of granting RESOURCE or DBA to a user grants them UNLIMITED TABLESPACE directly:

ops$tkyte_at_ORA10G> drop user a cascade;
User dropped.  

ops$tkyte_at_ORA10G> create user a identified by a; User created.  

ops$tkyte_at_ORA10G> grant resource to a;
Grant succeeded.  

ops$tkyte_at_ORA10G> select * from dba_sys_privs where grantee = 'A';  

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
A                              UNLIMITED TABLESPACE                     NO
 

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation