oracle创建存储过程报错权限不足-向日葵屋

oracle创建存储过程报错权限不足

使用用户a直接执行insert into c.ccc(id) values(1); 可以运行，但是放在存储过程中报错权限不足。

检查下用户a的权限

select * from user_sys_privs;
select * from role_sys_privs;
select * from user_tab_privs;

发现a的对象权限为空，角色权限中有insert any table，但是在系统权限中没有insert any table。怀疑是通过角色赋予的权限在存储过程中无效导致的。

解决方法：直接对a赋予相应的系统权限或者对象权限即可。

例:

grant insert on c.ccc to a; --赋予对象权限

其他解决方案

Scenario: Suppose that you must write a PL/SQL procedure that presents compilation errors to a developer. The procedure is to join the static data dictionary views ALL_SOURCE and ALL_ERRORS and use the procedure DBMS_OUTPUT.PUT_LINE to show a window of numbered source lines around each error, following the list of errors for that window. You want the developers to be able to run the procedure, and you want the procedure to treat each developer as the CURRENT_USER for ALL_SOURCE and ALL_ERRORS.

Solution: When you create the procedure, specify AUTHID CURRENT_USER. Grant the EXECUTE privilege to the developers who must use the procedure. Because the procedure is an IR unit, ALL_SOURCE and ALL_ERRORS operate from the perspective of the user who invokes the procedure.

简单来说，就是要准备一个拥有insert any table权限的用户b，然后用b创建存储过程
create or replace procedure b.test_190507 AUTHID CURRENT_USER
最后让a执行这个存储过程即可。