SQL Script

Dynamic Execute Granting

Updated : 3-March-2002
Version : 2.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

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

   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

FOR obj_rec in get_obj LOOP

   sql_stmt := 'grant execute on '||obj_rec.object_name||' to '||l_target_user;

   dbms_output.put_line(sql_stmt);

   EXECUTE IMMEDIATE sql_stmt;

END LOOP; 

END;
/

Previous Oracle Version Links

Version 8.0 - Dynamic Execute Granting

Return to Index of SQL Scripts