Re: I'm missing something obvious

A copy of this was sent to garysadler_at_my-deja.com (if that email address didn't require changing) On Thu, 26 Aug 1999 22:47:45 GMT, you 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?
>

it does not, the package dbms_shared_pool is being executed -- it is failing at runtime.

>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
>

sys is missing some base system priv. sys can RUN the procedure, the procedure is executing something that sys cannot run without a role. it is probably execute any procedure or something similar. On my system, with a set role none, i can do this. my SYS user has the following granted directly to it:

select * from dba_sys_privs where grantee = 'SYS';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SYS                            DELETE ANY TABLE                         NO
SYS                            DEQUEUE ANY QUEUE                        YES
SYS                            ENQUEUE ANY QUEUE                        YES
SYS                            EXECUTE ANY PROCEDURE                    NO
SYS                            EXECUTE ANY TYPE                         NO
SYS                            GRANT ANY PRIVILEGE                      NO
SYS                            GRANT ANY ROLE                           NO
SYS                            INSERT ANY TABLE                         NO
SYS                            MANAGE ANY QUEUE                         YES
SYS                            SELECT ANY SEQUENCE                      NO
SYS                            SELECT ANY TABLE                         YES
SYS                            UNLIMITED TABLESPACE                     YES
SYS                            UPDATE ANY TABLE                         NO

I believe its the execute any procedure priv that is key here. what does your sys account have?

>Oracle 8.1.5
>Solaris 2.6
>
>Gary Sadler
>Polygon Network
>gary_at_polygon.net
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 26 1999 - 21:13:53 CDT