Re: MORE Info - Stored Procedure Use vs. Role Use
Date: 1997/09/19
Message-ID: <342482b9.86698816_at_newshost>#1/1
On Thu, 18 Sep 1997 06:38:12 -0500, Larry Jones <lljo_at_chevron.com> wrote:
>I mis-stated that the grant has to be given to the person executing the
>procedure. It has to be whoever owns the proc.
>
[snip]
>First step was to move the procedure under the schema owner for the
>tables/views, WITS. Now the procedure CCT has been created under userid
>WITS, and WITS grants EXECUTE on CCT to PUBLIC. LLJO executes WITS.CCT,
>and does not get the "...not exist" error, on any of the tables owned by
>WITS, but does get "ORA-01031: insufficient privileges" in the WHERE
>portion of a SELECT statement on a table that is NOT owned by WITS, but
>to which the procedure must join.
>
Are you doing static or dynamic sql in the procedure? If you are doing static, this shouldn't happen. I'm guessing however you are using dbms_sql in the procedure. The privelege check for dbms_sql happens at run time, not compile time. Does the user WITS have direct access to the objects referenced in the dynamic query? Can you post the code?
>Now WHY it gets 01031, on a SELECT statement, I can't understand. But
>the problem is there again. Why should the owner of the other table have
>to grant SELECT directly to everyone? Is this the same problem? I don't
>think so, b/c it's not the 00942 error message, but one that should not
>even apply to a SELECT statement!
>
>I know there is a conflict with the use of roles and procedures, as
>described above, but I can't understand where this error is coming from.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 19 1997 - 00:00:00 CEST