Re: Oracle Security? - What Gives?

You don't want to edit the thing! In cases like this you can write SQL to generate the grant script!

simple eg

<turn all page/heading stuff off using SQL*Plus here> SPOOL commandfilename
SELECT 'GRANT SELECT ON ' || table_name || ' TO a_user; ' FROM CAT /
SPOOL OFF
<set up normal settings again>

You can use similar scripts to set up synonyms in a new schema, drop all objects etc. Much easier then dynamic SQL in PL/SQL blocks I reckon. Just use the data dictionary tables required for your particular task.

Regards,

Richard Gowan
roxl_at_c031.aone.net.au

Jerry Gitomer wrote in message ...
>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)
>
> 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
>
> @grantem
>
>Viola!
>
>
>I admit it is a bit of a kludge, but you wind up with a script that you
 can
>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 CDT