The power of Oracle Functions: Using Python connecting to an Autonomous Transacting Processing database with a wallet stored as secrets

Oracle Functions is a fully managed, multitenant, highly scalable, on-demand, functions-as-a-service platform. It’s built on enterprise-grade Oracle Cloud Infrastructure (OCI) and powered by the Fn Project open source engine. The serverless and elastic architecture of Oracle Functions means you have no infrastructure administration or software administration to perform. You don’t provision or maintain compute instances, and operating system software patches and upgrades are applied automatically. Oracle Functions ensures that your app is highly available, scalable, secure, and monitored.

This blog post retrieves Autonomous Transaction Processing database details, stored in OCI Vault secrets, and use the same details to connect to the database. Existing samples in the Conclusion provide details about the following subjects:

• How to fetch details from an OCI Vault secret

• Connecting to Autonomous Transaction Processing database using the wallets downloaded from the database directly, which are uploaded on Object Storage

As a best practice, OCI Vault can store important credentials and keys, such as database details, both in text and binary mode, with base 64 encoded fashion. When the wallet details are already stored in OCI Vault, you can fetch the secret from OCI Vault and then connect to the Autonomous Transaction Processing database.

Prerequisites for OCI Functions

Before you deploy this sample function, run the following steps of the Oracle Functions Quick Start guide for Cloud Shell:

• Set up your tenancy.

• Create application.

• Set up your Cloud Shell development environment.

List applications

When you’ve successfully completed the prerequisites, you can see your application in the list of applications with the following command:

fn ls apps

Also, ensure that the function has access to the Autonomous Transaction Processing database, using dynamic group and IAM policies.

Wallet secret details

This post isn’t about creating wallet secret in OCI Vault but fetching details from an OCI Vault secret and using them to connect to an Autonomous Transaction Processing database. The following example shows how to create a vault and create a wallet secret:

Create a vault. In the menu, select Security and then Vault. On the vault list page, click Create Vault.

Provide the vault name and details.

When the vault it active, you can view the vault details and click Create Secret in the sidebar under Secrets. Name your secret, enter a description, and choose the encryption key. By default, the vault name, unless you have any key created.

At this point, you have two choices. You can enter your key as plain text and have the Console base64 encode it for you, or you can choose base64 and enter an encoded value for your secret. When your secret is created, copy the OCID of the secret and keep it for later.

You can also create multiple secrets as shown in the following image:

Refer to an existing sample, which can help you understand how to retrieve the wallet secret details.

Serverless function that retrieves wallet secrets, decrypts the secret, and uses the details to connect to an Autonomous Transaction Processing database

This function retrieves secret values of your database connection stored in OCI Vault and uses them to connect to database. The requirements.txt needs the following commands:

• fdk: Required for Functions to work

• oci: The sdk required to connect to OCI Vault

• cx_Oracle: Required to connect to Autonomous Transaction Processing database

1. The content of text and binary secrets are returned decrypted by the function. The OCID of every secret gets the encoded text or binary content, including the following examples:

   • Fetch username

   • Fetch password

   • Fetch database URL

   
   def get_text_secret(secret_ocid):
       #decrypted_secret_content = ""
       signer = oci.auth.signers.get_resource_principals_signer()
       try:
           client = oci.secrets.SecretsClient({}, signer=signer)
           secret_content = client.get_secret_bundle(secret_ocid).data.secret_bundle_content.content.encode('utf-8')
           decrypted_secret_content = base64.b64decode(secret_content).decode("utf-8")
       except Exception as ex:
           print("ERROR: failed to retrieve the secret content", ex, flush=True)
           raise
       return decrypted_secret_content
   
   
   def handler(ctx, data: io.BytesIO=None):
   
       logging.getLogger().info("function start")
   	# ocid of the username stored as secret in OCI Vault mentioned in Figure 1
       username = get_text_secret(USERNAME_SECRET_OCID) 
       logging.getLogger().info("username : " + username)
   	# ocid of the password stored as secret in OCI Vault mentioned in Figure 1
       password = get_text_secret(PASSWORD_SECRET_OCID)
       logging.getLogger().info("password : " + password)
   	# ocid of the db_url stored as secret in OCI Vault mentioned in Figure 1
       db_url = get_text_secret(DB_URL_SECRET_OCID) 
       logging.getLogger().info("db_url : " + db_url)
   

2. Write the binary content of the secret into a temporary folder and point the database wallet temporary folder.

   
   DB_WALLET_PATH = "/tmp"
   
   def point_db_wallet_path():
       try:
           with open(DB_WALLET_PATH + '/sqlnet.ora') as orig_sqlnetora:
               newText = orig_sqlnetora.read().replace('DIRECTORY=\"?/network/admin\"',
                                                       'DIRECTORY=\"{}\"'.format(DB_WALLET_PATH))
           with open(DB_WALLET_PATH + '/sqlnet.ora', "w") as new_sqlnetora:
               new_sqlnetora.write(newText)
       except Exception as err:
           print("ERROR: failed to point db wallet path in sqlnet.ora file...", err, flush=True)
           raise
   
   def write_db_wallet_files():
       try:
           for key, value in db_wallet_dict.items():
               print("filename : ", key)
               print("ocid : ", value)
               get_binary_secret_into_file(value, os.path.join(DB_WALLET_PATH, key))
       except Exception as err:
           print("ERROR: failed to write db wallet files...", err, flush=True)
           raise
   
   def get_binary_secret_into_file(secret_ocid, filepath):
       #decrypted_secret_content = ""
       signer = oci.auth.signers.get_resource_principals_signer()
       try:
           client = oci.secrets.SecretsClient({}, signer=signer)
           secret_content = client.get_secret_bundle(secret_ocid).data.secret_bundle_content.content.encode('utf-8')
       except Exception as ex:
           print("ERROR: failed to retrieve the secret content", ex, flush=True)
           raise
       try:
           with open(filepath, 'wb') as secretfile:
               decrypted_secret_content = base64.decodebytes(secret_content)
               secretfile.write(decrypted_secret_content)
       except Exception as ex:                   
           print("ERROR: cannot write to file " + filepath, ex, flush=True)
           raise
   
   def handler(ctx, data: io.BytesIO=None):
   
   #  Post Fetching Username, Password, DB URL
       write_db_wallet_files()
       point_db_wallet_path()
   

3. Add the database wallet to the tns_admin environment, connect to database, and run a SQL to test.

   
   def handler(ctx, data: io.BytesIO=None):
   #    Post Fetching Username, Password, DB URL
   #    Post writing wallet Files
   #    Post pointing to DB Wallet     
       os.environ["TNS_ADMIN"] = DB_WALLET_PATH
       con = cx_Oracle.connect(username, password, db_url)
       logging.getLogger().info("Successfully Connected DB...")
       cur = con.cursor()
       cur.execute('Select * from departments_json')
       rows = cur.fetchall()
       logging.getLogger().info(rows)
       resp = {"connect": "true"}
   

These snippets refer to the complete code, as shown. If you’re using the latest version of OCI, have the latest pip installed in the environment using the docker file.

Complete code reference

func.py

import io
import base64
import os.path
import oci
import logging
from fdk import response
import cx_Oracle

USERNAME_SECRET_OCID = "your ocid secret value"
PASSWORD_SECRET_OCID = "your ocid secret value"
DB_URL_SECRET_OCID = "your ocid secret value"
CWALLET_SECRET_OCID = "your ocid secret value"
EWALLET_SECRET_OCID = "your ocid secret value"
KEYSTORE_SECRET_OCID = "your ocid secret value"
TRUSTSTORE_SECRET_OCID = "your ocid secret value"
OJDBC_SECRET_OCID = "your ocid secret value"
SQLNET_SECRET_OCID = "your ocid secret value"
TNSNAMES_SECRET_OCID = "your ocid secret value"
DB_WALLET_PATH = "/tmp"

db_wallet_dict = {'cwallet.sso': CWALLET_SECRET_OCID,
                  'ewallet.p12': EWALLET_SECRET_OCID,
                  'keystore.jks': KEYSTORE_SECRET_OCID,
                  'truststore.jks': TRUSTSTORE_SECRET_OCID,
                  'ojdbc.properties': OJDBC_SECRET_OCID,
                  'sqlnet.ora': SQLNET_SECRET_OCID,
                  'tnsnames.ora': TNSNAMES_SECRET_OCID
                  }


def point_db_wallet_path():
    try:
        with open(DB_WALLET_PATH + '/sqlnet.ora') as orig_sqlnetora:
            newText = orig_sqlnetora.read().replace('DIRECTORY=\"?/network/admin\"',
                                                    'DIRECTORY=\"{}\"'.format(DB_WALLET_PATH))
        with open(DB_WALLET_PATH + '/sqlnet.ora', "w") as new_sqlnetora:
            new_sqlnetora.write(newText)
    except Exception as err:
        print("ERROR: failed to point db wallet path in sqlnet.ora file...", err, flush=True)
        raise

def write_db_wallet_files():
    try:
        for key, value in db_wallet_dict.items():
            print("filename : ", key)
            print("ocid : ", value)
            get_binary_secret_into_file(value, os.path.join(DB_WALLET_PATH, key))
    except Exception as err:
        print("ERROR: failed to write db wallet files...", err, flush=True)
        raise

def get_text_secret(secret_ocid):
    #decrypted_secret_content = ""
    signer = oci.auth.signers.get_resource_principals_signer()
    try:
        client = oci.secrets.SecretsClient({}, signer=signer)
        secret_content = client.get_secret_bundle(secret_ocid).data.secret_bundle_content.content.encode('utf-8')
        decrypted_secret_content = base64.b64decode(secret_content).decode("utf-8")
    except Exception as ex:
        print("ERROR: failed to retrieve the secret content", ex, flush=True)
        raise
    return decrypted_secret_content

def get_binary_secret_into_file(secret_ocid, filepath):
    #decrypted_secret_content = ""
    signer = oci.auth.signers.get_resource_principals_signer()
    try:
        client = oci.secrets.SecretsClient({}, signer=signer)
        secret_content = client.get_secret_bundle(secret_ocid).data.secret_bundle_content.content.encode('utf-8')
    except Exception as ex:
        print("ERROR: failed to retrieve the secret content", ex, flush=True)
        raise
    try:
        with open(filepath, 'wb') as secretfile:
            decrypted_secret_content = base64.decodebytes(secret_content)
            secretfile.write(decrypted_secret_content)
	except Exception as ex:                   
        print("ERROR: cannot write to file " + filepath, ex, flush=True)
        raise

def handler(ctx, data: io.BytesIO=None):
    logging.getLogger().info("function start")
    secret_ocid = secret_type = resp = ""
    try:
        username = get_text_secret(USERNAME_SECRET_OCID)
        logging.getLogger().info("username : " + username)
        password = get_text_secret(PASSWORD_SECRET_OCID)
	    logging.getLogger().info("password : " + password)
        db_url = get_text_secret(DB_URL_SECRET_OCID)
        logging.getLogger().info("db_url : " + db_url)
        write_db_wallet_files()
        #logging.getLogger().info('INFO: DB wallet dir content =' + os.listdir(DB_WALLET_PATH), flush=True)
        point_db_wallet_path()
        os.environ["TNS_ADMIN"] = DB_WALLET_PATH
        con = cx_Oracle.connect(username, password, db_url)
        logging.getLogger().info("Successfully Connected DB...")
        cur = con.cursor()
        cur.execute('Select * from departments_json')
        rows = cur.fetchall()
        logging.getLogger().info(rows)
        resp = {"connect": "true"}
    except Exception as e:
        print('ERROR: Missing configuration keys, secret ocid and secret_type', e, flush=True)
	    logging.getLogger().info("function end")
    return response.Response(
        ctx,
        response_data=resp,
        headers={"Content-Type": "application/json"}
    )

Docker file

FROM oraclelinux:7-slim
WORKDIR /function
RUN groupadd --gid 1000 fn && adduser --uid 1000 --gid fn fn

ARG release=19
ARG update=3

RUN  yum-config-manager --disable ol7_developer_EPEL && \
     yum-config-manager --enable ol7_optional_latest && \
     yum-config-manager --enable ol7_oracle_instantclient && \
     yum-config-manager --enable ol7_oracle_instantclient && \
     yum -y install python3 oracle-release-el7 && \
     yum -y install oracle-instantclient${release}.${update}-basiclite && \
     rm -rf /var/cache/yum

RUN mkdir /tmp/dbwallet
RUN chown -R fn:fn /tmp/dbwallet
ENV TNS_ADMIN=/tmp/dbwallet

ADD . /function/

RUN pip3 install --upgrade pip
RUN pip3 install --no-cache --no-cache-dir -r requirements.txt
RUN rm -fr /function/.pip_cache ~/.cache/pip requirements.txt func.yaml Dockerfile README.md

ENV PYTHONPATH=/python
ENTRYPOINT ["/usr/local/bin/fdk", "/function/func.py", "handler"]

Conclusion

In this post, we looked at the most secure and reliable way to fetch Autonomous Database wallet contents in OCI. We also saw how easily you can retrieve those secrets and use them in your applications with the OCI Python SDK and then use the details to connect to the database.

You’re not limited to serverless functions or using the Python SDK. Any application using any OCI SDK version can work just as easily with the secrets service in Oracle Cloud Infrastructure and connect to the Autonomous Transaction Processing database.

• Autonomous Transaction Processing documentation

• Oracle Functions: Connecting to an ATP database with a wallet stored as secrets (Blog post)

• Oracle Functions samples on GitHub

• Oracle Functions samples using Python on GitHub

• Object Storage documentation

• Oracle Functions samples for Autonomous Database client running SQL with Python on GitHub