is there anyway to connect spring boot with tcps to database?

I’m using oracle database 12c and spring boot 2.3 with data JPA. My goal is to connect with the wallet throw TCPS to the database. There is no example or guides for this on the net as I searched. Can anyone explain and show some examples for this?

Answer

How to connect to Oracle from Java?

Commonly, in the java world, the most used driver is the THIN which is a pure java driver implementation provided by oracle which uses sockets to the connection. There are other options:

1. JDBC-ODBC bridge (no longer distributed with new Java)
2. Native-API driver (OCI driver fits here),
3. Network-Protocol driver
4. Database-Protocol driver (Thin driver)

Source: https://stackoverflow.com/a/21711330/3957754

There are not other options to connect to oracle from java.

What is oracle wallet?

In short, is just an option provided by oracle to hide the user and password when some client (java, c#, oracle sql developer, agents, etc) wants to establish a connection to the database instance.

Here an example of traditional connection from spring boot in which user and password are required:

spring:
  datasource:
    url: jdbc:oracle:thin:@host:port:sid
    username: user
    password: pwd

For more details check these links:

• https://docs.oracle.com/cd/E92519_02/pt856pbr3/eng/pt/tsvt/concept_UnderstandingOracleWallet.html?pli=ul_d96e224_tsvt
• https://www.appservgrid.com/documentation111/docs/rdbms12cr1/DBIMI/to_dbimi10236_d209.htm#DBIMI10236
• https://www.microfocus.com/documentation/dataexpress/WS24/Help/GUID-CB6FAEE3-6570-4A14-8A61-885F3F578C4B.html
• https://o7planning.org/10495/oracle-wallet

How to use oracle wallet?

If user & password are not required, what is the magic? Of course there are no magic. As any other service, the alternative is a file. So if you review the following links, you will find a lot of steps to create some special files that the client(java, c#, oracle sql developer, agents, etc) should use instead user and password:

• https://www.jetbrains.com/help/datagrip/connect-to-oracle-cloud-by-using-wallets.html
• https://sumanruet.wordpress.com/2015/11/22/how-to-use-oracle-wallet/
• https://helpcenter.netwrix.com/NA/Configure_IT_Infrastructure/Oracle/Configure_Oracle_Create_Wallet.html
• http://www.dba-oracle.com/t_wallet_manager.htm
• https://community.appdynamics.com/t5/Knowledge-Base/How-do-I-configure-Oracle-Wallet-authentication-for-database/ta-p/37633

How to use the oracle wallet files?

After a lot of lectures, I found these links:

• http://oracle.ninja/using-one-client-with-multiple-oracle-wallets/
• https://babumani.blogspot.com/2020/07/configure-oracle-client-with-multiple.html
• https://oraclesean.com/blog/managing-wallets-for-multiple-oracle-autonomous-databases

In which as other lectures, one option is to use the classic file called tnsnames.ora specifying the absolute path MY_WALLET_DIRECTORY of the wallet files:

MY_AWESOME_ID= (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)
(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=oracon_frankfurtdb.atp.oraclecloud.com))
(security=(ssl_server_cert_dn="CN=adwc.eucom...")
(MY_WALLET_DIRECTORY=D:_Downloads_Oracle_DB_Client_19.3networkadminFRANKFURT)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)))

Comparing the previous with some official tnsnames.ora templates, we can see that just MY_WALLET_DIRECTORY under security do the job :

MYSID=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mydnshostname)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = MYSID)
    )
  ) 

• http://www.dba-oracle.com/t_sample_tnsnames.ora.htm
• https://docs.oracle.com/cd/B19306_01/gateways.102/b16217/a_smpfil.htm

Another links:

• Setup Oracle SSL Listener

How to use tnsnames.ora from java?

According to this, we can use tnsnames.ora directly on THIN connection passing the folder which contains the wallet files to the TNS_ADMIN parameter:

jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=/foo/bar/

or more elegantly

jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=$ORACLE_HOME/network/admin

This configuration requires extra libraries 18.3 JDBC drivers.

Advice

As you can see, a lot and crazy steps are required to implement this kind of authentication. Also is not devops compatible because manual or human tasks are required. You could use docker to automate that. Anyway is to complex and tedious this implementation.

If the attacker was able to obtain your user & password using the following ways, Why couldn’t he access the oracle wallet files?:

• illegal direct access to the production infrastructure allows him to read any file on the server: application.yml, wallet files, etc
• illegal access to the source code in which you have the credentials (very bad idea)
• access to the server trough a bug in the java application.

I advice to use the classic way: THIN with user & password but with these security dispositions:

• use environment variables to hide credentials in source code
• use some variables manager
• just database administrator should know or access to the database credentials of the production environment.
• production database should no be exposed to the world. It must be within a private network and specific clients (spring boot apps) should be able to access it.
• create specific user for specific apps. This user be able to see a minimal set of objects (schemes, tables, views, etc)
• constantly scan your source code and infrastructure to find security issues.