Re: select data from table owned by other user

In that case you already have privilege through a role and roles are not enabled in procedures and functions.
Typically this is resolved in either one or two ways in Oracle 7 and 8.0 1 make sure the table owner owns the procedures also. Grant execute privilege to other users
2 issue a direct grant to the user
In Oracle 8i you can create procedures to run with definers rights ('old' behavior) or invokers rights.

Hth,

Sybrand Bakker, Oracle DBA

Tom Schelhorn <t.schelhorn_at_finec.de> schreef in berichtnieuws 8g16nl$f3ea$1_at_fu-berlin.de...
> thanx for your hint.
>
> ... but there's one thing I'm wondering about: if I run the query directly
> as "select * from tmp_table" in a console window it's working fine. The
> problem only occurs if I try to run the "create procedure"-statement. I
 took
> a look in all_tables and I can already see tmp_table without having
 invoked
> any grants on it.
>
> Tom
>
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> schrieb in im Newsbeitrag:
> 958667193.11054.0.pluto.d4ee154e_at_news.demon.nl...
> > The issue here is you have insufficient privilege. Tmp_user needs to
 grant
> > you select privilege. Please run select table_name from all_tables where
> > table_name = 'TMP_TABLE' before and after the grant, and you will see
 your
> > table is known now.
> > Personally I avoid hardcoding owners, so IMO you need to create either a
> > private or a public synonym as well.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> > Tom Schelhorn <t.schelhorn_at_finec.de> schreef in berichtnieuws
> > 8g153m$eus6$1_at_fu-berlin.de...
> > > Hi everybody!
> > >
> > > I've to select some data from a table owned by another user "tmp_user"
 .
> > > When I run the following code:
> > >
> > > CREATE OR REPLACE PROCEDURE ...
> > > ...
> > > BEGIN
> > > DECLARE
> > > CURSOR tmp_Cur IS
> > > SELECT * from tmp_table;
> > > ...
> > >
> > > I always get the error-message:
> > > PLS-00201:identifier 'tmp_user.tmp_table' must be declared
> > >
> > > I know that I've to declare 'tmp_table' before I can use it, but I
 could'nt
> > > find the syntax for that in oracle-docs.
> > >
> > > any help would be fine..
> > > Tom
> > >
> > >
> > >
> >
> >
>
>
Received on Thu May 18 2000 - 00:00:00 CDT