存储过程中insert其它用户的表，编译提示无权限的处理

 

背景：某个用户下(假定A)的存储过程要调用另外用户(假定B)的表，在SYS下通过显式GRANT权限（具体如下）对A授权。

 

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DECTEMP1 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DECTEMP2 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DECTEMP3 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DECTEMP4 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DECTEMP5 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DUDECTEMP1 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DUDECTEMP2 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on dg_write.DCRASS_DUDECTEMP3 to oper;

 

 

 

故障现象：A中的存储过程编译提示权限错误

 

Error: PL/SQL: ORA-01031: insufficient privileges

 

通过模糊授权不管用，最后对B用户授予DBA后用B用户登录对A授权后A中的存储过程即可正常编译。

 

SQL> grant dba to dg_write;

 

Grant succeeded

 

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DECTEMP1 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DECTEMP2 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DECTEMP3 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DECTEMP4 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DECTEMP5 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DUDECTEMP1 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DUDECTEMP2 to oper;

 

grant DELETE,INSERT,SELECT,UPDATE on DCRASS_DUDECTEMP3 to oper;

 

 

看来SYS也不是万能的，也或许是ORACLE的一个BUG.

 

最后别忘记REVOKE B的DBA权限：

 

SQL> revoke dba from dg_write;

 

Revoke succeeded