Can I prevent execute immediate from inserting on a read-only user

Question

I have a read-only user that has to have the execute privileges to specific packages. These packages sometimes use execute immediate to insert values into tables.

I can see why it was built this way, however I need the package to throw an Insufficient Privileges error instead of just executing the modifying statements.

Is it possible to change the behaviour or build a workaround without changing the executed packages?

So read only user has:

GRANT SELECT ON table to READ_ONLY_USER;
GRANT EXECUTE, DEBUG ON package to READ_ONLY_USER;

Package contains:

query = 'INSERT INTO table VALUES (value)';
execute immediate query;

And I need an error when the user executes the package.

Answer 1

Check the following example. Shortly, keyword is AUTHID CURRENT_USER while creating that PL/SQL program unit.

Connected as MIKE (who owns table and procedure and grant SCOTT privileges to use them):

SQL> show user
USER is "MIKE"
SQL>
SQL> create table test (id number);

Table created.

SQL> create or replace procedure p_test
  2    authid current_user
  3  is
  4  begin
  5    execute immediate 'insert into mike.test values (1)';
  6  end;
  7  /

Procedure created.

SQL> exec p_test;

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID
----------
         1

SQL> grant select on test to scott;

Grant succeeded.

SQL> grant execute on p_test to scott;

Grant succeeded.

SQL>

Connected as SCOTT:

SQL> show user
USER is "SCOTT"
SQL>
SQL> select * From mike.test;

        ID
----------
         1

SQL> exec mike.p_test;
BEGIN mike.p_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "MIKE.P_TEST", line 5
ORA-06512: at line 1


SQL>

---

Without it, SCOTT is able to insert values into MIKE's table:

SQL> connect mike/lion@orcl
Connected.
SQL> create or replace procedure p_test
  2  is                                      --> no more authid current_user
  3  begin
  4    execute immediate 'insert into mike.test values (2)';
  5  end;
  6  /

Procedure created.

SQL> connect scott/tiger@orcl
Connected.
SQL> exec mike.p_test;

PL/SQL procedure successfully completed.

SQL> select * From mike.test;

        ID
----------
         1
         2

SQL>