Re: I'm missing something obvious

In article <7q8jnu$674$1_at_nnrp1.deja.com>,   michael_bialik_at_my-deja.com wrote:
> In article <7q4g6g$dhg$1_at_nnrp1.deja.com>,
> garysadler_at_my-deja.com wrote:
> > I am trying to execute dbms_shared_pool from within a stored
> > procedure owned by SYS, and getting privilege errors, even if SYS is
> the
> > user executing the procedure. Dbms_shared_pool is owned by SYS, as
is
> > dbms_utility. It's got something to do with roles because SYS can
> > actually execute dbms_shared_pool from sqlplus, unless I issue SET
> ROLE
> > NONE, then try it. Why would SYS need a role to execute a package
> that
> > it owns?
> >
> > Here's the exact message I get when running sqlplus after SET ROLE
> NONE:
> >
> > begin dbms_shared_pool.keep('owner.packagename'); end;
> > *
> > ERROR at line 1:
> > ORA-01031: insufficient privileges
> > ORA-06512: at "SYS.DBMS_UTILITY", line 68
> > ORA-06512: at "SYS.DBMS_SHARED_POOL", line 43
> > ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51
> > ORA-06512: at line 1
> >
> > Oracle 8.1.5
> > Solaris 2.6
> >
>
> Hi.
> As I remember, the privilege must be granted directly
> ( NOT via role ).
> Try
> "GRANT EXECUTE ON SYS.DBMS_SHARED_POOL TO PUBLIC ( or
> any user name )".
>
> Good luck. Michael.
>

I believe the correct answer is to log on as the package owner and grant execute to sys. I am pretty sure that is what we did so that our startup script pins and loads most of our packages on database instance startup.

--
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/
Share what you know. Learn what you don't. Received on Mon Aug 30 1999 - 08:19:40 CDT