Creator of a role gets what rights?

#1

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Thanks

#2

In article <1s1gfv4m2b10uup90chchm8q29u5c1a2ed (AT) 4ax (DOT) com>, Peter <peter (AT) nomorenewsspammin (DOT) ca> wrote:

Quote:

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Thanks

The role is granted with admin option to the creator automatically, but if
revoked later, the creator has no special privilege over the role.

#3

In order for a user to create a role, they need the CREATE ROLE
privilege. In order for a user to grant a role they did not create to
another, they need the GRANT ANY ROLE privilege. This can be verified
with a simple test:

ORA9I SQL> create user testuser identified by pass123;

User created.

ORA9I SQL> grant create session to testuser;

Grant succeeded.

ORA9I SQL> grant create role to testuser;

Grant succeeded.

ORA9I SQL> connect testuser/pass123
Connected.
ORA9I SQL> create role test_role;

Role created.

ORA9I SQL> grant test_role to peasland;

Grant succeeded.

ORA9I SQL> grant connect to peasland;
grant connect to peasland
*
ERROR at line 1:
ORA-01919: role 'CONNECT' does not exist

HTH,
Brian

--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

#4

But a role is neither a system or object privilege. It is a role.
Whether you can grant that role is determined by whether or not you are
the creator or have been given the GRANT ANY ROLE system privilege. And
you can't grant a role with the ADMIN or GRANT option, so to speak.

HTH,
Brian

Hans Forbrich wrote:

Quote:

Peter wrote:

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Peter,

When looking for explanations of syntax, I recommend you start in the
SQL Reference manual.

In this case, the GRANT command is relevant. Details are available in
http://otn.oracle.com/docs/products/...2a.htm#2062195

According to that, should your user need to pass on the capability being
granted:
- IF this command is a SYSTEM privilege, then you use the WITH ADMIN
OPTION but
- IF this is an OBJECT privilege, then you use the WITH GRANT OPTION

If you can not determine whether the capability is an object or a system
priivilege, then (according to the document) you can look at table 17-1
on the same section.

To the observant, an easy way to determine whether this is a SYSTEM or
and OBJECT privilege is to look for the keyword "ON" followed by an
object identifier - if that exists it is because you are granting the
capability on an OBJECT to a user.

[3 .... 4 .... 5]
/Hans

--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

#5

Brian Peasland wrote:

Quote:

But a role is neither a system or object privilege. It is a role.
Whether you can grant that role is determined by whether or not you are
the creator or have been given the GRANT ANY ROLE system privilege. And
you can't grant a role with the ADMIN or GRANT option, so to speak.

HTH,
Brian

Et tu Brutus?

From the examples in the "Oracle9i SQL Reference, Release 2 (9.2)", section GRANT, examples at

http://otn.oracle.com/docs/products/...2a.htm#2081423

Quote:

Granting a Role with the Admin Option: Example

To grant the dw_manager role with the ADMIN OPTION to the sample user sh, issue the following statement:

GRANT dw_manager
TO sh
WITH ADMIN OPTION;

User sh can now perform the following operations with the dw_manager role:

Enable the role and exercise any privileges in the role's privilege domain, including the CREATE MATERIALIZED VIEW
system privilege
Grant and revoke the role to and from other users
Drop the role
<<<<<<<<

note the materiaized view part is because role dw_manager has those capabilities in an earlier part of the example.

#6

On Tue, 24 Jun 2003 08:12:18 GMT, Peter <peter (AT) nomorenewsspammin (DOT) ca>
wrote:

Quote:

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Thanks

It is about time everyone in this group stops going over every
individual line from the manual Peter posts here. Everyone is just
wasting his time and Peter is better served by subscribing to a
course.
If we don't stop it now, he will continue until he has posted *every
single individual* line of the manual here.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

#7

On Tue, 24 Jun 2003 15:19:18 GMT, Hans Forbrich
<forbrich (AT) telusplanet (DOT) net> wrote:

I am just talking about what rights the creator of a ROLE will get
automatically, not sys vs object privileges. If you create a role of
course you can grant it to someone. Another way of putting this
question is this:

When you grant a role that you own to another person, can you include
"with admin option" or the "with grant option" in the statement?

The difference that this will make is, of course, when the role is
revoked, will there may be cascading effects. "with admin option" will
not cause any cascading effects.

Thanks a lot

Quote:

Peter wrote:

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Peter,

When looking for explanations of syntax, I recommend you start in the
SQL Reference manual.

In this case, the GRANT command is relevant. Details are available in
http://otn.oracle.com/docs/products/...2a.htm#2062195

According to that, should your user need to pass on the capability being
granted:
- IF this command is a SYSTEM privilege, then you use the WITH ADMIN
OPTION but
- IF this is an OBJECT privilege, then you use the WITH GRANT OPTION

If you can not determine whether the capability is an object or a system
priivilege, then (according to the document) you can look at table 17-1
on the same section.

To the observant, an easy way to determine whether this is a SYSTEM or
and OBJECT privilege is to look for the keyword "ON" followed by an
object identifier - if that exists it is because you are granting the
capability on an OBJECT to a user.

[3 .... 4 .... 5]
/Hans

#8

On Tue, 24 Jun 2003 15:39:28 GMT, Brian Peasland
<oracle_dba (AT) remove_spam (DOT) peasland.com> wrote:

Quote:

But a role is neither a system or object privilege. It is a role.
Whether you can grant that role is determined by whether or not you are
the creator or have been given the GRANT ANY ROLE system privilege. And
you can't grant a role with the ADMIN or GRANT option, so to speak.

HTH,
Brian

But why can't you grant a role to the next person if you got the role
granted to you with the "with admin option". When this role is revoked
at the top of the chain, there will not be cascading effects.

Thanks.

Quote:

Hans Forbrich wrote:

Peter wrote:

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Peter,

When looking for explanations of syntax, I recommend you start in the
SQL Reference manual.

In this case, the GRANT command is relevant. Details are available in
http://otn.oracle.com/docs/products/...2a.htm#2062195

According to that, should your user need to pass on the capability being
granted:
- IF this command is a SYSTEM privilege, then you use the WITH ADMIN
OPTION but
- IF this is an OBJECT privilege, then you use the WITH GRANT OPTION

If you can not determine whether the capability is an object or a system
priivilege, then (according to the document) you can look at table 17-1
on the same section.

To the observant, an easy way to determine whether this is a SYSTEM or
and OBJECT privilege is to look for the keyword "ON" followed by an
object identifier - if that exists it is because you are granting the
capability on an OBJECT to a user.

[3 .... 4 .... 5]
/Hans

#9

Why don't you test it for us and post the answers? This type of question
can easily be answered with a quick and simple test case, similar to the
one that I already posted. Create a role. Try to grant it with the ADMIN
option or with the grant OPTION. Do you get an error? If allowed, test
to see if there are cascading effects.

[soapbox]
I see questions like this a lot where it is quite easy for a person to
test themselves and figure out the answers. It's very easy for you to
do. So please do it! In the end, you will be building a nice foundation
of testing and developing test cases. These type of building blocks will
go a long way toward advancing your DBA career when you need to develop
more complicated test cases.

For instance, if I need to determine the optimal stripe width for RAID
serving spatial data using Oracle and ESRI's ArcSDE, who am I going to
ask? About the best answer I'll get is "it all depends", because that's
the true answer. It all depends. It depends on my particular
configuration. So I have to develop test cases and determine what is
optimal for my specific environment. Unless someone has done the same
exact tests with a closely matching configuration, they won't be able to
definitively answer my question.

So please do yourself a favor and start to create test cases to answer
your questions. This is the next step a DBA should be taking after RTFM.
Want to know if a user can accidentally create a table in 9i's new UNDO
tablespace? Try it out and see! Want to know what happens to your
archived redo logs on your standby when the standby database's power
goes out? Unplug the standby database server! Want to know if there are
cascading effects on granting roles that you've created? Set up a test
case and see!
[/soapbox]
I'm tired now. I think I'll grab a Coke, have a smile, and shut the hell
up............

Cheers,
Brian

Peter wrote:

Quote:

On Tue, 24 Jun 2003 15:19:18 GMT, Hans Forbrich
forbrich (AT) telusplanet (DOT) net> wrote:

I am just talking about what rights the creator of a ROLE will get
automatically, not sys vs object privileges. If you create a role of
course you can grant it to someone. Another way of putting this
question is this:

When you grant a role that you own to another person, can you include
"with admin option" or the "with grant option" in the statement?

The difference that this will make is, of course, when the role is
revoked, will there may be cascading effects. "with admin option" will
not cause any cascading effects.

Thanks a lot

Peter wrote:

Does the creator of a ROLE get
"with admin option" privilege or the "with grant option" priviilege?

Peter,

When looking for explanations of syntax, I recommend you start in the
SQL Reference manual.

In this case, the GRANT command is relevant. Details are available in
http://otn.oracle.com/docs/products/...2a.htm#2062195

According to that, should your user need to pass on the capability being
granted:
- IF this command is a SYSTEM privilege, then you use the WITH ADMIN
OPTION but
- IF this is an OBJECT privilege, then you use the WITH GRANT OPTION

If you can not determine whether the capability is an object or a system
priivilege, then (according to the document) you can look at table 17-1
on the same section.

To the observant, an easy way to determine whether this is a SYSTEM or
and OBJECT privilege is to look for the keyword "ON" followed by an
object identifier - if that exists it is because you are granting the
capability on an OBJECT to a user.

[3 .... 4 .... 5]
/Hans

--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

#10

Peter wrote:

Quote:

On Tue, 24 Jun 2003 15:19:18 GMT, Hans Forbrich
forbrich (AT) telusplanet (DOT) net> wrote:

I am just talking about what rights the creator of a ROLE will get
automatically, not sys vs object privileges. If you create a role of
course you can grant it to someone. Another way of putting this
question is this:

When you grant a role that you own to another person, can you include
"with admin option" or the "with grant option" in the statement?

The difference that this will make is, of course, when the role is
revoked, will there may be cascading effects. "with admin option" will
not cause any cascading effects.

Thanks a lot

Didn't bother looking at the reference I gave, did you? Just bashed out another question immediately.

THE ANSWER IS IN THE DOCUMENTATION RIGHT WHERE I INDICATED. CLICK ON THE LINK AND READ! EXAMPLES AND ALL.

Sybrand is right. I give up.