Q: Execute privilege required on stored function

From: Peter Mapson <mapsonp_at_ois.com.au>
Date: 1996/03/25
Message-ID: <4j5q9i$sdv_at_eagle.ois.com.au>#1/1


I'm using Oracle 7.1 and PL/SQL 2.1. One of the new features of this release is the ability to place user defined functions in SQL statements.

I have create a function, and can reference it successfully in an SQL statement when connected to the database as the owner of the function. If I grant execute on the function to a role, and then connect as a user who has been granted the role, I can also use the function in an anonymouse PL/SQL block. But if I try to reference the function in a SQL statement I get a PLS-00201 error ( identifier <function name> must be declared).

I have found that this problem goes away if I grant execute on the function directly from the owner of the function to the other user, or if I grant execute on the function to PUBLIC. Neither solution strikes me as desirable: I don't like granting things to PUBLIC, and having to grant execute to each and every user that requires the function is an administrative overhead I'd rather not have.

Why can I use a function which has been granted excute through a role in a PL/SQL block, but not in an SQL statement. This seems inconsistent. Am I missing the point, or is this a "bug" in Oracle's implementation of roles? All I can find in the manuals is a statement that to execute a function in a SQL statement you must either own it or have been granted execute on the function. This is somewhat ambiguous: does this mean I have to have been granted execute directly, or that I only need to have been granted execute indirectly through a role?

I know you can't argue with what the software actually does (when the manual and the software disagree, the software is always right). I tried the local Oracle support, and the guy I talked to there basically read the manual to me :( Anyone else had this problem? Received on Mon Mar 25 1996 - 00:00:00 CET

Original text of this message