Re: PL/SQL: how to SELECT from other user?

From: <maleform_at_my-deja.com>
Date: Wed, 03 Jan 2001 23:00:43 GMT
Message-ID: <930aun$upn$1@nnrp1.deja.com>

what if the permissions are controlled via roles and been granted?? This error still comes up. Stupid design.

In article <92tq8d$rnq$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rii.com> wrote:
> In article <92tp5o$qp9$1_at_nnrp1.deja.com>,
> susana73_at_hotmail.com wrote:
> > Hi,
> >
> > I have a stored procedure on user A. What it does is it inserts
 data
 by
> > selecting from user B. I granted the SELECT permission on user A to
> > select that particular table(ARCH) from user B.
> >
> > CREATE OR REPLACE PROCEDURE move_arch(
> > var_name IN VARCHAR2,
> > var_kind IN VARCHAR2
> > )
> > IS
> > BEGIN
> >
> > INSERT INTO archive (name, kind)
> > SELECT name, kind FROM USERB.arch
> > WHERE name = var_name AND kind = var_kind;
> > ......
> >
> > ##Error##
> > 10/5 PL/SQL: SQL Statement ignored
> > 11/44 PLS-00201: identifier 'USERB.ARCH' must be declared
> >
> > It didn't work after all because of the above error. Then I tried
> > creating a synonym 'ARCH' on USERA so that I could select directly
 from
> > ARCH table without specifying the USERB user. However, I still got
 the
> > SAME error mesage.
> >
> > Please help!!
> >
> > Susan
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >

>

> as userb you need to grant select on table arch to usera

>

> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers

>

> Sent via Deja.com
> http://www.deja.com/

>

--

Sincerely,
Larry Geller
Database Specialist
Oracle,Sybase,MS SQL Server


Sent via Deja.com
http://www.deja.com/

Received on Wed Jan 03 2001 - 17:00:43 CST