From: mcstockX@Xenquery   
      
   "Ted"  wrote in message   
   news:39d8ada9.0409151012.7da37d1e@posting.google.com...   
   | 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?   
      
   Like 'GRANT SELECT TO xxx ON SCHEMA SCOTT'? Good idea, but doesn't exist.   
      
   You can, however, right PL/SQL to loop through all objects in a schema and   
   grant appropriate privileges to the target role   
      
   Something like:   
      
   procedure grant_all_objects(ip$role in varchar2)   
       for r1 in (select object_type, object_name from user_objects where   
   object_type in .... )   
       loop   
           case   
           when r1.object_type = 'TABLE'   
           then execute immediate 'grant select on ' || r1.object_name || ' to   
   ' || ip$role;   
           when r1.object_type in ('PROCEDURE','FUNCTION'...   
      
   ++ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)