Re: Granting the privileges of existing objects within a schema to a new role
Date: Wed, 15 Sep 2004 19:41:19 -0500
Message-ID: <SYOdnfeeWb0ZftXcRVn-vA_at_texas.net>
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,sequences 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,UPDATE,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_cursor;
/* Tables first */
FOR tab_rec in get_tab LOOP
l_sql := 'grant select,insert,update,delete on '||tab_rec.table_name||' to '||l_target_user;
dbms_sql.parse(cursor_id,l_sql,1);
result := dbms_sql.execute(cursor_id);
END LOOP; /* Views */
FOR view_rec in get_view LOOP
l_sql := 'grant select,insert,update,delete on '||view_rec.view_name||' to '||l_target_user;
dbms_sql.parse(cursor_id,l_sql,1);
result := dbms_sql.execute(cursor_id);
END LOOP; /* Sequences */
FOR seq_rec in get_seq LOOP
l_sql := 'grant select on '||seq_rec.sequence_name||' to '||l_target_user;
dbms_sql.parse(cursor_id,l_sql,1);
result := dbms_sql.execute(cursor_id);
END LOOP; dbms_sql.close_cursor(cursor_id);
END;
/
- add loops for each type (e.g., packages, etc.)