I have a Flask app that uses Flask-SQLAlchemy and the python-oracledb driver to interface with an Oracle database. I am attempting to run oracledb in thick mode so that I can enforce encryption between the application and the database. However, I am having difficulty confirming that the encryption has been successfully configured.
What I have done is the following:
Installed Oracle Instant Client (v23.5)
Created a sqlnet.ora file which contains the following:
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)
TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = C:\oracle\logs
TRACE_FILE_CLIENT = trace.log
Set the TNS_ADMIN environment variable to the folder containing sqlnet.ora
Initialized thick mode in my Flask app code:
oracledb.init_oracle_client(lib_dir=r"path\to\instantclient")
I can confirm that thick mode is being initialized successfully because I can print out my Instant Client version to the console. However, the trace file is not being generated in the target directory, which makes me concerned that the entire sqlnet.ora file (including encryption parameters) may not be getting read correctly.
My end goal is ensure that the encryption is configured correctly and works. I figured I could verify this by setting up a trace, but I haven't been able to generate the trace log file. Any thoughts on confirming proper loading of the sqlnet.ora file with the specified encryption parameters?
Regarding checking whether a sqlnet.ora is being read, I quickly enable net tracing on my dev box with the following ~/.sqlnet.ora entry:
adr_base=/tmp
trace_level_client=16
Make sure the adr_base directory exists before you run your app.
I then navigate to /tmp/oradiag_cjones/diag/clients/user_cjones/host_*/trace. One of the trace files shows lines like:
C:2024-10-08 08:56:49.532011 : nlstdts_trace_source:Attempted load of local pfile source /Users/cjones/.sqlnet.ora
S:2024-10-08 08:56:49.532019 : nlstdts_trace_source:Parameter source loaded successfully
You can check encryption is enabled on a connection by querying network_service_banner from v$session_connect_info, see https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#native-network-encryption
In pure python-oracledb:
import traceback
import os
import platform
import oracledb
ld = None # On Linux, pass None
if platform.system() == 'Darwin':
ld = str(os.environ.get('HOME'))+'/Downloads/instantclient_23_3'
elif platform.system() == 'Windows':
ld = r'C:\oracle\instantclient_19_23'
oracledb.init_oracle_client(lib_dir=ld)
print('Using Thick mode')
un = os.environ.get('ORACLE_USERNAME')
pw = os.environ.get('ORACLE_PASSWORD')
cs = os.environ.get('ORACLE_DSN')
with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
with connection.cursor() as cursor:
sql = """select network_service_banner
from v$session_connect_info"""
for (r,) in cursor.execute(sql):
print(r)
will give output like:
Using Thick mode
TCP/IP NT Protocol Adapter for Linux: Version 23.0.0.0.0 - Production
Encryption service for Linux: Version 23.0.0.0.0 - Production
AES256 Encryption service adapter for Linux: Version 23.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 23.0.0.0.0 - Production
In SQLAlchemy:
# Using python-oracledb in SQLAlchemy 2
import os
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text
ld = str(os.environ.get('HOME'))+'/Downloads/instantclient_23_3'
# Get credentials
un = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")
cs = os.environ.get("PYTHON_CONNECTSTRING")
# Note the first arg is different than for SQLAlchemy 1.4
engine = create_engine(f'oracle+oracledb://@',
thick_mode={"lib_dir": ld},
connect_args={
"user": un,
"password": pw,
"dsn": cs
}
)
with engine.connect() as conn:
print(connection.scalars(text(
"""select network_service_banner
from v$session_connect_info""")).fetchall())
#print(oracledb.clientversion())
This gives:
['TCP/IP NT Protocol Adapter for Linux: Version 23.0.0.0.0 - Production',
'Encryption service for Linux: Version 23.0.0.0.0 - Production',
'AES256 Encryption service adapter for Linux: Version 23.0.0.0.0 - Production',
'Crypto-checksumming service for Linux: Version 23.0.0.0.0 - Production']