Execute procedure owned by a schema in sys-CodePudding

I have a schema called test, and it has a procedure called doAProcedure('varA'). Inside the procedure it calls a function funcA that belongs to schema test.

CREATE OR REPLACE PROCEDURE doAProcedure(tabname varchar2) AUTHID CURRENT_USER AS ....

If I log in to the db as test, I am able to complete execute doAProcedure('valA');

However, I want to support executing the procedure as SYS as well. if I execute the following command as sys, it is complaining that function funcA is not declared. execute test.doAProcedure('varA'); Not sure what needs to be fixed.

CodePudding user response：

Problem is in: AUTHID CURRENT_USER

This means that the current user must also be authorized to execute code inside the procedure.

Fix 1: Remove this part and leave only:

CREATE OR REPLACE PROCEDURE doAProcedure(tabname varchar2) AS

Fix 2: Grant execute to function funcA for SYS

grant execute on test.funcA to sys;

CodePudding user response：

This is test case; everything works OK when connected as the owner:

SQL> connect scott/tiger@pdb1
Connected.
SQL> create or replace function f_test return number is
  2  begin
  3    return 1;
  4  end;
  5  /

Function created.

SQL> create or replace procedure p_test authid current_user is
  2  begin
  3    dbms_output.put_line('Function returned ' || f_test);
  4  end;
  5  /

Procedure created.

SQL> set serveroutput on
SQL> exec p_test
Function returned 1

PL/SQL procedure successfully completed.

SQL>

Let's connect as SYS and try again:

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> exec scott.p_test
BEGIN scott.p_test; END;

*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SCOTT.P_TEST", line 1
ORA-06512: at line 1


SQL>

As you said, it won't work.

"Problem" is in Oracle 12c or later, with new security feature (see inherit privileges).

What to do? A little grant (still connected as SYS):

SQL> grant inherit privileges on user sys to scott;

Grant succeeded.

SQL> exec scott.p_test
Function returned 1

PL/SQL procedure successfully completed.

SQL>

Now it works.