From: murphytx@texas.net   
      
   Ted wrote:   
   > How do I grant all privileges for a schema that has a large number of   
   > existing tables, procedures, functions, etc to a newly created role,   
   > without having to issue a grant statement for each object and each   
   > privilege?  I want the role to have all of the rights of the schema   
   > owner.   
   >   
   > Is there any kind of blanket granting of all privileges to a role?   
      
   -- Try something like:   
   rem This script performs dynamic granting of tables,views,sequences to   
   rem users/roles/PUBLIC. This script needs to be run as the owner   
   rem of the objects you are granting to.   
      
   rem s_user - List of users/roles to grant to. Can be comma seperated.   
      
   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,INSERT,UPDATE,DELETE 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 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;   
   /   
      
   -- add loops for each type (e.g., packages, etc.)   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)