PL/SQL: ORA-01031: insufficient privileges 6/1 PL/SQL: SQL Statement ignored GRANT

Question

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

I'm getting the above message when trying to create a package. I can simulate with a very simple procedure and select statement. My understanding is that have SQL select access for a user does not translate to PL/SQL (package) access for the same user, and that an option is to use roles. This has not worked for me.

This is all done with the same user (not apex_180100).

Showing the issue:

This SQL works. It doesn't make sense, but proves that I can select from the tables.

SELECT 1
FROM   apex_180100.wwv_flow_activity_log l,
           apex_180100.wwv_flow_worksheet_rpts r
WHERE  l.ir_report_id IS NOT NULL
AND l.flow_id = 100
AND l.worksheet_id = r.worksheet_id
AND l.ir_report_id = r.id
AND l.flow_id = r.flow_id
AND l.step_id = r.page_id;

I granted select to a role

GRANT SELECT ON apex_180100.wwv_flow_worksheet_rpts TO PRIV_FULL_TABLE;
GRANT SELECT ON apex_180100.wwv_flow_activity_log  TO PRIV_FULL_TABLE;

I grant my role to my procedure (ultimately I will grant to my package)

GRANT PRIV_FULL_TABLE TO PROCEDURE p_test;

I get an error when creating this simple sample procedure.

create OR REPLACE procedure p_test is
V_TEST NUMBER;
begin


SELECT 1
INTO V_TEST
FROM   apex_180100.wwv_flow_activity_log l,
           apex_180100.wwv_flow_worksheet_rpts r
    WHERE  l.ir_report_id IS NOT NULL
    AND l.flow_id = 100
    AND l.worksheet_id = r.worksheet_id
    AND l.ir_report_id = r.id
    AND l.flow_id = r.flow_id
    AND l.step_id = r.page_id;

end;

PL/SQL: ORA-01031: insufficient privileges compilation error

Answer 1

Hm, there's something strange in what you are saying. Usually we grant privileges to users, not procedures.

SQL> create procedure p_test as begin
  2  null;
  3  end;
  4  /

Procedure created.

SQL> create role priv_full_table;

Role created.

SQL> grant priv_full_table to procedure p_test;
grant priv_full_table to procedure p_test
                                        *
ERROR at line 1:
ORA-00991: only MAC privileges may be granted to procedures


SQL>

---

Apart from that, if I understood you correctly, issue is exactly what you thought that solves it: privileges granted to roles won't work in named stored procedures. p_test is a named procedure:

create OR REPLACE procedure p_test is ...

which means that you'll have to grant those privileges directly to user which will be using them.