Re: Stored procedures vs table owner

Stored procedures are restricted to privileges bestowed upon the owner directly, not any permissions granted via a role. On the other hand, an anonymous block being executed is compiled at the time of execution and will honor all privileges granted to the user both directly and through enabled roles.

So, a PL/SQL block may execute fine as an anonymous block. But taking the same block and attempting to create a stored procedure may not compile. It will now scope down to honoring only privileges bestowed directly to the owner (i.e. the user attempting the create). So, if the creation now fails, then the privileges it needed were granted via a role.

You can verify this by attempting to execute the PL/SQL code as an anonymous block under PL/SQL AFTER disabling all roles (via the set role command). With all the user roles disabled, the block will be left with only those privileges directly granted to the user and should fail for the same reasons encountered trying to create the procedure.

To allow the user the ability to create the stored procedures you want, grant the necessary privileges directly to the user id instead of using roles.

On Fri, 17 Oct 1997 16:19:05 -0400, Bob Blizard <rblizard_at_cmcsys.com> wrote:

>I am trying to create a stored procedure which queries data dictionary
>tables to build a list of datafiles to back up using UTL_FILE. It works
>great as a block of PL/SQL run by SYSTEM, but when I try to change it to
>CREATE OR REPLACE PROCEDURE... it throws up and tells me I have to
>declare identifier 'SYS.V$LOGFILE', etc.
>
>I tried a simple block of code which accesses V$LOGFILE plus a table
>owned by a random user, and I can run it as system or sys, but compile
>it only as SYS. Is this documented? Why does it work this way? I find
>SYSTEM can execute ok, so this now a question I need to understand the
>why of, rather than being permanently stuck...
>
>Thanks in advance.
>--
>
>Bob Blizard rblizard_at_cmcsys.com
>CMC Systems 175 Littleton Rd Westford, MA 01886
>Ph (508) 392-1300 Fax (508) 392-1303
>
>"To park in the first row of life, you must first go there to
> look for a parking space"

Bob Cunningham
bcunn_at_oanet.com
bcunn_at_compuserve.com Received on Sun Oct 19 1997 - 00:00:00 CEST