Can't find table in stored function, procedure or package in Oracle

This is a strange problem:

We have rights to select a table and can select in normal sql statement. However, when we write a stored procedure or function (with or without a package), we encounter an error that says table not found.

The one solution we found was to grant that account select any table rights. Once granted, we can compile and use the stored procedure/package. However, the problem with doing that is the account can also select any table in any schema in that instance.

I found the following solution thanks to a support from the community forum:

Note:388774.1


Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.3
This problem can occur on any platform.
Symptoms
When SYSTEM user grants select on a table to another user with SQL*Plus , it works fine: SYSTEM user has the GRANT ANY OBJECT PRIVILEGE system privilege .

SQL> CONNECT system/xxx
Connected.
SQL> grant SELECT, INSERT, UPDATE, DELETE on SCOTT.TEST TO X;
Grant succeeded.
1. Under 10g, when this statement is in a stored procedure, it returns "ORA-1031 - insufficient privilege".
2. Under 9i, it returns "ORA-00942: table or view does not exist "

SQL> CREATE OR REPLACE PROCEDURE system.def_grant_test
   IS
   cursor_handle INTEGER;
   BEGIN
    cursor_handle := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_handle, 'GRANT SELECT, INSERT, UPDATE, DELETE on SCOTT.TEST TO X', dbms_sql.native );
    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
  END;
 /

Procedure created.
SQL 9i> exec system.def_grant_test
BEGIN system.def_grant_test; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SYSTEM.DEF_GRANT_TEST", line 6
ORA-06512: at line 1
SQL 10g> exec system.def_grant_test
BEGIN system.def_grant_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SYSTEM.DEF_GRANT_TEST", line 6
ORA-06512: at line 1
Cause
Generally an invokers's called procedure executes using the caller's privileges and all unqualified objects are resolved in the schema of the caller.
The privileges of the invoker are checked during runtime only for
DML statements ('insert', 'update','delete', 'update')
dynamic sql statements ('execute immediate' and 'open for using')
cursor statements ('open' and 'open for update')
DDL statements ('lock table')
parsed SQL statements using dbms_sql.parse
Solution
To implement the solution, please execute the following steps:

1. Create the procedure defined as an invoker's right procedure , with AUTHID CURRENT_USER clause:

SQL> CREATE OR REPLACE PROCEDURE system.inv_grant_test authid current_user
   IS
    cursor_handle INTEGER;
    BEGIN
     cursor_handle := DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE(cursor_handle, 'GRANT SELECT, INSERT, UPDATE, DELETE on SCOTT.TEST TO X', dbms_sql.native );
     DBMS_SQL.CLOSE_CURSOR(cursor_handle);
    END;
/

Procedure created.
SQL> exec system.inv_grant_test
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_privs where table_name='TEST' and grantee='X';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

-------- -------- ---------- -------- ---------- --- ---
X SCOTT TEST SCOTT INSERT NO NO
X SCOTT TEST SCOTT DELETE NO NO
X SCOTT TEST SCOTT SELECT NO NO
X SCOTT TEST SCOTT UPDATE NO NO