SQL ScriptDynamic Granting |
This script performs dynamic granting of tables,views,sequences to users/roles/PUBLIC. This script needs to be run as the owner of the objects you are granting to.
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; /
Return to Index of SQL Scripts
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|