Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: "Table must be declared" in procedure?

Re: "Table must be declared" in procedure?

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Thu, 01 Feb 2001 13:41:36 GMT
Message-ID: <95bp2e$i22$1@nnrp1.deja.com>

In article <957qu3$8ds$1_at_nnrp1.deja.com>,   mjgusa_at_my-deja.com wrote:
> Thanks Mark. In the end, that was it. What is the rule on this
 anyway?
> What types of things must be granted to a specific user, vs
 indirectly
> via a role that is granted to the user?
>
> Thanks,
> Mike
>
> In article <956ju2$309$1_at_nnrp1.deja.com>,
> Mark D Powell <markp7832_at_my-deja.com> wrote:
> > In article <956j20$22b$1_at_nnrp1.deja.com>,
> > mjgusa_at_my-deja.com wrote:
> > > Hi:
> > >
> > > I am trying to have a procedure insert data into the table of the
> > > caller's schema and getting an error telling me that the result
> > > table "must be declared". I am just doing something like:
> > > insert into otherguys.results (select whatever from mytable)
> > >
> > > The procedure is a definer rights procedure, owned by the same
 user
 who
> > > owns mytable. I have even granted pretty much all privileges on
 both
> > > tables to all, but still I get this error trying to load the
> > > procedure. What's the real problem? Is there something like
 FORCE
> > > that I can use? I am using 8.1.7 on an NT.
> > >
> > > Thanks,
> > > Mike
> > >
> > I am not sure in your specific case, but a likely cause of the error
> > you are getting is this: in order for owner A to access owner B's
> > tables from within packaged code owner A must have a direct grant on
> > user B's objects. The grants can not be inherited from a role.
> >
> > So as B try granting the necessary privileges directly to A and see
 if
> > the compile errors go away. If not you should post the actual error
> > messages.
> >
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >

I am not sure of the formal 'rule', but in order to reference objects owned by another user in code that you can grant to other third parties you must have a direct grant. This requirement effects packages, stored procedures and functions. It also affects views, but right now I can not remember if a direct grant allows you to reference other's code and grant the view to a third person or if you need the grant 'with the grant option'.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/
Received on Thu Feb 01 2001 - 07:41:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US