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;