SEO

The following is quoted from the 10gR2 Beta document.
================================================== =====================
The connect role privilege reduction feature reduces the number of
privileges granted to the connect role to one, the CREATE SESSION
privilege. The privileges have been removed from the connect role:

- CREATE CLUSTER
- CREATE DATABASE LINK
- CREATE SEQUENCE
- ALTER SESSION
- CREATE SYNONYM
- CREATE TABLE
- CREATE VIEW

This feature assists customers in deploying secure configurations by
helping enforce the least privilege principle.
================================================== =====================

This change may or may not be related to the comments here, and
elsewhere, with respect to the dangers related to creating users and
giving them the CONNECT role. But it makes me very happy and I have
received permission to post it here at c.d.o.server.

So be warned ... if you have been using CONNECT as the lazyman's way
of creating users with permission to connect to the database ... it
will not work the same way in the future unless you intentionally
modify the role. Hopefully no one will but rather will create their
own custom roles that reflect job titles and responsibilities.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan wrote:
> The following is quoted from the 10gR2 Beta document.
> ================================================== =====================
> The connect role privilege reduction feature reduces the number of
> privileges granted to the connect role to one, the CREATE SESSION
> privilege. The privileges have been removed from the connect role:
>
> - CREATE CLUSTER
> - CREATE DATABASE LINK
> - CREATE SEQUENCE
> - ALTER SESSION
> - CREATE SYNONYM
> - CREATE TABLE
> - CREATE VIEW
>
> This feature assists customers in deploying secure configurations by
> helping enforce the least privilege principle.
> ================================================== =====================
>
> This change may or may not be related to the comments here, and
> elsewhere, with respect to the dangers related to creating users and
> giving them the CONNECT role. But it makes me very happy and I have
> received permission to post it here at c.d.o.server.
>
> So be warned ... if you have been using CONNECT as the lazyman's way
> of creating users with permission to connect to the database ... it
> will not work the same way in the future unless you intentionally
> modify the role. Hopefully no one will but rather will create their
> own custom roles that reflect job titles and responsibilities.

Well finally. Developers will have to learn what roles are about. Are there
any changes to resource as well or are the removed priveleges silently added
to the resource role?

You know, people insist in grant connect, resource to myuser, and the
Oracle Documentation sets some really bad examples (why the hell should
the RMAN catalog owner get resource and connect on top of recovery_catalog_owner, as the
10g RMAN Reference suggests?).

But still good to know.

Holger

Holger Baer wrote:
> DA Morgan wrote:
>
>> The following is quoted from the 10gR2 Beta document.
>> ================================================== =====================
>> The connect role privilege reduction feature reduces the number of
>> privileges granted to the connect role to one, the CREATE SESSION
>> privilege. The privileges have been removed from the connect role:
>>
>> - CREATE CLUSTER
>> - CREATE DATABASE LINK
>> - CREATE SEQUENCE
>> - ALTER SESSION
>> - CREATE SYNONYM
>> - CREATE TABLE
>> - CREATE VIEW
>>
>> This feature assists customers in deploying secure configurations by
>> helping enforce the least privilege principle.
>> ================================================== =====================
>>
>> This change may or may not be related to the comments here, and
>> elsewhere, with respect to the dangers related to creating users and
>> giving them the CONNECT role. But it makes me very happy and I have
>> received permission to post it here at c.d.o.server.
>>
>> So be warned ... if you have been using CONNECT as the lazyman's way
>> of creating users with permission to connect to the database ... it
>> will not work the same way in the future unless you intentionally
>> modify the role. Hopefully no one will but rather will create their
>> own custom roles that reflect job titles and responsibilities.
>
>
> Well finally. Developers will have to learn what roles are about. Are there
> any changes to resource as well or are the removed priveleges silently
> added
> to the resource role?
>
> You know, people insist in grant connect, resource to myuser, and the
> Oracle Documentation sets some really bad examples (why the hell should
> the RMAN catalog owner get resource and connect on top of
> recovery_catalog_owner, as the
> 10g RMAN Reference suggests?).
>
> But still good to know.
>
> Holger

I always granted them dba - sometimes I had to add
select any table, though.
Usually does gets the job done.

BWUHAHAHAHAH (smell the sulfur yet?)
--
Regards,
Frank van Bortel
The comments in this message should only be used by experienced users.
I can take no responsibility for accidents or damage caused by following
the above advice. I am not liable in any way.

Holger Baer wrote:

> Well finally.

My feeling exactly.

> Developers will have to learn what roles are about. Are there
> any changes to resource as well or are the removed priveleges silently
> added to the resource role?

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.

> You know, people insist in grant connect, resource to myuser, and the
> Oracle Documentation sets some really bad examples (why the hell should
> the RMAN catalog owner get resource and connect on top of
> recovery_catalog_owner, as the
> 10g RMAN Reference suggests?).
>
> But still good to know.
>
> Holger

I am hopeful that Sarbanes-Oxley, HIPAA, and the obvious threat of laws
and litigation related to data theft will lead Oracle to tighten up some
of the default install practices.

With 10g they finally got around to killing CHANGE_ON_INSTALL. I would
very much like to see these roles pounded into dust too. And then the
next item on my list will be a change so that when Oracle installs the
default will be resource_limit = TRUE and the default profile will
include the VERIFY_FUNCTION function as well as limitations on password
expiration, password reuse, etc.

Oracle is already more secure than its competition out of the box. That
does not mean best practices shouldn't be the default.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

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

http://asktom.oracle.com/pls/ask/f?p...:1063989617206

As I recall this was at the heart of a long and heated thread here
earlier this year... the only thing everyone agreed on was that it was a
major problem, much bigger than any associated with CONNECT.

-Mark Bole

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.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

In article <1117604758.138385@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@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@ORA10G> drop user a cascade;
User dropped.

ops$tkyte@ORA10G> create user a identified by a;
User created.

ops$tkyte@ORA10G> grant resource to a;
Grant succeeded.

ops$tkyte@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

Thomas Kyte wrote:
> In article <1117604758.138385@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@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@ORA10G> drop user a cascade;
> User dropped.
>
> ops$tkyte@ORA10G> create user a identified by a;
> User created.
>
> ops$tkyte@ORA10G> grant resource to a;
> Grant succeeded.
>
> ops$tkyte@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@x.washington.edu
(replace x with u to respond)