Re: GRANT Help

kittu_at_gis.eng.hawaii.edu (Chakravarthy Nalamotu) wrote:
>Hi All,
>Is there a way to grant Object Privileges
>(ALTER, SELECT etc.) on all the objects
>in a schema to a role?
>
>The procedure that I follow right now is
>
>GRANT ALTER, SELECT
> ON owner.object1
> TO role1
>
>GRANT ALTER, SELECT
> ON owner.object2
> TO role1
>
>What if the "owner" schema has 200 objects.
>There must be a better way to grant access
>to these objects, instead of granting
>permissions to individual objects.
>
>OK, I was looking for a statment similar to
>the one below...to work for me (incorrect though)
>
>GRANT ALTER, SELECT
> ON owner
> TO role1
>
>and was expecting the statement to give "ALTER"
>and "SELECT" privileges to all the objects in
>the "owner" schema.
>
>There must be a better way to do this, rather
>than granting permission on each object to a
>role.

Try the Following

set feedback off
set heading off
set term off
set trim on
set pagesize 0
set line 120
set echo off

spool my_grant.sql

select 'grant alter, select on user_dummy.'||object_name||' to my_role;'
from user_objects
order by object_name

spool off

ste feedback on
set heading on
set trim on
set pagesize 60
set term on

The above code creates a file named my_grant.sql, please open it and edit for any changes and run it.

BY using

sql> start my_role

a Hope this Helps

Regards

Vijay Kumar Banda
Ford Motors!! Received on Fri May 17 1996 - 00:00:00 CEST