UTL_HTTP, secure hosts and ORA-24263 certificate errors

Security eh? It is just one of those little things that is good to have in a database. The “problem” with security is that it often adds complexity to your environment, but I’d rather have a little extra complexity than have my personal details splattered all over the dark web in some database of the Australian population.

Now I know there’s perhaps a certain hypocrisy I can be accused of in stating this from a position of having a blog, a Youtube channel and open Twitter DMs, where just about any information about is readily available to all…but can we at least all agree that security is important

A common security question that comes up on AskTom is related to UTL_HTTP (or any mechanism that reaches out from the database over the network). When I say “common question”, it is not the first question that comes up – that one is always about network ACLs. But once your ACLs are sorted out, the next security obstacle that typically arises is dealing with SSL and certificates.

Once we head into the realm of certificates and guaranteeing that a host/target is who it claims to be, UTL_HTTP is (quite rightly) very particular about initiating a communication with a remote host. For example, if you try to communicate securely with ‘www.oracle.com’ and and fire up ‘openssl’ to get the “true” name of this site you’ll find that this is not the name of the host in the certificate.

[oracle@db192 ~]$ openssl s_client -connect www.oracle.com:443
...
C = US, ST = California, L = Redwood City, O = Oracle Corporation, CN = www-cs-02.oracle.com

Thus when you are using UTL_HTTP (or UTL_SMTP etc) to communicate securely with www.oracle.com via a certificate that you have downloaded/imported into your wallet, we’re going to see “www-cs-02.oracle.com” in the certificate and respond with:

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1501
ORA-24263: Certificate of the remote server does not match the target address.
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1441
ORA-06512: at line 1

Back in 12.2, we added a capability to handle this discrepancy between the public host name and the certificate host name via a new parameter for UTL_HTTP

SQL> DESC UTL_HTTP

...
...
FUNCTION BEGIN_REQUEST RETURNS REQ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 URL                            VARCHAR2                IN
 METHOD                         VARCHAR2                IN     DEFAULT
 HTTP_VERSION                   VARCHAR2                IN     DEFAULT
 REQUEST_CONTEXT                BINARY_INTEGER          IN     DEFAULT
 HTTPS_HOST                     VARCHAR2                IN     DEFAULT
 
...
...

If you are only dealing a single target host, that is fine. You would use openssl to work out what name you want to pass for https_host and you’re done. But what if the hosts you are communicating with are many and/or dynamic. For example, you might be communicating with mail servers based on a users email domain. How can we (at runtime) automatically work out what the value for ‘https_host’ should be when initiating a conversation with UTL_HTTP?

External tables can come to the rescue here. We can take advantage of the preprocessor facilities to dynamically extract the host name using ‘openssl’.

I’ll put my hosts in a flat file, but they could be in any place where you can access them from your external table definition.

select host from my_table;

Let’s say that yields a file ‘host.dat’ like this:

[oracle@lin122 tmp]$ cat host.dat
www.oracle.com
www.outlook.com

Now I can use openssl and some parsing to extract the secure name that you want.

[oracle@lin122 tmp]$ cat ssl_check.sh
#/bin/bash
/usr/bin/cat $1 | while read HOST 
do
  /usr/bin/echo -n | /usr/bin/openssl s_client -connect $HOST:443 2>/dev/null | \
      /usr/bin/awk '/Certificate chain/ , /Server certificate/' | \
      /usr/bin/grep ' 0 s:' | /usr/bin/awk -v H=$HOST -F= '{print H","$NF}'
done

Then I can hook that up as an external table with a pre-processor.

create or replace directory tmp as '/tmp';

create table secure_host (
  name     varchar2(50),
  ssl_name varchar2(50)
)
organization external (
  type oracle_loader
  default directory tmp
  access parameters (
    records delimited by newline
    preprocessor tmp:'ssl_check.sh'
    fields terminated by ','
    missing field values are null
    (
      name     char(50),
      ssl_name char(50)
    )
  )
  LOCATION ('host.dat')
)
reject limit unlimited;

So this will

– read host.dat
– pass that file name to the script
– the script loops through and finds the secure host name
– sends host and secure host back, eg

SQL> select * from secure_host;

NAME                 SSL_NAME
-------------------- ------------------------------ 
www.oracle.com       www-cs-01.oracle.com
www.outlook.com      outlook.com

That gives you what you need to pass into UTL_HTTP and the like.