/* 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;