Re: Permissions problem when attempting to execute a procedure

"Randy Baker" <rsbakerZ_at_msn.com> schrijfbewerkingen: > I've been writing scripts to distribute to our clients to make any schema
> changes required between versions of our applications.
>
> One of the most frequent things I have to do is create a simple trigger
> which inserts a sequence value as the primary key of a table.
>
> Rather than include the trigger text in each script which has to create a
> trigger, I created a procedure to do the job for me. The procedure generates
> the code for the trigger and passes it to DBMS_SQL.Parse() to perform the
> update. However, if I try to
> execute the procedure, I get an error about insufficient priviledges:
>
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "CRIBSAMPLE.WXDB_CREATESEQUENCETRIGGER", line 30
> ORA-06512: at line 2
>
> In this case, I am logged in as CRIBSAMPLE, and CRIBSAMPLE owns its default
> table space.
>
> Note that if I execute the trigger creation text generated by the procedure
> directly from the worksheet, the trigger is created without any problems.
> The procedure was also created properly via the same worksheet while logged
> in as the same user.
>
> I'm somewhat baffled. Summarizing...
>
> 1. I can create a trigger
> 2. I can create a procedure which generate the same text as used to create
> the trigger in step (1).
> 3. I *can't* call the procedure I created.
>
> --
> Randy Baker (remove Z from address in email replies)
>
>

If you use stored software (in this case DBMS_SQL) in a procedure, you have to have execute rights on the stored software, either directly as the user or via a role.
If you use stored software in een STORED procedure you must have execute rights directly as a user. Execute rights via a role is not enough.

I've never been able to discover why ORACLE makes this distinction, but this is how it is.

Jaap. Received on Fri Mar 20 1998 - 00:00:00 CST