SQL Script

Dynamic Granting

Updated : 17-January-1999
Version : 1.0

Description

This script performs dynamic granting of tables,views,sequences of the SELECT, INSERT, UPDATE, DELETE, REFERENCES privilege only to users. This script needs to be run as the owner of the objects you are granting to. This is useful for setting up seperate schemas to store procedures and triggers. Note that the REFERENCES privilege can only be granted to schemas and therefore this script will fail of you attempt to grant references to a ROLE.

Parameters

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

SQL Source

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

accept s_user prompt 'Enter USERNAME or PUBLIC to grant to : ' 
prompt
show user
prompt 'Granting SELECT,INSERT,UPDATE,DELETE,REFERENCES 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,references 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;
/

spool off