Connect to Database using oracle client and kerberos with Python

Question

I have a python script which basically uses x_Oracle oracle client to connect to my database server and after connection I can run my SQL queries.

Now, as my db credentials have expired and I am forced to use kerberos auth which I have no idea about. Previously just doing the below code would work:

conn_str = u'username/[email protected]:1521/dbx1.ct.com'
    conn = cx_Oracle.connect(conn_str)
    c = conn.cursor()
    c.execute(
        "select DISTINCT ITEM_ID,  UOM,  from SMCFS93 where item = '" + item + "'")
    for row in c:
        print(row)

But now I need to use kinit to generate a ticket and I have a C:\kerberos\krb5.conf file and a C:\krb5cc_User file which I have given path location in SQL developer and then I can connect to my db server.

The method works fine if one wants to connect to DB using SQL developer but I am not able to get how to make this work in Python.

I tried creating a subprocess to enter kinit everytime and generate a ticket and tried few answers on stackoverflow but I can't connect to my db. Any suggestions on how to connect db server using kerberos will be of great help.

Answer 1

Connect to Oracle from Python using the following:

• The OCI version of Oracle's Instant Client
• Kerberos via the Windows keystore
• jaydebeapi and jpype Python libraries

Benefits:

• No need to manually enter username/password to generate a Kerberos ticket.
• Authenticate using credentials the active windows session's Windows keystore.
• No Kerberos key files to track and secure
• Other applications (SQL*Plus, SQL Developer, etc) can re-use the same Kerberos configuration
• The krbcontext Python library is entirely unnecessary. Thus, no need for Docker on a Windows machine.

Steps:

1. Get a krb5.conf for your Windows domain from your IT people (or create one)

2. Configure the Oracle Instant Client to use Kerberos authentication by creating an sqlnet.ora text file in the Instant Client's \network\admin subdirectory. File contents:

   SQLNET.KERBEROS5_CONF={Path to your Krb5.conf file}
   SQLNET.KERBEROS5_CONF_MIT=TRUE
   SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5PRE)
   SQLNET.KERBEROS5_CC_NAME=OSMSFT:
   

3. Verify that the Instant Client is correctly configured for Kerberos. The following command will connect and authenticate to the server without prompting you for username and password. (Do not replace the "/" with your username and password.)

   sqlplus.exe /@server:port/SID

4. Make sure that the Oracle Instant Client root directory is in your environment path. Otherwise the jaydebeapi.connect command will throw the following error: java.lang.UnsatisfiedLinkError: no ocijdbc19 in java.library.path

5. Connect in Python using JDBC

   args = "-Djava.class.path=" + {jdbc_jar_file_path}
   jpype.startJVM(args, convertStrings = False)
   
   connection_string = 'jdbc:oracle:oci:/@server:port:sid'
   jaydebeapi.connect(
     {jdbc_java_class_name}
   , connection_string
   , jars = {jdbc_jar_file_path}
   )
   

Answer 2

You can use krbcontext & jaydebeapi python libraries to connect to oracle database

import jaydebeapi as jj
import jpype as j
import krbcontext
    
with krbcontext.krbContext(
        using_keytab=True,
        principal=$KERBEROS_Principal,
        keytab_file=$keytab,
        ccache_file='/tmp/krb5cc'
    ):
        # create instance of JVM
        args = '-Djava.class.path=%s' % config.jdbc_jar
        jvm_path = j.getDefaultJVMPath()
        j.startJVM(jvm_path, args)
    
        conn = None
        try:
            conn = jj.connect("oracle.jdbc.OracleDriver",
                              "jdbc:oracle:thin:@//{}:{}/{}",
                              {'username': $username,
                              'password': $password,
                              'oracle.net.authentication_services': "(KERBEROS5)"},
                              jars="jdbc6.jar")
        except Exception as e:
            print(e)

From my own blog