SQL Script

Dynamic Granting

Updated: 17-Jan-1999
Version: 1.0

Description

This script performs dynamic granting of tables,views,sequences of the SELECT privilege only to users/roles/PUBLIC. 

This script needs to be run as the owner of the objects you are granting to. This is useful for setting up read only roles.

Parameters

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

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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 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 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 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

Site Map

SQL Script

Dynamic Granting

Updated: 17-Jan-1999Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 17-Jan-1999
Version: 1.0