flaskencryptionoracle-sqldeveloperflask-sqlalchemypython-oracledb

Oracledb thick mode/encryption with flask-sqlalchemy

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:

SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = C:\oracle\logs
TRACE_FILE_CLIENT = trace.log

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?

Solution

  • 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']