Re: GRANT Help

From: Naresh Ramamurti <nramamur_at_worldnet.att.net>
Date: 1996/05/14
Message-ID: <319952CE.67E6_at_worldnet.att.net>#1/1

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.
>
> Thank you for any replies.
> Kittu.Kittu,

You can use dynamic SQL to accomplish this task. e.g :- Let's say you wish to grant select, insert, update and delete on all of X's tables to role Y.
Here's what you do:-
SQL> select

	'grant select,insert,update,delete on X.'||table_name||' to Y;'
     from 
     	user_tables;

Using appropriate set options in SQL*Plus spool the output of this statement to a file. The file would then look like:-
grant select,insert,update,delete on X.table1 to Y; .....
.....
grant select,insert,update,delete on X.tablen to Y;

You can have any number of tables in X's schema. You can use the same script for different kinds of objects:- e.g For Stored Procedures, Packages, etc replace select... with execute ... You can query from user_objects or "obj" instead of user_tables if you wish to select all objects and grant the same privilege to a role.

Hope this helps.
Naresh. Received on Tue May 14 1996 - 00:00:00 CEST