Re: PL/SQL privileges

From: Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl>
Date: Fri, 21 May 2004 08:27:47 +0200
Message-ID: <2l9pa0lhsgfnhtemnlqnmo6g51vjr4t1e2@4ax.com>

On Thu, 20 May 2004 12:04:29 +0200, "Jon" <aaa_at_bbb.ccc> wrote:

>Hi,
>
>A user has "create any table" privileges and the same user has a procedure
>with
>
>"execute immedate 'create table ...'"
>
>when the user tries to run the procedure, it returns the following error
>
>"ORA-01031: insufficient privileges"
>
>thanks
>

Likely the user has privilege *through a role* and roles are ignored during compilation of stored procedures as roles are volatile. Either: grant 'create any table privilege' directly to the user (not that it wouldn't be a horrible thing to do) or
create the procedure with authid current_user in 8i and higher.

BTW: this question has been posed more than a 1000 times in a year. It is a doc question and I have provided this explanation numerous time.
This forum is a volunteer operation. Could you *PLEASE* search the archives at groups.google.com prior top posting? TIA

--
Sybrand Bakker, Senior Oracle DBA

Received on Fri May 21 2004 - 01:27:47 CDT