Granting the privileges of existing objects within a schema to a new role

[Ted]

How do I grant all privileges for a schema that has a large number of
existing tables, procedures, functions, etc to a newly created role,
without having to issue a grant statement for each object and each
privilege? I want the role to have all of the rights of the schema
owner.

Is there any kind of blanket granting of all privileges to a role?

[Mark C. Stock]

"Ted" <st******@ceva. net> wrote in message
news:39******** *************** ***@posting.goo gle.com...
| How do I grant all privileges for a schema that has a large number of
| existing tables, procedures, functions, etc to a newly created role,
| without having to issue a grant statement for each object and each
| privilege? I want the role to have all of the rights of the schema
| owner.
|
| Is there any kind of blanket granting of all privileges to a role?

Like 'GRANT SELECT TO xxx ON SCHEMA SCOTT'? Good idea, but doesn't exist.

You can, however, right PL/SQL to loop through all objects in a schema and
grant appropriate privileges to the target role

Something like:

procedure grant_all_objec ts(ip$role in varchar2)
for r1 in (select object_type, object_name from user_objects where
object_type in .... )
loop
case
when r1.object_type = 'TABLE'
then execute immediate 'grant select on ' || r1.object_name || ' to
' || ip$role;
when r1.object_type in ('PROCEDURE','F UNCTION'...

++ mcs

[Bob Murphy]

Ted wrote:

How do I grant all privileges for a schema that has a large number of
existing tables, procedures, functions, etc to a newly created role,
without having to issue a grant statement for each object and each
privilege? I want the role to have all of the rights of the schema
owner.

Is there any kind of blanket granting of all privileges to a role?

-- Try something like:
rem This script performs dynamic granting of tables,views,se quences to
rem users/roles/PUBLIC. This script needs to be run as the owner
rem of the objects you are granting to.

rem s_user - List of users/roles to grant to. Can be comma seperated.

set verify off
set pause off
set doc off
set heading off

accept s_user prompt 'Enter USERNAME,ROLE, or PUBLIC to grant to : '

prompt

show user

prompt 'Granting SELECT,INSERT,U PDATE,DELETE only to &s_user'
prompt

DECLARE
l_sql varchar2(254);
cursor_id integer;
result integer;

l_target_user varchar2(80) := '&s_user';

cursor get_tab is
select table_name from user_tables ;

cursor get_view is
select view_name from user_views;

cursor get_seq is
select sequence_name from user_sequences;

BEGIN

cursor_id:=dbms _sql.open_curso r;

/* Tables first */

FOR tab_rec in get_tab LOOP

l_sql := 'grant select,insert,u pdate,delete on
'||tab_rec.tabl e_name||' to '||l_target_use r;
dbms_sql.parse( cursor_id,l_sql ,1);
result := dbms_sql.execut e(cursor_id);

END LOOP;

/* Views */

FOR view_rec in get_view LOOP

l_sql := 'grant select,insert,u pdate,delete on
'||view_rec.vie w_name||' to '||l_target_use r;
dbms_sql.parse( cursor_id,l_sql ,1);
result := dbms_sql.execut e(cursor_id);

END LOOP;

/* Sequences */

FOR seq_rec in get_seq LOOP

l_sql := 'grant select on '||seq_rec.sequ ence_name||' to
'||l_target_use r;
dbms_sql.parse( cursor_id,l_sql ,1);
result := dbms_sql.execut e(cursor_id);

END LOOP;

dbms_sql.close_ cursor(cursor_i d);

END;
/

-- add loops for each type (e.g., packages, etc.)