Re: ORA-01031: insufficient privileges

A copy of this was sent to Nandakumar <N.Kumar_at_rocketmail.com> (if that email address didn't require changing) On Thu, 11 Nov 1999 20:22:20 GMT, you wrote:

>why's it like that?
>
>if system does not have privilege to grant some select on some table to
>some user ( non-owner), then when you create a role of select privileges
>on tables in different schemas, you would have to connect to each user
>and grant select privilege on appro. table, which i guess is not an easy
>way.
>moreover, you require the password of each user to connect.
>
>any other easier way??
>

SYS and SYSTEM are just accounts. SYS has some special attributes to it but it really is just yet another account, like SCOTT.

In order for USERA to grant a privelege on USERB's objects, USERB must have at some time granted USERA that privelege WITH GRANT OPTION. That will allow USERA to convey that grant to someone else. So, if userb "grant select on T to usera with grant option", then userA can not only SELECT from T but usera can grant select on T to others.

If you want, grant select on your objects to SYS or SYSTEM with the grant option, then sys or system can grant select on those objects to others.

>regards
>
>In article <80f4gk$840$1_at_nnrp1.deja.com>,
> Ben Ryan <benryan_at_my-deja.com> wrote:
>> In article <80f3i7$787$1_at_nnrp1.deja.com>,
>> Nandakumar <N.Kumar_at_rocketmail.com> wrote:
>> > when i (SYSTEM user) try to GRANT SELECT privilege ON TABLE_A TO
>user
>> > USER_B i get the error mesg.
>> >
>> > ERROR at line 1:
>> > ORA-01031: insufficient privileges.
>> >
>> > TABLE_A is owned by user USER_A.
>> >
>> > ... changing the log on to USER_A , the grant succeedes.
>> >
>> > i was under the assumption that SYSTEM can grant/revoke any
>privilege
>> to
>> > any user on any object. am i wrong?
>>
>> Yes. you are wrong.
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 11 1999 - 14:52:18 CST