Re: Oracle Security? - What Gives?

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.
>
> 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

Hi Troy,

Something doesn't sound right to me. The whole point about having roles is that you avoid the need to do numerous grants to users.

I would guess that the database is complaining about something else. Remember, in order for the role to run your procedure as you've described
it needs all of the following permissions:

	Create Sequence
	Drop Sequence
	Execute Procedure

You may also get this message if the owner of the procedure does not have the necessary object privileges for the objects used inside the procedure. These are the only privileges which must have been directly granted.

Another thing to check is that the users can actually see the procedure. i.e that you reference it as SCHEMA.procedure_name or that you've set up synonyms.

Hope this is of some assistance.

Regards  

Mervin Samuels Received on Wed Aug 26 1998 - 00:00:00 CDT