How to make a cx_oracle connection encrypted using ssl?

user_16447854 0
Viewed 1024

I am using cx_oracle module with python 3.7 version and I need to check whether the connection is encrypted. If not I need to set the ssl as true in order to make it encrypted.

Here is my piece of code to make the connection:

import cx_Oracle
dsn = cx_Oracle.makedsn(host='127.0.0.1', port=1521, sid='your_sid')
conn = cx_Oracle.connect(user='your_username', password='your_password', dsn=dsn)
conn.close()
cx-oracle python-3.x ssl oracle python
2 Answers

As you are thinking in enabling security to your connection, your first step should be to use a wallet, even before considering using ssl , and avoid using passwords. It does not matter how encrypted is your network traffic, if your passwords are visible in your Python programs. I know it is not part of the question itself, but it is a very good practice and available for cx_Oracle.

One example ( My Python programs runs in a Linux client machine which connects to an Oracle Database in Linux too using ssl )

Client Side

1.Create the wallet

mkstore -wrl "/home/myuser/wallet_directory" -create

2.Create the credential

mkstore -wrl "/home/myuser/wallet_directory" -createCredential mynetalias myuser myuserpw

Where mynetalias is an alias for my tns string connection which I will store on my tnsnames.ora file. In my example I will use the same directory where I created the wallet.

3.Create the tnsnames.ora and add the same alias used in the wallet

mynetalias =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = orclpdb1)
        )
    )

4.Create the sqlnet.ora file

WALLET_LOCATION =
    (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
            (DIRECTORY = /home/myuser/wallet_dir)
        )
    )
SQLNET.WALLET_OVERRIDE = TRUE

5.Add your TNS_ADMIN environment variable to your bash profile.

cd
echo "export TNS_ADMIN=/home/myuser/wallet_directory" >> .bashrc

If you definitely know that the database server enforces integrity and encryption, then you do not need to configure anything in the client side. However you can also, or alternatively, do so depending on your business needs. Add the following lines to the sqlnet.ora file where the wallet is located

SQLNET.CRYPTO_CHECKSUM_CLIENT = required
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

Database Side

In order to setup SSL and encryption we need to add these values to the Database sqlnet.ora file. Review your requirements and discuss the right security algorithms. In my case my database accepts connection either way ( with or without encryption ).

SQLNET.CRYPTO_CHECKSUM_SERVER = accepted
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512)
SQLNET.ENCRYPTION_SERVER = accepted
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

You might want to review these parameters here:

SQLNET Parameters

How to connect

Normal

connection = cx_Oracle.connect(dsn="mynetalias")

Pool

pool = cx_Oracle.SessionPool(externalauth=True, homogeneous=False,
                             dsn="mynetalias")
pool.acquire()

Remember that dsn must match exactly the alias used in your tnsnames.ora configured before.

Use the information provided by the view V$SESSION_CONNECT_INFO to assure your connection is encrypted ( field network_service_banner)

user_137555380
user_173156430
Related