René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Grant Object Privileges to other Users in Oracle

set verify       off
set serveroutput  on

declare
  cursor cur_t is select object_name,object_type from all_objects where 
    owner = '&OWNER' and 
    object_type in ('TABLE', 'VIEW') and
    status = 'VALID';

  obj_name   all_objects.object_name%type;
  obj_type   all_objects.object_type%type;
begin
  open cur_t;
  loop
    begin 
      fetch cur_t into obj_name, obj_type;
      exit when cur_t%notfound;
      execute immediate 
        'grant all on ' || obj_name || ' to ' || '&GRANTEE' || 
        ' with grant option';

    exception
      when others then
        ##(dbms_output,/ora/plsql/dbms_output.html#put_line).put_line(
          '**** Problem with: ' || obj_name || ', ' || 
           obj_type || ': ' || substr(sqlerrm, 1, 100));
    end; 
  end loop;
end;
/