Last week we tested Oracle UTL_DBWS package and noticed some handicaps. Calling web service from Oracle database server have some unsecure issues for auditing and database security.
- First of all you must install the web service which is not "default=enable" option while installing the 11G database software. So It means Oracle doesnt suggest UTL_DBWS packages If its not necessary. You have to download latest copy of dbws-callout-utility-10131.zip from Oracle Technology Network (OTN). However Oracle software installation creating other UTL_* web services and giving their execute privileges to PUBLIC.
- Important note: Its possible to install UTL_DBWS with Oracle Database 10g software. So we have to revoke execute privilege from public. Be cautious when revoing privileges granted to PUBLIC [ID 247093.1]
- conn / as sysdba
- Revoke execute on UTL_DBWS from PUBLIC;
- compile invalid objects with utlrp.sql
- Oracle reffered installing this service into user defined schema account instead of POWER USER SYS account. So you can call web service only with this user and call java packages from this user. Bu another handicap is here; Even you install the utl_dbws under the user account you have to give "CREATE PUBLIC SYNONYM" system privilege to schema account. Creating public synonym privilege is bring sharing schema data to another users even they dont have privilege to see data.
- Before you install the web service you have to load java publisher to database. For 11gR2 and 11gR1 version databases are using latest Java Publisher 10.2 and you can download it also from OTN.
- Public granted object counts is increase 23886 to 29537. Loading Java publisher create approximately 6000 objects. These objects are synonyms ,classes,resources and interfaces.
- You must set the Shared pool size and java pool size for using UTL_DBWS.
INSTALLATION
SQL> desc sys.utl_dbws
ERROR:
ORA-04043: object sys.utl_dbws does not exist
SQL>
SQL>
SQL> SELECT owner, status, count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE='JAVA CLASS' GROUP BY owner, status;
OWNER STATUS COUNT(*)
------------------------------ ------- ----------
MDSYS VALID 456
SYS VALID 20285
EXFSYS VALID 47
ORDSYS VALID 1871
SQL> show parameter SHARED_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL>
SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SQL>
SQL> alter system set SHARED_POOL_SIZE=132M scope=both;
System altered.
SQL> alter system set JAVA_POOL_SIZE=80M scope=both;
System altered.
SQL> show parameter JAVA_POOL_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 80M
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
********************************************************************************************************************************************
[oracle@server ~]$ mv /u01/app/oracle/product/11.2.0/db_1/sqlj /u01/app/oracle/product/11.2.0/db_1/sqlj.org
[oracle@server ~]$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/sqlj.org/
total 4
drwxr-xr-x 2 oracle oinstall 4096 Dec 21 2011 lib
[oracle@server ~]$
[oracle@server ~]$ ls -ltr /u01/app/oracle/product/11.2.0/db_1/sqlj.org/lib/
total 4828
-rw-r--r-- 1 oracle oinstall 438097 Aug 13 2010 runtime12.jar
-rw-r--r-- 1 oracle oinstall 438097 Aug 13 2010 runtime12ee.jar
-rw-r--r-- 1 oracle oinstall 4053906 Aug 13 2010 translator.jar
[oracle@server ~]$
[oracle@server ~]$
[oracle@server ~]$
********************************************************************************************************************************************
[oracle@server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 10:51:31 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server ~]$
********************************************************************************************************************************************
[oracle@server ~]$ unzip dbws-callout-utility-10131.zip sqlj\* -d $ORACLE_HOME
Archive: dbws-callout-utility-10131.zip
creating: /u01/app/oracle/product/11.2.0/db_1/sqlj/
creating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/utl_dbws_decl.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/utl_dbws_body.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/dbwsclientws.jar
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/dbwsclientdb102.jar
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/dbwsclientdb11.jar
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/dbwsa.jar
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/dbwsclientdb101.jar
********************************************************************************************************************************************
[oracle@server ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 10:55:49 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter user sys identified by "password";
User altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
********************************************************************************************************************************************
Oracle SQLJ Translator and Oracle JPublisher Downloads
JPublisher 10g Release 10.2
********************************************************************************************************************************************
[oracle@server ~]$ unzip jpub_102.zip -d $ORACLE_HOME
Archive: jpub_102.zip
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/sqljutl.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/translator.jar
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/lib/runtime12.jar
creating: /u01/app/oracle/product/11.2.0/db_1/sqlj/bin/
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/bin/README.txt
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/bin/jpub.c
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/bin/jpub
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/bin/jpub.exe
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/README.txt
creating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/
creating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/Booleans.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/Indexby.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/Inherit.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/MyRationalC.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/MyRationalO.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/MyRationalO8i.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/PlsqlType.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/README.txt
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/Rational.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestBooleans.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestCallin.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestIndexby.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestInh.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestInstall.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestInstallCreateTable.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestInstallJDBC.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestQuery.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestMyRationalC.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestMyRationalO.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestMyRationalO8i.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestPlsqlType.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestRationalO.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TestRationalP.java
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/WrDbmsUtil.sql
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/connect.properties
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/jpub.properties
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/demo/jpub/TemperatureService.wsdl
creating: /u01/app/oracle/product/11.2.0/db_1/sqlj/doc/
inflating: /u01/app/oracle/product/11.2.0/db_1/sqlj/doc/faq.html
[oracle@server ~]$
[oracle@server ~]$
**************************************************************************************************
[oracle@server ~]$ cd /u01/app/oracle/product/11.2.0/db_1/sqlj/bin/
[oracle@server bin]$
[oracle@server bin]$ chmod +x jpub
[oracle@server bin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/sqlj/bin
[oracle@server bin]$
CREATE USER SCOTT IDENTIFIED BY "tiger" DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP;
grant CREATE PUBLIC SYNONYM to scott;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;
select * from dba_sys_privs where GRANTEE='SCOTT';
SELECT grantee, granted_role FROM dba_role_privs where GRANTEE = 'SCOTT';
****************************************************************************************************
[oracle@server bin]$ cd $ORACLE_HOME/sqlj/lib
[oracle@server lib]$ pwd
/u01/app/oracle/product/11.2.0/db_1/sqlj/lib
[oracle@server lib]$
[oracle@server lib]$
****************************************************************************************************
[oracle@server lib]$ loadjava -u scott/tiger -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar >& loadjava.txt
[oracle@server lib]$
[oracle@server lib]$
****************************************************************************************************
lasses Loaded: 4061
Resources Loaded: 81
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 63
Classes skipped: 0
Synonyms Created: 4061
Errors: 0
****************************************************************************************************
[oracle@server lib]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:20:29 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @utl_dbws_decl.sql
Package created.
SQL> @utl_dbws_body.sql
Package body created.
Grant succeeded.
SQL> desc utl_dbws
PROCEDURE ADD_PARAMETER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
XML_NAME VARCHAR2 IN
Q_NAME VARCHAR2(4096) IN
P_MODE VARCHAR2 IN
FUNCTION CREATE_CALL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
FUNCTION CREATE_CALL RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PORT_NAME VARCHAR2(4096) IN
OPERATION_NAME VARCHAR2(4096) IN
FUNCTION CREATE_SERVICE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_NAME VARCHAR2(4096) IN
FUNCTION CREATE_SERVICE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
WSDL_DOCUMENT_LOCATION URITYPE IN
SERVICE_NAME VARCHAR2(4096) IN
FUNCTION GET_IN_PARAMETER_TYPES RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_LOCAL_PART RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2(4096) IN
FUNCTION GET_NAMESPACE_URI RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2(4096) IN
FUNCTION GET_OPERATIONS RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PORT VARCHAR2(4096) IN
FUNCTION GET_OUTPUT_VALUES RETURNS TABLE OF ANYDATA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_OUT_PARAMETER_TYPES RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION GET_PORTS RETURNS TABLE OF VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
FUNCTION GET_PROPERTY RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
FUNCTION GET_RETURN_TYPE RETURNS VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
FUNCTION INVOKE RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
REQUEST XMLTYPE IN
FUNCTION INVOKE RETURNS ANYDATA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
INPUT_PARAMS TABLE OF ANYDATA IN
PROCEDURE RELEASE_ALL_SERVICES
PROCEDURE RELEASE_CALL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
PROCEDURE RELEASE_SERVICE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SERVICE_HANDLE NUMBER IN
PROCEDURE REMOVE_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
PROCEDURE SET_HTTP_PROXY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HTTPPROXY VARCHAR2 IN
PROCEDURE SET_LOGGER_LEVEL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LEVEL VARCHAR2 IN
PROCEDURE SET_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
KEY VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE SET_RETURN_TYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
RET_TYPE VARCHAR2(4096) IN
PROCEDURE SET_TARGET_ENDPOINT_ADDRESS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CALL_HANDLE NUMBER IN
ENDPOINT VARCHAR2 IN
FUNCTION TO_QNAME RETURNS VARCHAR2(4096)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_SPACE VARCHAR2 IN
NAME VARCHAR2 IN
SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$
[oracle@server lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:24:07 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 15:24:07 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' );
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxySet','write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','getClassLoader','');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','*','connect,resolve');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.util.PropertyPermission','*','read,write');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission','setFactory','');
Call completed.
SQL> call dbms_java.grant_permission('SCOTT','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
Call completed.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@server lib]$
ref id: Using UTL_DBWS to Make a Database 11g Callout to a Document Style Web Service [ID 841183.1]
Hiç yorum yok:
Yorum Gönder