Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Security? - What Gives?

Re: Oracle Security? - What Gives?

From: OraCLeDbA <oracle_dba_at_zdnetmail.com>
Date: 1998/08/26
Message-ID: <6s2nrs$59f@dfw-ixnews7.ix.netcom.com>#1/1

No. You cannot grant privilege of a object through a role if you are referencing that object in a stored procedure/function. That is, you must be granted DIRECTLY the corresponding privilege from the owner of the object (or any one with grant option on that object). You CANNOT be granted that privilege INDIRECTLY through a role.

To me, I think it is really stupid. If I grant that privilege to a role, that means that I give every one who has that role that privilege and they can use it in whatever way they want (including a procedure/function). I hope that Oracle will fix that problem in the later release

Winnie

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 Wed Aug 26 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US