Prev: 11g upgrade - DBUA0 instance
Next: how to increase hardnofiles & softnofiles on SUSE SLES9
From: Thomas Olszewicki on 8 Jan 2008 14:19
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.
 | 
Pages: 1
Prev: 11g upgrade - DBUA0 instance
Next: how to increase hardnofiles & softnofiles on SUSE SLES9