Authentication with public keys and cx_Oracle using Python

Question

I've Googled a bit but I haven't found any substantial results. Is it possible to use key-based authentication to connect to an Oracle server using Python? My objective is to be able to automate some reporting I'm doing with Python without having to store a username/password anywhere in the server.

Terrie

Note sure, but this may help - using Oracle Wallet: dbaportal.eu/?q=node/126 – Earl

Answer 1

One possible solution is to implement Oracle Wallet. Creating an Oracle Wallet entry involves having:

• a tnsname resolution name established for the said instance
• a username and password

Example: The Oracle sid I'm working with is named ORCL, the user I have to connect with is named my_user. In your tnsnames.ora file you already have an entry that resolves the ORCL service name/sid, create one more with exactly the same parameters:

#initial local name entry:
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = my_ip)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

#create an additional local name entry:
ORCL_MY_USER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = my_ip)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

After the new entry resolves successfully, create the oracle wallet entry for the ORCL_MY_USER local name. This new local name you're going to use in your python script to connect without providing or hard coding a password in it.

Example:

import cx_Oracle

db = cx_Oracle.connect("/@ORCL_MY_USER")

cursor = db.cursor()

r = cursor.execute("SELECT username, password, account_status, default_tablespace, temporary_tablespace from dba_users order by username")

for username, password, account_status, default_tablespace, temporary_tablespace in cursor:

    print username, '\t', password, '\t', account_status, '\t', default_tablespace, '\t', temporary_tablespace

Contraband

Thank you. I did end up implementing a very similar solution. I like your idea of separating the Wallet user's SID from the "normal" one. One question though, wouldn't you need to specify the Oracle Wallet location in sqlnet.ora? – Terrie

But wouldn`t anyone be able to connect to the database using this code. What if you want only some people to be able to do that, as in using publc key/private key model. – Gerson

Answer 2

Yes, you do have to specify Oracle wallet's location in sqlnet.ora.

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/product/11.2.0.2/dbhome_1/owm/wallets/oracle)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

From my understanding, the Oracle wallet default location is under $ORACLE_HOME/owm/wallets/.

Selfdeprecating