Re: HELP with grants

Thanks Thomas:

    At least I know I'm not going nuts. I've been getting exactly the same results you got. I guess I thought that if you had the "grant any privilege" system privilege, it should give you permission on objects as well. I'm going to try your procedure method. Since I'm the one granting all the system privileges, I've got "execute any procedure" and all the others.

Van

Thomas Kyte wrote in message <36ab480d.15333258_at_192.86.155.100>...
>A copy of this was sent to "Van Messner" <vmessner_at_netaxis.com>
>(if that email address didn't require changing)
>On Sat, 23 Jan 1999 16:21:09 -0500, you wrote:
>
>>Hello Thomas
>> I must be misunderstanding something. This is a quote from the Oracle
>>documentation "Once an object in the Oracle database has been created, it
>>can be administered by either the creator of the table or by a user who
has
>>GRANT ANY PRIVILEGE available to them. Administration of a database
object
>>consists of granting privileges that will allow users to manipulate the
>>object by adding, changing, removing, or viewing data in the database
>>object."
>
>can you let me know which document/chapter/section has that -- i'll file a
bug
>against it.
>
>Upon further research I see there is a "grant grant any privilege to USER"
>command (just never used it, learn something new every day...) . It
applies
>only to system priveleges. The sql lang manual has 2 grant sections:
>
>----------------------------------------------------------------
>GRANT (system privs)...
>
>prerequisites:
>To grant a system privilege, you must either have been granted the system
>privilege with the ADMIN OPTION or have been granted GRANT ANY
>PRIVILEGE system privilege.
>
>To grant a role, you must either have been granted the role with the ADMIN
>OPTION or have been granted GRANT ANY ROLE system privilege, or you must
>have created the role.
>
>
>GRANT (object privs)...
>
>prerequisites:
>You must own the object or the owner of the object must have granted you
the
>object privileges with the GRANT OPTION. This rule applies to users with
the
>DBA role.
>---------------------------------------------------------
>
>
>Just to see that that is the case, I did the following:
>
>SQL> create user test identified by test;
>User created.
>
>SQL> grant grant any privilege to test;
>Grant succeeded.
>
>SQL> grant create session to test;
>Grant succeeded.
>
>SQL> connect test/test
>Connected.
>
>SQL> grant alter user to tkyte;
>Grant succeeded.
>
>SQL> grant select on scott.emp to tkyte;
>grant select on scott.emp to tkyte
> *
>ERROR at line 1:
>ORA-01031: insufficient privileges
>
>
>so, test can grant any SYSTEM privilege but not access to others objects...
>
>if you have create any procedure and execute any procedure, you can grant
on
>others objects using the pl/sql workaround with dbms_sql...
>
>sorry for the mis-information on the grant any privilege thing -- i typed
in
>privelege not privilege (always spell that word wrong) when looking for it
and
>didn't see it at first.
>
>> In enterprise manager I can see that both SYS and Tom have this system
>>privilege. In Tom's case it was granted first through a role, then
>>explicitly by SYS.
>> Neither Tom nor SYS can grant update etc on the objects owned by
another
>>user.
>>Thanks for the other info,
>>
>>Van
>>
>
>[snip]
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Service Industries
>Reston, VA USA
>
>--
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
-
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
>Anti-Anti Spam Msg: if you want an answer emailed to you,
>you have to make it easy to get email to you. Any bounced
>email will be treated the same way i treat SPAM-- I delete it.
Received on Sun Jan 24 1999 - 08:16:43 CST