我可以阻止在只读用户上插入execute immediate吗

Question

我有一个只读用户，该用户必须具有特定包的执行权限。这些包有时使用execute immediate将值插入到表中。

我可以理解为什么它是这样构建的，但我需要包抛出权限不足错误，而不仅仅是执行修改语句。

是否可以在不更改已执行的包的情况下更改行为或构建变通方法？

所以只读用户有：

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

包装包含：

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

当用户执行包时，我需要一个错误。

Answer 1

检查以下示例。很快，在创建PL/SQL程序单元时，关键字是AUTHID CURRENT_USER。

连接为MIKE(拥有表和过程并授予SCOTT使用它们的权限(：

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>

连接为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>

---

没有它，SCOTT可以将值插入MIKE的表：

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>