example - procedure plsql oracle

Oracle "tabla o vista no existe" desde el interior del procedimiento almacenado

Question

el escenario es así ...

TENGO un espacio de nombre XXX donde creé algunas tablas y algunos procedimientos almacenados ...

TIENEN un espacio de nombre YYY donde crearon algunas tablas ...

OTORGARON XXX acceso a sus tablas, así que cuando me conecté a SQL Developer usando la conexión XXX , puedo hacer lo siguiente:

SELECT * FROM YYY.TableA

Pero si intento ejecutar esa misma declaración desde dentro de un procedimiento almacenado (ya sea un procedimiento almacenado simple o un paquete), el procedimiento almacenado no se compila. Sucede a muchas sp. ¿Hay algún otro permiso que tenga que pedir? Estoy ejecutando sp''s así:

CREATE OR REPLACE PROCEDURE PRC_SOMESP(
) AS BEGIN
END PRC_SOMESP;

Los procedimientos que no acceden a las tablas YYY se compilan bien.

Gracias por adelantado.

Después de la respuesta de Justin Cave, intento agregar la frase "AUTHID CURRENT_USER" a la sp pero obteniendo el mismo resultado de "tabla o vista no existe":

CREATE OR REPLACE PROCEDURE PRC_PROC1( PARAMETERS... )  
AUTHID CURRENT_USER  
AS  
    MYVAR NUMBER;  
BEGIN  
    STATEMENTS...
END PRC_PROC1;  

CREATE OR REPLACE PACKAGE PKG_PROC2  
AUTHID CURRENT_USER  
AS  
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE PRC_PROC2( PARAMETERS... )  
END PKG_PROC2  

¿Debo consultar algo más?

Answer 1

En sus procedimientos almacenados en el esquema XXX si accede a las tablas del esquema YYY , asegúrese de calificarlas por completo:

select count(1) from YYY.TableA;

Otra cosa a considerar es la carcasa (en caso de que esté mezclando mayúsculas y minúsculas en sus identificadores de Oracle).

Una última cosa: publique el error que está recibiendo. Será más fácil ayudarte de esa manera.

Answer 2

Lo más probable es que el problema sea que la concesión se realizó a través de un rol. Los privilegios otorgados a un usuario no están disponibles en el procedimiento almacenado de derechos del definidor (el predeterminado).

En SQL Developer, es relativamente fácil verificar que este es el problema. Si ejecuta el comando

SET ROLE none

y luego ejecute la instrucción SELECT, esperaría que obtuviera el mismo error ORA-00942.

Suponiendo que ese sea el caso, la solución generalmente sería pedirle a los propietarios de las tablas en el esquema YYY que otorguen acceso a las tablas directamente a usted en lugar de otorgar acceso a través de un rol. Salvo eso, puede definir su procedimiento almacenado como un procedimiento almacenado de derechos del invocador agregando AUTHID CURRENT_USER a la declaración. Eso significa que la persona que llama del procedimiento necesitaría tener acceso a los objetos subyacentes, pero permitiría que sus procedimientos hagan uso de los privilegios otorgados a través de un rol.

Si desea crear un procedimiento almacenado de derechos del invocador, también deberá consultar el nombre de la tabla utilizando SQL dinámico para posponer la verificación de privilegios para el tiempo de ejecución. Entonces tendrías algo como

CREATE OR REPLACE PROCEDURE PRC_SOMESP 
  AUTHID CURRENT_USER
AS 
  l_cnt pls_integer;
BEGIN
  EXECUTE IMMEDIATE ''SELECT COUNT(*) FROM yyy.TableA'' INTO l_cnt;
END PRC_SOMESP;

si deseaba un procedimiento almacenado de derechos del invocador que consultara la tabla TableA en el esquema XXX.

Answer 3

Solo tuve el mismo problema. No soy un administrador de bases de datos, pero la forma en que se me explicó es "Lo básico es que sus privilegios personales de rol no están en vigor dentro del procedimiento almacenado".

Me recomendaron calificar el nombre del SP con el propietario de las tablas, así:

CREATE OR REPLACE PROCEDURE yyy.PRC_PROC1( PARAMETERS... ) etc

Esto funcionó en mi caso en mi entorno de desarrollo. Mi entorno solo tiene un espacio de nombres, por lo que no estoy seguro de que esto resuelva la pregunta del OP, pero espero que ayude a avanzar en este tema para las próximas 18.000 personas que busquen esta pregunta ;-).

Además, cuando ponga mi SP en producción, tendré que eliminar el calificador y nuestro software de instalación se ejecutará crear bajo la autoridad adecuada.