PLSQL Grant on Tables used on a package

Question

I have two procedures one defines and uses a CURSOR with table from another schema and it gives me an error of insufficient privileges; the other one, creates a table with a dynamic SQL, referencing the same tables, but goes without error,

why so?

First procedure is as follows:

CREATE SCHEMA1.PROCEDURE proc1 AS
TYPE Record1 IS RECORD (v_one VARCHAR2(20), v_two VARCHAR2(20)); 
CURSOR procCursor RETURN Record1 IS
SELECT P.CODE, R.DESCRIPTION
FROM SCHEMA2.ROLES771 R, SCHEMA2.ROLEP773 P
WHERE ...;

BEGIN
...
END SCHEMA1.PROCEDURE;

The other one, without error is this:

CREATE SCHEMA1.PROCEDURE proc1 AS
TYPE Record1 IS RECORD (v_one VARCHAR2(20), v_two VARCHAR2(20)); 
CURSOR procCursor RETURN Record1 IS
SELECT P.CODE, R.DESCRIPTION
FROM TMP_TBL;

BEGIN
EXECUTE IMMEDIATE ('TRUNCATE TABLE TMP_TBL');
EXECUTE IMMEDIATE ('
INSERT INTO TMP_TBL (ONE, TWO) 
SELECT P.CODE, R.DESCRIPTION
FROM SCHEMA2.ROLES771 R, SCHEMA2.ROLEP773 P');
...
END SCHEMA1.PROCEDURE;

Answer 1

As you said, tables are owned by someone else (i.e. another user).

Problem you described is often result of you being granted privileges via role. Such privileges work at SQL level or in anonymous PL/SQL blocks, but won't work in named PL/SQL procedures (functions, procedures, packages, triggers).

Documentation says

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block

(yes, I know, this is 10g document; later versions say that it "builds and runs a dynamic SQL statement in a single operation" which isn't that descriptive).

Basically, that's why execute immediate runs OK.

---

What to do? Let another user grant privileges directly to you, not via role.