在存储过程中访问视图授权

在存储过程中访问另一个schema的同义词，如果访问权限是通过角色来授权的，在编译存储过程中不能访问对象情况，这就需要直接授权，

以下是参考脚本

DECLARE
  V_GRANTOR_USER VARCHAR2(20) := 'S_USER'; -- 原来
  V_GRANTEE_USER VARCHAR2(20) := 'T_USER'; -- 被授予的用户
  --V_GRANTEE_ROLE VARCHAR2(20) := 'R_DEV'; -- 被授予的role
BEGIN
  FOR L IN (SELECT  SYNONYM_NAME OBJECT_NAME
              FROM Dba_Synonyms
             WHERE OWNER = V_GRANTEE_USER
            ) LOOP
    EXECUTE IMMEDIATE 'DROP  SYNONYM ' || V_GRANTEE_USER ||'.'||L.OBJECT_NAME;
  END LOOP;
 
  FOR L IN (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OWNER = V_GRANTOR_USER
               AND OBJECT_TYPE IN ('TABLE') -- SEQUENCE,job,type
            ) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT,DELETE,UPDATE,INSERT ON ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME || ' TO ' || V_GRANTEE_USER;
    EXECUTE IMMEDIATE 'CREATE  SYNONYM ' || V_GRANTEE_USER || '.' || L.OBJECT_NAME || ' FOR ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME;
  END LOOP;

  FOR L IN (SELECT OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OWNER = V_GRANTOR_USER
               AND OBJECT_TYPE IN ('VIEW','SEQUENCE') -- SEQUENCE,job,type
            ) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME || ' TO ' || V_GRANTEE_USER;
    EXECUTE IMMEDIATE 'CREATE  SYNONYM ' || V_GRANTEE_USER || '.' || L.OBJECT_NAME || ' FOR ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME;
  END LOOP;
  -- 具体存储过程，视图，包的授权
  FOR L IN (SELECT distinct upper(OBJECT_NAME) OBJECT_NAME
              FROM DBA_OBJECTS
             WHERE OWNER = V_GRANTOR_USER
               AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE','PACKAGE BODY','TYPE')
            ) LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME || ' TO ' || V_GRANTEE_USER;
    dbms_output.put_line(L.OBJECT_NAME);
    EXECUTE IMMEDIATE 'CREATE  SYNONYM ' || V_GRANTEE_USER || '.' || L.OBJECT_NAME || ' FOR ' || V_GRANTOR_USER || '.' || L.OBJECT_NAME;
  END LOOP;
END;