在存储过程中访问另一个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;