Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions
  • 7,880,711 Comments

Discussions

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

Denny Wong-Oracle
Denny Wong-Oracle Member Posts: 183
edited May 16, 2014 5:09PM in JDeveloper and ADF

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.

Tagged:

Best Answer

  • Dimitar Dimitrov
    Dimitar Dimitrov Member Posts: 919 Bronze Trophy
    edited May 16, 2014 5:07PM Accepted Answer

    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

Answers

  • dvohra21
    dvohra21 Member Posts: 14,327 Gold Crown

    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.

  • 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
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,807 Red Diamond

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

    Timo

  • Dimitar Dimitrov
    Dimitar Dimitrov Member Posts: 919 Bronze Trophy
    edited May 16, 2014 5:07PM Accepted Answer

    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

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

This discussion has been closed.