Re: Role Privilege Question/Peculiarity

A copy of this was sent to Martin Douglas <Martin.Douglas_at_Boeing.com> (if that email address didn't require changing) On Tue, 31 Aug 1999 16:47:21 GMT, you wrote:

>Put your thinking hats on...
>
>My application requires the CREATE ANY PROCEDURE system privilege so
>that it can perform procedure creation via dynamic sql. The schema used
>is granted this privilege and works fine. However, our DBA got the
>brilliant idea (or not) to stick this privilege under a role and then
>grant the role to the above schema instead. Viola... the application no
>longer works and complains about invalid privileges in the dbms_sql and
>dbms_sys_sql packages.
>
>Any explanations as to why this would happen? There obviously is a
>reason for not passing on the privilege... I just want to know why oh
>why :)
>
>Thanks!

see the URL in my signature for paper "Why do I get an ORA-1031 or PL-201 with stored procedures"

You don't need CREATE ANY PROCEDURE (which is *very* powerful) to simply create procedures -- you just need CREATE PROCEDURE to create a new procedure in your schema.

--
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 Tue Aug 31 1999 - 13:38:34 CDT