0

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

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Barry Brierley
  • 27
  • 7
  • Are you sure you have enough privileges to create a procedure? Usually Oracle says "object doesn't exist" for a table or a view you have no rights to select from – ekochergin Jul 13 '21 at 10:08
  • Yes, I can run the following. create OR REPLACE procedure p_test is V_TEST NUMBER; begin SELECT 1 INTO V_TEST FROM dual ; end; – Barry Brierley Jul 13 '21 at 10:10

2 Answers2

0

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I do not get your last error. I am able to grant the role to the procedure without issue. – Barry Brierley Jul 13 '21 at 10:14
  • Aha, Oracle 12c. That might be MY problem (I'm on 11g). Nonetheless, what about granting privilege directly to user (instead via role)? – Littlefoot Jul 13 '21 at 10:27
  • sqlplus / as sysdba grant select on apex_180100.wwv_flow_worksheet_rpts to wms; GRANT SELECT ON apex_180100.wwv_flow_activity_log to wms; create or replace... This works (Thank you @Littlefoot). BUT, logistically will be almost impossible to do in our many production sites as we don't have sysdba. It would be really great to understand how to get this to work with the privileges I have. I don't understand the comment about named procedure vs stored procedure. Is there something clever I could do with dynamic SQL or views as a workaround? – Barry Brierley Jul 13 '21 at 10:56
  • A stored procedure IS named, it has its name. It is different from an anonymous PL/SQL block (the one that begins with `DECLARE ... BEGIN ... executable statements here ... END`. In named PL/SQL procedures, grants acquired via roles don't work and - as far as I can tell - you can't "fix" that in any other way but by granting privileges directly to user(s) who will be using them. – Littlefoot Jul 13 '21 at 11:01
0

Thanks to @Littlefoot

I used a workaround. My procedure is relatively simple, I wanted to insert into a custom table from my "problem" tables. I wanted this to be called by an hourly DB job. As I can select and insert in SQL, but not PL/SQL procedures, I used a SQL script instead of datbase procedure.

i.e I converted my package into a series of SQL statements. I stored this sql script on the server and ran as a DBA job executable.

Not ideal.

PS 'execute immediate' doesn't work either.

Barry Brierley
  • 27
  • 7