Re: Privileges Issue

Hi Guys,

Thanks to everyone who's replied so quickly. It's looking like if I was using 9i or beyond, I'd have no problem as the GRANT ANY OBJECT privilege would sort me out.

However, as I'm using 8i I'll have to create a procedure in the owner schema to issue the necessary grants, as these need to take place as part of a version release package.

Thanks again for all your help. I thought it might just be me missing something obvious, but it turns out I was trying to do something not possible in 8i.

Cheers.

James
Matthias Hoys wrote:
> "Jimbo1" <jamestheboarder_at_googlemail.com> wrote in message
> news:1152890107.254065.84660_at_p79g2000cwp.googlegroups.com...
> > Hello there,
> >
> > I'm currently experiencing some fun and games with System Privileges in
> > Oracle 8.1.7 (An upgrade to 10gR2 is taking place in January 2007, but
> > that's not relevant to this question. It's just a pre-emptive answer to
> > any 'helpful' individuals who advise an upgrade).
> >
> > In a nutshell, I'm trying to grant the SELECT privilege on a table in
> > another schema to a another seperate schema. Sounds simple eh? The
> > schema I'm connecting to in order to do this has been granted the DBA
> > role, so this should not be a problem.
> >
> > Anyway, let's say my schema with the DBA role is called Schema 1.
> >
> > I'm connecting to Schema 1 and running the following statement:
> >
> > GRANT SELECT ON Schema2.tableA TO Schema3;
> >
> > I'm getting the following exception:
> >
> > ERROR at line 1:
> > ORA-01031: insufficient privileges
> >
> > The DBA role has the GRANT ANY PRIVILEGE privilege.
> >
> > I am totally confused. Does anybody know what I'm missing here?
> >
> > Thanks in advance.
> >
> > James
> >
>
> I'm not sure but I don't think you can do with 8i, even when you have the
> DBA role. You have to log in with user2 and GRANT SELECT on any object to
> user3.
>
> connect user2/pwd;
> grant select on tableA to user3;
>
> Matthias
Received on Fri Jul 14 2006 - 10:51:39 CDT