問題:

是個老問題了, 總結(jié)下兩種解決方法;
如果userB的存儲過程中訪問了userA中的表, 編譯時會提示ORA-00942 表不存在,或ORA-01031 無權(quán)限,
即使userB有DBA權(quán)限或者通過role權(quán)限也不行!

原因:

ORACLE存儲過程中是不能繼承非顯式授權(quán);
在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);

解決辦法:

方法一:

也是最常見的解決辦法, 顯式授個權(quán). grant select ,insert on userA.tabname to userB;

方法二:

用動態(tài)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>