Oracle "table or view does not exist" from internal stored procedure

Question

the script is like this ...

I have a XXX namespace where I created some tables and some stored procedures ...

We have a YYY namespace where they created several tables ...

THEY GIVES XXX access to their tables, so when I connect to SQL Developer using the XXX connection, I can do:

 SELECT * FROM YYY.TableA 

But if I try to run the same statement from a stored procedure (either a simple stored procedure or a package), the stored procedure will not compile. This happens to many sp. Is there any other permission I should ask for? I run sp like this:

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

Procedures that do not have access to YYY tables compile well.

Thanks in advance.

After Justin Cave's answer, I try to add the sentence "AUTHID CURRENT_USER" to sp, but get the same result "table or view does not exist":

 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 

Should I check something else?

Answer 1

Most likely, the problem is that the grant was executed through a role. Privileges granted to the user are not available in the stored procedure for determining rights (by default).

In SQL Developer, it is relatively easy to verify that this is a problem. If you run the command

 SET ROLE none 

and then run the SELECT statement, I expect you to get the same error ORA-00942.

Assuming this is the case, the solution, as a rule, was to ask the table owners in the YYY schema to provide access to the tables directly to you, and not to provide access through the role. If you have forbidden this, you can define your stored procedure as a stored procedure for call rights by adding AUTHID CURRENT_USER to the declaration. This means that the caller of the procedure must have access to the underlying objects, but this will allow your procedures to use the privileges granted through the role.

If you want to create a stored call right procedure, you will also need to access the table name using dynamic SQL to defer privilege checking at runtime. This way you will have something like

 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; 

if you need a stored call right procedure by querying TableA table in schema XXX.

Answer 2

In your stored procedures in Scheme XXX , if you access tables from the YYY schema, make sure that you fully qualify them:

 select count(1) from YYY.TableA; 

Another thing to keep in mind is the casing (in case you mix upper and lower case in your Oracle identifiers).

Last: send the error you get. It will be easier for you to help you this way.

Answer 3

I had the same problem. I am not a database administrator, but, as I was explained, "the main thing is that your personal privileges in the role do not apply in the stored procedure."

I was recommended to qualify the SP name with the owner of the tables, for example:

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

This worked in my case in my dev environment. There is only one namespace in my environment, so I'm not sure if this solves the OP question, but hopefully helps move this issue forward for the next 18K people looking for this question; -).

In addition, when I put my SP into production, I will need to remove the qualifier, and our installation software will work under the appropriate authority.