問題:
是個老問題了, 總結(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>
