Re: "insufficient privileges" error when running Java Stored procedure in a different schema

Denny Wong-Oracle

I am having a "insufficient privileges" error when running Java Stored procedure in a different schema, see details below.  I am not sure what privileges are missing (I already granted the EXECUTE privilege), any suggestion?  - Thanks.

Define a simple java class and deploy it as Java Stored Procedure for testing:

Schema: USER1

package test;

public class HelloWorld {

  public HelloWorld() {

    super();

  }

  public static String hello () {

    return "HELLO";

  }

}

CREATE OR REPLACE FUNCTION HELLO RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'test.HelloWorld.hello() return java.lang.String';

grant execute on HELLO to USER2

Test the Java Stored Procedure via PL/SQL Function Call (within same schema):

Schema: USER1

SET SERVEROUTPUT ON

DECLARE

  v_Return VARCHAR2(200);

BEGIN

  v_Return := USER1.HELLO;

  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

END;

anonymous block completed

v_Return = HELLO

Test the Java Stored Procedure via PL/SQL Function Call in a different schema:

Schema: USER2

SET SERVEROUTPUT ON

DECLARE

  v_Return VARCHAR2(200);

BEGIN

  v_Return := USER1.HELLO;

  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

END;

Error report -

ORA-01031: insufficient privileges

ORA-06512: at "USER1.HELLO", line 1

ORA-06512: at line 4

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to change the current username or password

           without the appropriate privilege. This error also occurs if

           attempting to install a database without the necessary operating

           system privileges.

           When Trusted Oracle is configure in DBMS MAC, this error may occur

           if the user was granted the necessary privilege at a higher label

           than the current login.

*Action:   Ask the database administrator to perform the operation or grant

           the required privileges.

           For Trusted Oracle users getting this error although granted the

           the appropriate privilege at a higher label, ask the database

           administrator to regrant the privilege at the appropriate label.

Dimitar Dimitrov

You have created the function with AUTHID CURRENT_USER, which means that the function is executed with invoker's rights (but not with definer's rights). This means that the invoker must have grants (either direct or via roles) on all the objects used/accessed within the function. In your case the user USER2 has not been granted with EXECUTE right on the Java class/source of the test.HelloWorld class, which causes the ORA-01031 exception. You should either create the function without AUTHID CURRENT_USER (i.e. with AUTHID DEFINER, which is default, if you do not have any special reason to use AUTHID CURRENT_USER) or grant EXECUTE right on JAVA SOURCE of the test.HelloWorld class to USER2.

Dimitar

dvohra21

As suggested

*Action:   Ask the database administrator to perform the operation or grant

           the required privileges.

Login as SYS AS SYSDBA and grant permissions to USER1.

Denny Wong-Oracle

I did perform this grant in SYS account, but still got the same error:

Login as SYS AS SYSDBA

grant execute on USER1.HELLO to USER2

However, this works.

grant execute any procedure to USER2

But this grant is too powerful and is not recommended.

Thanks.

Timo Hahn

I'm sure you get the right answer to this question in the database forum.

Timo

Dimitar Dimitrov

You have created the function with AUTHID CURRENT_USER, which means that the function is executed with invoker's rights (but not with definer's rights). This means that the invoker must have grants (either direct or via roles) on all the objects used/accessed within the function. In your case the user USER2 has not been granted with EXECUTE right on the Java class/source of the test.HelloWorld class, which causes the ORA-01031 exception. You should either create the function without AUTHID CURRENT_USER (i.e. with AUTHID DEFINER, which is default, if you do not have any special reason to use AUTHID CURRENT_USER) or grant EXECUTE right on JAVA SOURCE of the test.HelloWorld class to USER2.

Dimitar

Denny Wong-Oracle

Yes, you are right.  In the loadjava call, we need to specify the "-grant USER2" option, which wasn't obvious in the first place.