Dear all,

I am getting "ORA-00942 for PL/SQL: ORA-00942: table or view does not exist". I check the table exists and the user has required permission on the table through roles. I understand roles are disabled in stored procedures, and permissions need to be granted directly to user for the procedures to work. However, we are required to enforce the permissions being granted to roles instead of direct grant to users. Is there a work around that will help the store procedures to work without direct grant of permissions to the users?

I have the following grant and procedure:

grant select on hr.employees to scott;
grant execute on dbms_output;

conn scott/scott

create or replace Procedure get_result
AUTHID CURRENT_USER AS
Cursor c_info is
SELECT LAST_NAME FROM hr.EMPLOYEES;
begin
For i in c_info Loop
dbms_output.put_line('User last name: '|| i.last_name);
END LOOP;
end get_result;
/

Warning: Procedure created with compilation errors.

show error
Errors for PROCEDURE GET_RESULT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/6 PL/SQL: SQL Statement ignored
4/31 PL/SQL: ORA-00942: table or view does not exist
8/9 PL/SQL: Statement ignored
8/51 PLS-00364: loop index variable 'I' use is invalid


Of course when the "grant select on hr.employees to scott" is executed, the above errors went away. Can anyone advice how do avoid the direct grant? We are required to use roles instead of direct grant.


Thanks in advance.
Unna