ORA- 942 despite having the select privilege

Here is an intriguing issue where a user was able to run a SQL statement directly or from within an anonymous PL SQL Block. But the same SQL statement fails with ORA 942 - table or view does not exist, when it is run from within a procedure.

User A was able to execute this

CREATE TABLE A.TEST AS SELECT * FROM B.TEST ;

User A was also able to execute this

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE A.TEST AS SELECT * FROM B.TEST';

END;

/

But the same user A was not able to execute this procedure.

CREATE PROCEDURE A.TEST_PROC

AS

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE A.TEST AS SELECT * FROM B.TEST';

END;

/
-- Procedure created

EXECUTE TEST_PROC;

This fails with ORA 942 - table or view does not exist. User A has select privilege on B.TEST via a role!

Oracle allows two modes of operation for executing named PL SQL - definer rights and invoker rights. By default all procedures and functions are created with definer rights. In this definer rights model, all roles are disabled during procedure compilation and execution. We were able to compile the above procedure because B.TEST is within the execute immediate block. But during the execution of this procedure, the role is disabled and therefore User A looses the access on B.TEST.

Hence, the procedure execution fails with ORA 942. Even a call to SET ROLE is not allowed within the procedure. You must remove the role dependency and give direct grants to the user, to be able to run the above procedure.

With invoker rights model, roles are enabled for PL SQL execution but not during compilation.