关于ORACLE存储过程中不能继承非显式授权问题

问题:

是个老问题了, 总结下两种解决方法;
如果userB的存储过程中访问了userA中的表, 编译时会提示ORA-00942 表不存在,或ORA-01031 无权限,
即使userB有DBA权限或者通过role权限也不行!

原因:

ORACLE存储过程中是不能继承非显式授权;
在mos中有Doc说明了这事:
An Example to Show One Cause of a "ORA-00942: table or view does not exist" Error Within a Stored Procedure (Doc ID 391068.1);

解决办法:

方法一:

也是最常见的解决办法, 显式授个权. grant select ,insert on userA.tabname to userB;

方法二:

用动态SQL 比如:

 insert into u1.t1 values(sysdate);  

改为

execute immediate 'insert into u1.t1 values(sysdate)';

示例:

SQL> grant connect,resource to u1 identified by pwd1;

Grant succeeded.

SQL> grant connect,resource to u2 identified by pwd2;

Grant succeeded.


SQL> conn u1/pwd1 
Connected.
SQL> create table t1 (d date);

Table created.

SQL> conn / as sysdba
Connected.

SQL> create role ro ;

Role created.


SQL>  grant select ,insert on u1.t1 to ro;

Grant succeeded.

SQL> grant ro to u2;

Grant succeeded.


SQL> conn u2/pwd2
Connected.
SQL> 
SQL> select * from u1.t1;

no rows selected

SQL> insert into u1.t1 values(sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from u1.t1;

D
-------------------
2021-03-24 15:20:19


SQL> 
SQL> create or replace procedure proa  authid CURRENT_USER is
  2  begin
  3    insert into u1.t1 values(sysdate);
  4    commit;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE PROA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: SQL Statement ignored
3/18     PL/SQL: ORA-00942: table or view does not exist
SQL> create or replace procedure prob authid CURRENT_USER is
  2  begin
  3    execute immediate 'insert into u1.t1 values(sysdate)';
  4    commit;
  5  end;
  6  /

Procedure created.

SQL> exec prob;

PL/SQL procedure successfully completed.

SQL> exec prob;

PL/SQL procedure successfully completed.

SQL> select * from u1.t1;

D
-------------------
2021-03-24 15:41:50
2021-03-24 15:43:28
2021-03-24 15:43:30

15:43:36 SQL>