How to make a cx_oracle connection encrypted using ssl?-CodePudding

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()

CodePudding user response：

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)

CodePudding user response：

The easiest way to do this is to use the Oracle native network encryption as is explained in the cx_Oracle manual: Securely Encrypting Network Traffic to Oracle Database

Oh and now that we are up to this, be smart and connect to a service name and not to a sid. Currently many databases run as pluggable databases and using the sid would bring you into the root container and not where your application has it's tables.

In order to use the sqlnet.ora, make sure to define the TNS_ADMIN environment variable such that it points to a directory where you put the sqlnet.ora file and optionally also the tnsnames.ora file in.