Re: Oracle Security? - What Gives?

[Quoted] Hi Merv,

    No, they are right. Oracle does not grant execute privileges to roles.

    Okay, what we want is an easy way to assign these privileges to a large group of users without too much administrative bs. My answer is:

    spool grantem.sql
    SELECT USERNAME FROM DBA_USERS:
    spool off
    !vi grantem.sql

        (substitute your favorite editor for vi and use host instead of ! if you are on NT)

[Quoted]         delete users whom you don't want to grant the privilege to.

        make a global change to the ones who are left to make each line read:

        GRANT EXECUTE ON <procedure_name> TO <user_name>;

    exit the editor and return to SQL

    _at_grantem

Viola!

I admit it is a bit of a kludge, but you wind up with a script that you can [Quoted] use to grant privileges to the same group in the future and everything is nicely documented.

regards

Jerry

Merv wrote in message <35E4296C.337E_at_mcmail.com>...
>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 Thu Aug 27 1998 - 00:00:00 CEST