Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work!

I took both of these thoughts, and went back in and re-created the procedure as the schema owner of the tables.

So access to all tables should be a given, since the schema owner is the one who created the tables.
But when I execute it, it still bombs out with the same message. Regardless of whether or not I execute the procedure as the schema owner, or if I grant access to the procedure to my regular id and run it from there.

Now if I look at the objects owned by the schema owner, it shows both tables, and the stored procedure which references those tables. So I know I have them all under the same id. I also granted ALL (even though execute is all that is needed) to my regular id, logged on as that id, and executed the procedure owned by the schema owner, but got the same results.

I also ran the SQL straight from my HD while logged on as the schema owner, to make sure it still worked that way, and it did. So the problem still has something to do with the fact that it is a stored procedure.

Any more ideas?

Tomm Carr wrote:
>
> madhus_at_aquas.com wrote:
> >
> > My guess is that the tables: w_wellwork_common, w_selected_wellwork
> > are not
> > owned by the user under which you are logging in and executing. If
> > this is
> > the case, give select access on these tables directly to the user, not
> > thru'
> > a role. Procedures require direct access. Try this and it should work.
> > good luck!
> >
>
> When executing a stored procedures, the access rights in effect are
> those of the owner of the SP. So the access should be given directly to
> the owner of the SP, not the user.
>
> --
> Tomm Carr
> --
> "Can you describe your assailant?"
> "No problem, Officer. That's exactly what I was doing when he hit me!"
 

-- 
**********************
Larry Jones
lljo_at_chevron.com