SQL Script

Dynamic Granting

Updated : 5-Mar-2002
Version : 2.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

spool dysrgrnt.lst

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
	sql_stmt varchar2(1024);

	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

/* Tables first */

FOR tab_rec in get_tab LOOP

   sql_stmt := 'grant select,insert,update,delete,references on '||tab_rec.table_name||' to '||l_target_user;

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

/* Views */

FOR view_rec in get_view LOOP

   sql_stmt := 'grant select,insert,update,delete on '||view_rec.view_name||' to '||l_target_user;

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

/* Sequences */

FOR seq_rec in get_seq LOOP

   sql_stmt := 'grant select on '||seq_rec.sequence_name||' to '||l_target_user;

   EXECUTE IMMEDIATE sql_stmt;

END LOOP;

END;
/
spool off

Previous Oracle Version Links

Version 8.0 - Dynamic Granting

Return to Index of SQL Scripts