18 Haziran 2013 Salı

Callout to a Document Style Web Service



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.

  1. 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. 
  2. 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                                                                                                                                                                        
  3. 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.
  4. 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.
  5. 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: