Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CONNECT Role Privileges

Re: CONNECT Role Privileges

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 01 Jun 2005 06:55:15 -0700
Message-ID: <1117633984.628049@yasure>


Thomas Kyte wrote:
> 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
Thanks Tom.
-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Jun 01 2005 - 08:55:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US