Monthly Archives: May 2018

SQLcl – enabling TLS for JDBC thin driver

Leave a reply

This is a quick follow-up from my previous post. This time we’re going to add TLS capabilities to SQLcl. I’m going to reuse the java security file we created earlier to override the security provider’s list (/opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security).

The thing with SQLcl is there is no configuration file like in SQL Developer. No problem, all we did was adding JVM options anyway, except for adding “oraclepki.jar” to the CLASSPATH. Luckily, SQLcl ships with a recent version so we don’t need that.
To add the JVM options we can use either JAVA_TOOL_OPTIONS or _JAVA_OPTIONS. It can’t get simpler than that, can it?

$ export JAVA_TOOL_OPTIONS='-Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/btr/tns/wallet)))" -Doracle.net.authentication_services="(TCPS)" -Doracle.net.ssl_server_dn_match=false -Djava.security.properties=/opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security'

Connect using username/password but use TLS for in-flight traffic encryption:

$ sql system/********@"(DESCRIPTION=(ADDRESS=(HOST=ol7122rac-scan.localdomain)(PROTOCOL=TCPS)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=pdbrac1.localdomain)))"
Picked up JAVA_TOOL_OPTIONS: -Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/btr/tns/wallet)))" -Doracle.net.authentication_services="(TCPS)" -Doracle.net.ssl_server_dn_match=false -Djava.security.properties=/opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security

SQLcl: Release 18.1.1 Production on Tue May 29 20:51:26 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue May 29 2018 20:51:26 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL>

Or connecting using TLS authentication:

$ sql /@"(DESCRIPTION=(ADDRESS=(HOST=ol7122rac-scan.localdomain)(PROTOCOL=TCPS)(PORT=1523))(CONNECT_DATA=(SERVICE_NAME=pdbrac1.localdomain)))"
Picked up JAVA_TOOL_OPTIONS: -Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/btr/tns/wallet)))" -Doracle.net.authentication_services="(TCPS)" -Doracle.net.ssl_server_dn_match=false -Djava.security.properties=/opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security

SQLcl: Release 18.1.1 Production on Tue May 29 20:57:30 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL>

SQL Developer – enabling TLS for JDBC thin driver

8 Replies

I don’t think this is documented anywhere (and no hits on google) but it shows once again how awsome SQL Developer is. 🙂
Instead of using “jdbc:oracle:oci” for TLS enabled connections we’re going to set SQL Developer up to do just that with “jdbc:oracle:thin”. In that regard, SQL Developer is no more special than any Java application.
To keep it short I’m sticking to the basics, maybe I’ll add some variations into the comments later.
First, we need to add “OraclePKIProvider” to the JRE security provider’s list. The provider’s list is defined in “$JAVA_HOME/jre/lib/security/java.security”.
I’m going to append to it by creating a new file that I only source from SQL Developer. In my case, the next free slot is 10. Depending on your OS and JRE version the list may slightly vary.
(also make sure that the java.security file is appendable: “security.overridePropertiesFile=true”)

$ vi /opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security
security.provider.10=oracle.security.pki.OraclePKIProvider

Next, we add a few options to the sqldeveloper.conf file:

$ vi /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf
# add this to end of the config file

# specify the path to your SSO wallet file
AddVMOption -Doracle.net.wallet_location="(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/btr/tns/wallet)))"
# this is required if you want to use TLS authentication next to in-flight traffic encryption
AddVMOption -Doracle.net.authentication_services="(TCPS)"
# optionally, if you want the client to verify the server certificate
AddVMOption -Doracle.net.ssl_server_dn_match=true
# append/override JRE security config to add Oracle provider
AddVMOption -Djava.security.properties=/opt/sqldeveloper/sqldeveloper/bin/sqldev.java.security
# even SQLDev 18.1 ships with a very old version that is not compatible - just download the latest one from OTN
AddJavaLibFile /home/btr/orasec/jlib/oraclepki.jar

That’s it, we’re done.

Now you can create connections using TNS, LDAP, or Advanced. Basic/EZCONNECT does not support to specify the protocol, so it can’t be used for TLS.

Here we only use TLS for in-flight traffic encryption:

And here, we make use of TLS user authentication:

Little trick with lsnrctl

Leave a reply

Are you running RAC/GI with role separation? You’re tired of constantly switching between “oracle” and “grid” users or you don’t have access to “grid” at all?
This little trick saves me lots of time whenever I need to check the status or services details of a listener running as “grid”. I hope it serves you as well as it does serve me 🙂

This gives me the listener names running on the connected node:

$ ps -ef|grep tnslsnr
grid      3468     1  0 21:50 ?        00:00:00 /u01/app/gi12201/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
grid      3643     1  0 21:50 ?        00:00:00 /u01/app/gi12201/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid      3646     1  0 21:50 ?        00:00:00 /u01/app/gi12201/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
grid      3652     1  0 21:50 ?        00:00:00 /u01/app/gi12201/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
grid      3665     1  0 21:50 ?        00:00:00 /u01/app/gi12201/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit

By default, every listener in a GI/RAC setup has an IPC end-point with the KEY being the same as the listener name. For instance:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.56.55)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.55)(PORT=1521)))

So you can simply run lsnrctl with the connect descriptor on the command line (as oracle):

$ lsnrctl status "(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))"

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-MAY-2018 22:12:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                17-MAY-2018 21:52:41
Uptime                    0 days 0 hr. 20 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/gi12201/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ol7122rac2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=192.168.56.55)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.55)(PORT=1521)))
Services Summary...
Service "65388e79ee096b69e0533238a8c08c32.localdomain" has 2 instance(s).
  Instance "RAC11", status READY, has 2 handler(s) for this service...
  Instance "RAC12", status READY, has 2 handler(s) for this service...
Service "RAC1.localdomain" has 2 instance(s).
  Instance "RAC11", status READY, has 2 handler(s) for this service...
  Instance "RAC12", status READY, has 2 handler(s) for this service...
Service "pdbrac1.localdomain" has 2 instance(s).
  Instance "RAC11", status READY, has 2 handler(s) for this service...
  Instance "RAC12", status READY, has 2 handler(s) for this service...
The command completed successfully

Yes, this only works for listeners that run on the node you’re connected to. You can’t do it across nodes, but I still find it extremely useful.

For your convenience, you can put the connect descriptors in the TNSNAMES.ORA sourced by “oracle”:

$ cat $TNS_ADMIN/tnsnames.ora 
listener_scan1=(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-MAY-2018 22:30:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "65388e79ee096b69e0533238a8c08c32.localdomain" has 2 instance(s).
  Instance "RAC11", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.56.53)(PORT=1523))
...

It also works within the lsnrctl command line:

$ lsnrctl

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-MAY-2018 22:39:58

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
Current Listener is (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "65388e79ee096b69e0533238a8c08c32.localdomain" has 2 instance(s).
  Instance "RAC11", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCPS)(HOST=192.168.56.53)(PORT=1523))
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.53)(PORT=1521))
...

Any comments? I’m listening…

Home is where the ORACLE_HOME is

Leave a reply

Here’s a little SQL that may be helpful in certain troubleshooting situations. It can answer questions like:

  • Which ORACLE_HOME is my database running from?
  • Which server-side sqlnet.ora is used by my connection (TNS_ADMIN)?

Or more generally speaking: what’s the value of a given environment variable for my session’s server process.

with
  function get_env(var in varchar2)
  return varchar2
  is
    val varchar2(32767);
  begin
    dbms_system.get_env(get_env.var, get_env.val);
    return get_env.val;
  end;
select get_env('&var_name.') env_val from dual
/

Examples:

SQL> r
Enter value for var_name: ORACLE_HOME

ENV_VAL                                                                                                                                                                                                            
--------------------------------
/u01/app/oracle/product/ora12201                      

SQL> r
Enter value for var_name: TNS_ADMIN

ENV_VAL                                                                                                                                                                                                            
--------------------------------
/u01/app/oracle/network/admin

SQL> r
Enter value for var_name: PATH

ENV_VAL                                                                                                                                                                                                            
--------------------------------

Note, Oracle apparently intentionally redacts the value for certain environment variables, e.g. PATH.
But for the use cases above I’ve found it extremely useful…