Re: Oracle Security? - What Gives?

I don't think this is quite correct. If the user_id that compiles the procedure has been given privileges through a role, the procedure won't be executable by others with those privileges granted through a role.

The user_id that compiles the procedure must have the privileges granted directly to it. You could test this by logging into SQL*Plus and doing a set roles none. Then try and drop and recreate the sequence. If it fails, you know the privilege has been granted through a role. Get the create any sequence privilege granted directly to the compiling user, recompile your procedure and have other users test it again.

If create any sequence has been granted directly to the compiling user, but other users still don't have sufficient privileges to use the procedure, you might try one other thing. Get the create any sequence privilege granted to the compiling user with grant (or admin) option. This gives the compiling user permissions to grant this privilege to other users. Then recompile your procedure and test. (I built a set of stored procs to grant and revoke roles, so I know this latter step is necessary in some cases and does work.)

One of these two fixes should work. You shouldn't ever have to give direct system or object permissions to more than the user compiling the procedure.

Dennis
Troy Perchotte <max_at_headroom.com> wrote in article <35E34BD9.3ED67DF7_at_headroom.com>...
> 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.
>
> Since I need a large number of users to be able to execute this
> procedure, I granted a "create any sequence" to the group's role. I
> still received the same error.
>
> 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".
>
> What gives???
>
> Troy Perchotte
>
>
>
  Received on Wed Aug 26 1998 - 00:00:00 CDT