SQL Script Dynamic Execute Granting

Updated: 30-Mar-2002
Version: 1.0

Description

This script performs dynamic granting of PROCEDURES/FUNCTIONS/PACKAGES to users/roles/PUBLIC. This script needs to be run as the owner of the objects you are granting to

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 EXECUTE only on PACKAGES,PROCEDURES,FUNCTIONS to &s_user'
prompt

DECLARE
	l_sql varchar2(254);
	cursor_id integer;
	result integer;

	l_target_user varchar2(80) := '&s_user';

cursor get_obj is
    select object_name from user_objects
    where object_type IN ('PACKAGE','FUNCTION','PROCEDURE');

BEGIN

cursor_id:=dbms_sql.open_cursor;

FOR obj_rec in get_obj LOOP

   l_sql := 'grant execute on '||obj_rec.object_name||' to '||l_target_user;
   dbms_sql.parse(cursor_id,l_sql,1);
   dbms_output.put_line(l_sql);
   result := dbms_sql.execute(cursor_id);

END LOOP;

dbms_sql.close_cursor(cursor_id);

END;
/

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links