oracle给授权过程,oracle授权过程的简介与内容

/* 99.SQL$

1.任务：将userA所有的table(或view等)的select权限(或insert等)授权给userB

2.基本思想： 以usera登录.将select "grant insert on "||table_name ||" to userb;" from user_objects ;生成的所有SQL语句执行。

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

*/

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;