Re: ORA-01031: insufficient privileges

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 12 Nov 1999 17:03:56 +0800
Message-ID: <382BD7FC.72B2@yahoo.com>

Nandakumar 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??
>
> 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.
> >
>
> --
> Nandakumar
> (N.Kumar_at_rocketmail.com)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

It is relatively simple to create a procedure to perform this task..

create or replace
procedure USERB.do_grant ( the_grant varchar2) is begin
  (dynamic sql to issue "the_grant" dependent on version) end;

Then as SYSTEM, simply

exec USERB.do_grant('grant select on table to USERA');

and then drop the proc (if you want)

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Fri Nov 12 1999 - 03:03:56 CST