Random Wits

Life is too short for a diary

Projects

$ latest projects
     ├── encrypted-files
├── binary-semaphore
├── AES
└── DES
view all

Random

$ random stuff
├── my bookshelf
    ├── resources
    └── quotes
    └── about me
say Hello

Back to Top


Fri 10 Jan 2020

Connect to an Oracle Database using kerberos with python

Tags: kerberos python docker oracle database

Previously I had written python scripts connecting to oracle database. However recently the server was upgraded with kerberos & most of the scripts were failings.

After few digging, I learnt few things about kerberos authentication. Kerberos is a network authentication protocol which uses tickets to authenticate access to services.

Table of Contents

  1. Get Host IP address
  2. Download JDBC driver
  3. Create a config file
  4. Parse config file
  5. Connect to Oracle database
  6. Kerberos
  7. Full code to use kerberos
  8. Using Docker

Get Host IP address

Connect to your host server which is hosting the oracle database. For my setup, I was using RedHat Server. Get the host IP address & use it as $HOSTNAME

$ hostname -I

Download JDBC driver

I am using Oracle thin client-side JDBC driver (ojdbc6.jar) for Oracle 12c. You can download the drivers from here.

Create a config file

Let’s create a config.json used for our script & replace $HOSTNAME, $PORT_NUMBER, $SERVICE_NAME, $USERNAME, $PASSWORD respectively.

{
    "_hostname": "$HOSTNAME",
    "_portnumber": $PORT_NUMBER,
    "_service_name": "$SERVICE_NAME",
    "_username": "$USERNAME",
    "_password": "$PASSWORD",
    "_jdbc_jar": "ojdbc6.jar",
    "_jdbc_class": "oracle.jdbc.OracleDriver",
    "_jdbc_url": "jdbc:oracle:thin:@//{}:{}/{}"
}

Parse config file

Let’s create a simple python script to parse config file

Connect to Oracle database

Let us try to connect to oracle database using jaydebeapi library.

However while running the script, you will get the following error

ORA-01017: invalid username/password; logon denied

Kerberos

To connect to Oracle database using kerberos, we need the following

  • kerberos principal
  • keytab file

Assuming that the oracle is already configured to use kerberos,

$ ssh $HOSTNAME
$ kinit
# you should get prompt like username@MYDOMAIN.com
# username@MYDOMAIN.com is the kerberos principal

Ketyab file is a binary file containing pairs of Kerberos principals and encrypted keys use to authenticate to the server. It should be provided by the IT administrator. Else you can generate it like this

$ ktutil
addent -password -p username@MYDOMAIN.com -k 1 -e RC4-HMAC
# - enter password for username -
wkt username.keytab

This will create a username.keytab file on the system. Use can verify if the file is valid

# check if existing ticket
$ klist
# remove the ticket
$ kdestroy
$ kinit -kt username.keytab username@MYDOMAIN.com

if you got no errors till these steps, you can copy the file to your test folder

Full code to use kerberos

Using Docker

As if now, krbcontext library only supports Linux like OS. So we can use Docker to run the python script on windows

FROM alpine:3.7

### 2. config
ENV WORKPATH /usr/src/project
WORKDIR $WORKPATH

### 3. setup for the $WORKPATH
COPY ./krbOracle.py $WORKPATH
COPY ./SharmaT1.keytab $WORKPATH
COPY ./ojdbc6.jar $WORKPATH
COPY ./config.json $WORKPATH

### 4. installing req libraries
RUN apk add --update \
    python3 \
    python3-dev \
    py-pip \
    build-base \
    openjdk8-jre \
  && pip3 install --upgrade pip setuptools \
  && rm -rf /var/cache/apk/*

RUN apk --update add krb5-dev

### uncomment these lines if kerberos complain following error
### error :  Clock skew too great
### set the timezone accordingly
# ENV TZ=America/New_York
# RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

### 5. set the environment
ENV JAVA_HOME /usr/lib/jvm/java-1.8-openjdk
ENV PATH $PATH:/usr/lib/jvm/java-1.8-openjdk/jre/bin:/usr/lib/jvm/java-1.8-openjdk/bin
ENV LD_LIBRARY_PATH /usr/lib/jvm/java-1.8-openjdk/jre/lib/amd64/server:/usr/lib/jvm/default-jvm/lib/amd64/jli

### 6. exports
RUN export JAVA_HOME
RUN export PATH
RUN export LD_LIBRARY_PATH

### 7. install python libraries
RUN pip3 install jaydebeapi
RUN pip3 install krbcontext
RUN pip3 install JPype1==0.6.3 --force-reinstall

### 8. run the script
ADD krbOracle.py /
CMD [ "python3", "./krbOracle.py"]

You can build & run the dockerfile

$ docker build -t tshrocks/krboracle .
$ docker run -it --rm tshrocks/krboracle

comments powered by Disqus