Oracle递归授权view底层依赖表查询权限存储过程

create or replace procedure sys.grant_view_base_table_access
(
p_accessowner VARCHAR2,
p_vowner VARCHAR2,
p_vname VARCHAR2
)
--RETURN number
as
v_accessowner VARCHAR2(200) :=trim(upper(p_accessowner));
v_owner VARCHAR2(200) := trim(upper(p_vowner));
v_name VARCHAR2(200) := trim(upper(p_vname));
--v_granted number := 0;
v_sql VARCHAR2(500);
-- Check referenced tables and views

BEGIN

for myCursor in (
SELECT
referenced_owner,
referenced_name
FROM dba_dependencies
where referenced_type = 'TABLE'
START WITH (owner, name, type) IN
(SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_type = 'VIEW'
and owner = v_owner
AND object_name = v_name)
CONNECT BY NOCYCLE name = prior referenced_name
and owner = prior referenced_owner
and type = 'VIEW'
union all
SELECT
referenced_owner,
referenced_name
FROM dba_dependencies
where referenced_type = 'VIEW'
START WITH (owner, name, type) IN
(SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_type = 'VIEW'
and owner = v_owner
AND object_name = v_name)
CONNECT BY NOCYCLE name = prior referenced_name
and owner = prior referenced_owner
and type = 'VIEW')

 

LOOP
-- 在这里进行授权处理
v_sql := 'GRANT select on '||myCursor.referenced_owner||'.'||myCursor.referenced_name||' TO '||v_accessowner||'';
dbms_output.put_line(''||V_SQL||'');
EXECUTE IMMEDIATE v_sql;
END LOOP;
v_sql := 'GRANT select on '||v_owner||'.'||v_name||' TO '||v_accessowner||'';
dbms_output.put_line(''||V_SQL||'');
EXECUTE IMMEDIATE v_sql;

--v_granted := 1;
--RETURN v_granted;
END;

 

--使用示例：
--execute sys.grant_view_base_table_access('need_privs_user','v_owner','v_name');