Re: Oracle 11g: Java Stored Procedures - ORA 1031

On Jan 8, 11:19 am, Thomas Olszewicki <Thom..._at_cpas.com> wrote:
> Working environment: Oracle 10.2.0.2 , 10.2.0.3 on several OS
> platforms
> Nor working environment: Oracle 11.1.0.6 on Windows XP Pro.
>
> The problem:
> JSP(Java Stored Procedure) and Wrapper PL/SQL package are own by user
> Schema1.
> User Schema1 Grants EXECUTE on Wrapper PL/SQL package to user Schema2.
> User Schema2 can execute pure PL/SQL function from Wrapper PL/SQL
> package
> on both Oracle 10g and Oracle 11g.
>
> User Schema2 cannot execute function using JSP on Oracle 11g.
> (Error ORA - 01031 -...)
>
> Please find below a working sample of the code to reproduce this
> problem.
> It works on Oracle 10g, but it fails on Oracle 11g on my environment.
> I suspect, I'm missing some privileges to be granted to User Schema2,
> but I cannot find any relevant documentation.
> I've experimented with several roles and/or privileges for user
> Schema2.
> But no success so far.
>
> The Question:
> Which privilege should I grant to user Schema2 to run successful JSP
> own by user Schema1.
> Which chapter of Oracle documentation describes this new requirement.
>
> The Code:
> --
> -- Connected as DBA user with privs to create other users;
> --
>
> -- Create 2 users (schemas) able to create and run packages.
> --
> CREATE USER Schema1 IDENTIFIED BY sqll;
> GRANT CREATE SESSION TO Schema1;
> GRANT CREATE ANY PROCEDURE TO Schema1;
> GRANT ALTER ANY PROCEDURE TO Schema1;
> GRANT EXECUTE ANY PROCEDURE TO Schema1 WITH ADMIN OPTION;
>
> CREATE USER Schema2 IDENTIFIED BY sqll;
> GRANT CREATE SESSION TO Schema2;
> GRANT CREATE ANY PROCEDURE TO Schema2;
>
> -- Connect as first new user: create Java source and package.
> --
> CONNECT Schema1/sqll@<...>;
> create or replace and compile java source named TestTimeClass as
>   import java.util.Date;
>   public class TestTimeClass
>     {
>       public static long getTime() {
>       Date dCurrent = new Date();
>       long nCurrent = dCurrent.getTime();
>       return nCurrent;
>       }
>     }
> /
>
> create or replace package TESTJavaPackageCall AUTHID CURRENT_USER is
>   FUNCTION TestTime RETURN NUMBER;
>   FUNCTION TestNum RETURN NUMBER;
> end;
> /
>
> create or replace package body TESTJavaPackageCall is
>   FUNCTION TestTime RETURN NUMBER AS
>     LANGUAGE JAVA NAME 'TestTimeClass.getTime() return long';
>   FUNCTION TestNum RETURN NUMBER AS
>    BEGIN
>      RETURN TO_NUMBER(TO_CHAR(SYSDATE,'SSSS'));
>    END;
> end;
> /
>
> -- Test run new package.functions
> SELECT TESTJavaPackageCall.TestTime FROM DUAL;
> SELECT TESTJavaPackageCall.TestNum FROM DUAL;
>
> -- GRANT Execute on new package to second user:
> GRANT EXECUTE ON TestJavaPackageCall TO Schema2;
>
> -- Connect as second user:
> CONNECT Schema2/sqll@<...>;
>
> SELECT Schema1.TESTJavaPackageCall.TestNum FROM DUAL;
> SELECT Schema1.TESTjavaPackageCall.TestTime from dual;
> -- At this point Oracle 10g will return correct results.
> -- On Oracle 11g only function TestNum will return a value.
> -- Call to function TestTime will result in ORA - 01031 error.
>
> Thank you
> Thomas Olszewicki
> CPAS Systems Inc.

Hi,

You need needs to grant execute on the underyling Java class; the 10g behavior was a laisser-aller that we fixed in 11g.

Kuassi http://db360.blogspot.com Received on Wed Jan 09 2008 - 16:24:53 CST