Re: Oracle Security? - What Gives?

I'm using 7.3.4 and have only had to grant privileges to the owner of the stored procedure. I then grant execute permission to the role and all users granted that role can run the procedure.

From PCR Number 487928:

When a stored procedure is executed, it is executed under the privileges of the owner of the procedure without enabling any roles. ...
Do not confuse roles with GRANTs and REVOKEs -- Roles are meant to be toggled on and off on a per session basis, whereas GRANTs and REVOKEs (as well as any associated dependencies) updates the data dictionary because they are DDL operations. This is why it becomes an issue of gross ambiguity: A user can potentially log on in the form of two sessions, one with the role enabled, the other one with the role disabled, and the outcome of both sessions will not be the same.

Hence, the restriction that stored object creation cannot depend on privileges granted through a role is added in the Oracle7 Server to avoid ambiguous cases of this nature.

Regards,
Dean

Troy Perchotte wrote:
>
> I created a stored procedure that first drops and then creates a
> sequence. It runs successfully; however, when I tested it from another
> user's account, oracle returns an "insufficicient priveledges" error.

[ snip ]  

> After speaking with oracle support, I was told that I had to grant the
> "create any sequence" to each individual account, and that the role's
> permission "doesn't work in this case".
Received on Wed Aug 26 1998 - 00:00:00 CDT