Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

UTL_HTTP, SSL, Wallets, and certificate authentication

Jason_(A_Non)Mar 19 2018 — edited May 22 2018

I'm stuck on an issue regarding wallets and certificates and SSL negotiation so seeking outside input to point out what I'm missing.

Background:

Remote_system calls web service on app_server, which routes message to database_server for additional processing.  Once done, database_server makes utl_http call back to remote_system web service to return results.  Communications between remote and our system are currently HTTP.

Improvement:

New functionality is being added that does same round-trip, but it should use HTTPS instead.  The twist is that remote_system needs our client certificate as part of their security to establish a SSL connection.

I've modified the existing PL/SQL code that handles the UTL_HTTP calls to add in

UTL_HTTP.set_wallet('file:/oracle/wallet');

before the existing code that does a

utl_http.begin_request(...);
utl_http.set_header(...);

I've granted the authority to use the wallet via

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'acl_file.xml',
    principal    => 'MYUSER',
    is_grant     => TRUE,
    privilege    => 'use-client-certificates');
   -- assign the wallet with all imported certificates:
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
     acl         => 'acl_file.xml',
     wallet_path => 'file:/oracle/wallet/');
END;
/

From the OS, I've created the wallet and imported the certificates up the chain from the certificate I've been provided

orapki wallet create -wallet /oracle/wallet -pwd Dave -auto_login
orapki wallet add -wallet /oracle/wallet -trusted_cert -cert "DigiCertGlobalRootCA.crt" -pwd Dave
orapki wallet add -wallet /oracle/wallet -trusted_cert -cert "DigiCertCA.crt" -pwd Dave

When I try to import the provided certificate the app_server uses I end up with

oracle@dbserver: orapki wallet add -wallet /oracle/wallet -user_cert -cert "/oracle/wallet/star_site_location.crt" -pwd Dave
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
PKI-04006: No matching private key in the wallet.
Could not install user cert at/oracle/wallet/star_site_location.crt
Please add all trusted certificates before adding the user certificate

I didn't find anything more on the PKI-04006 error other than to install the private key.  I've been provided only the public key, given this certificate was requested from the app_server.

I stumbled across this post

though I'm not sure what to do if I ask for and receive the p12 file as the last post is not clear to me.

Is that the right path, if so, how do I implement what is suggested?  If it is not right for my situation, what is the right path?  As I stated, I'm trying to use the trusted/user certificate from the app_server, which are used in the remote_system to app_server web service SSL communications, when going from the database_server to the remote_system for a SSL web service call.

Thanks

Comments

sb92075
912342 wrote:
Hi,

I'm trying to make heterogeneus connection, but when edited listener.ora return this error:

TNS-01201: Listener cannot find executable /app/oracle/product/11.2.0/db_home2/bin/oracle for SID ORCL

Any suggestions?
(PROGRAM=dg4odbc)
give fully qualified pathname for PROGRAM

PROGRAM=/u01/app/oracle/whatever/dg4odbc
Zakr
* (PROGRAM= /opt/progress/datadirect/Connect_for_ODBC_70/dg4odbc)*


[root@localhost admin]# lsnrctl start

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 13-FEB-2012 10:14:51

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                13-FEB-2012 10:14:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))
The listener supports no services
The command completed successfully
The listener supports no services
this is normal?

Edited by: 912342 on 13/02/2012 10:20

Edited by: 912342 on 13/02/2012 10:20

Edited by: 912342 on 13/02/2012 10:20
Pierre Forstmann
Yes, you may need to wait up to 1 minute for database instance automatic registration. If you don't want to wait, you need to run for each database instance:
alter system register;
Richard Harrison .
Hi,
What is your ORACLE_HOME?

is it

/home/oracle/app/oracle/product/11.2.0/dbhome_2

or

/app/oracle/product/11.2.0/dbhome_2

????

The listener log implies the first one but the parameter file implies the 2nd one?

Cheers,
Harry
Zakr 
[root@localhost admin]# echo $ORACLE_HOME 
/home/oracle/app/oracle/product/11.2.0/dbhome_2

[root@localhost admin]# lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 13-FEB-2012 11:10:36

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                13-FEB-2012 11:08:14
Uptime                    0 days 0 hr. 2 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
I changed the oracle_home on listener ... but the service postgresql does not appear yet.
Richard Harrison .
Hi again,
The top comment line of your listener.ora implies it is located in db_home1 but you are starting the listener from dbhome2. If there is no config at all then a default config will be started up. This looks like what might be happening make sure the listener.ora is located in the correct ORACLE_HOME.

Cheers,
Harry
Zakr
thanks all... this solved..

create public database link pt connect to "postgres" identified by "postgres" using 'PG_SERVICE';

but now.. when create the dblink.. and try select.. return this error.
missing some lib?


select * from table_x@pt
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-28541: Error in HS init file on line 18.
ORA-02063: preceding 2 lines from PT


initPOSTGRESQL.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

# HS init parameters
#
HS_FDS_CONNECT_INFO = "PostgreSQL Wire Protocol"
HS_FDS_TRACE_LEVEL = ON
HS_LANGUAGE = "BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
HS_NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS"
HS_OPEN_CURSORS = 200
HS_FDS_SHAREABLE_NAME = /opt/Connect_for_ODBC_70/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/opt/Connect_for_ODBC_70/odbc.ini
set ODBCINSTINI=/opt/Connect_for_ODBC_70/odbcinst.ini
set LD_LIBRARY_PATH = /opt/Connect_for_ODBC_70/lib:
/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib:
/lib:/usr/lib:/etc)
set PATH=/opt/Connect_for_ODBC_70/lib:/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib:
/lib:/usr/lib
#
# Environment variables required for the non-Oracle system
#
#set [envvar]=[value]
Richard Harrison .
Hi again,
Is LD_LIBRARY_PATH all on one line or spread over 3 lines it looks like it is 3 lines that should all be joined together?

Cheers,
Harry
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 19 2018
Added on Mar 19 2018
#database-security, #database-security-general
15 comments
7,329 views