oracle授权过程

/* 99.SQL$
<HUG - 110426.1 By: Jim>
1.任务：将userA所有的table（或view等）的select权限（或insert等）授权给userB
2.基本思想： 以usera登录.将select 'grant insert on '||table_name ||' to userb;' from user_objects ;生成的所有SQL语句执行。
</HUG - 110426.1 By: Jim>

<TEST>
 set serveroutput on
 declare
  p_owner varchar2;
  p_obj_type varchar2;
 begin
  p_owner := 'japp';
  p_obj_type := 'table';
  grant_objects(p_owner,p_obj_type);
 end;
 /
 set serveroutput off
</TEST>
*/

create or replace procedure grant_objects(
p_owner varchar2, -- 引用者，即 SCHEMA
p_obj_type varchar2 -- 对象类型：'TABLE','VIEW','PROCEDURE','FUNCTION'
) as

 str_sql varchar2(1000);
 in_count number default 0;
 begin
 for grant_ob_value in (select OBJECT_NAME from user_objects where OBJECT_TYPE in(upper(p_obj_type)))
    loop
  if 'TABLE' = upper(p_obj_type) then
   str_sql := 'grant DELETE,SELECT,INSERT,UPDATE on '||grant_ob_value.OBJECT_NAME||' to '||upper(p_owner);  
  elsif 'VIEW' = upper(p_obj_type) then
   str_sql := 'grant SELECT on '||grant_ob_value.OBJECT_NAME||' to '||upper(p_owner);
  elsif 'PROCEDURE' = upper(p_obj_type) then
   str_sql := 'grant EXECUTE on '||grant_ob_value.OBJECT_NAME||' to '||upper(p_owner);
  elsif 'FUNCTION' = upper(p_obj_type) then
   str_sql := 'grant EXECUTE on '||grant_ob_value.OBJECT_NAME||' to '||upper(p_owner);
  elsif 'FUNCTION' = upper(p_obj_type) then
   str_sql := 'grant EXECUTE on '||grant_ob_value.OBJECT_NAME||' to '||upper(p_owner);
  end if;
  --select count(1) into in_count from DBA_TAB_PRIVS D where D.GRANTEE = upper(p_owner) and D.TABLE_NAME = grant_ob_value.OBJECT_NAME;
        begin          
            IF in_count = 0 THEN
              dbms_output.put_line(str_sql);
              execute immediate str_sql;
            END IF;
        exception
          --When Others Then Null;
            when OTHERS Then
    dbms_output.put_line(sqlerrm);
        end;
    end loop; 
end;