Key Takeaways

• Python-oracledb supports multiple OCI SDK authentication methods, so you’re not locked into just one way of connecting, you can pick what fits your setup best.
• The most common options you’ll come across are API keys, session tokens, instance principals, and resource principals, each with its own trade-offs.
• API key-based authentication is still the simplest to get started with, but it involves managing config files and private keys locally.
• Session tokens are a good step up for security, they’re temporary and expire automatically, which makes them useful for development and short-lived access.
• Instance principals and resource principals remove the need to manage credentials entirely, making them a better fit for cloud-native or production workloads.
• The nice part is that all of these can be used with python-oracledb through the auth_type parameter, so switching between them doesn’t require a complete rewrite of your code.

The python-oracledb driver supports several OCI SDK authentication methods to connect Python applications to Oracle Cloud Autonomous Databases. Each method has it’s own use cases and benefits. This post provides a quick overview and links to detailed tutorials for each of these authentication methods.

OCI SDK authentication methods rely on token-based authentication, where a secure token is generated and used instead of repeatedly sending credentials. These tokens represent an authenticated identity and are validated by OCI services to authorize requests. Different authentication methods vary in how these tokens are obtained and managed.

The following diagram provides a high-level view of how OCI SDK authentication methods interact with OCI IAM to obtain database access tokens (db-tokens), which are then used by the database client for secure authentication.

In all cases, the chosen authentication method is used to authenticate with IAM, which then issues a db-token. This token is passed to the database client (via file or API) and used in place of a password.

For a detailed explanation of this flow, refer to the Oracle documentation.

Supported Authentication Methods in python-oracledb

API Key-Based Authentication

With API key-based authentication, you create a configuration file stored locally. The file contains your user OCID, tenancy OCID, region, private key path, and fingerprint.

This method results in a permanent configuration file on your machine, so it should be used only if you’re working from a secure network and are comfortable storing private keys locally.

In python-oracledb, API key-based authentication is supported in two flavours:

ConfigFileAuthentication — Reads your user, tenancy, fingerprint, key file, and region from ~/.oci/config.

SimpleAuthentication — A streamlined version where you provide minimal parameters directly.

For detailed setup steps and examples, refer to my blog on configuring API key-based authentication.

Instance Principal Authentication

Instance principal authentication allows an OCI compute instance, container, or function to make API calls without user credentials or a local configuration file.
Once the required resources and policies are set up, applications running on the instance can call Oracle Cloud Infrastructure services directly.

OCI automatically provides a short-lived token to the instance, which python-oracledb uses to authenticate database connections securely.

This method is ideal for cloud-native workloads and automation, especially when storing API keys locally is not desirable.

You can explore this further in my blog on Instance Principal authentication.

Resource Principal Authentication

Resource principal authentication allows OCI services such as Data Science notebooks, Functions, or OKE workloads to make API calls without user credentials or local configuration files.

Once the required dynamic groups and IAM policies are set up, applications running inside these services can directly access Oracle Cloud Infrastructure services.

OCI automatically provides short-lived tokens to the running resource, which python-oracledb can use to authenticate database connections securely.

This method is ideal for applications running in OCI-managed environments where there is no direct access to underlying compute instances and storing credentials is not practical.

You can learn more on it in my post on Resource Principal authentication.

Session Token-Based Authentication

Session token-based authentication uses a local configuration file containing your user OCID, tenancy OCID, region, private key path, and a temporary session token file path.
The temporary session token expires after a short period (usually one hour) and provides quick, secure authentication without storing long-lived credentials.

In python-oracledb, session token authentication is supported in two flavors:

SecurityToken — Uses the session profile from your OCI configuration file.

SecurityTokenSimple — A streamlined version where configuration parameters are supplied via environment variables.

Python-oracledb reads the token and private key, builds a signer, and authenticates securely, without requiring a password.

This method is ideal for short-lived credentials, interactive sessions.

Learn more about setup and usage in my blog on session token authentication.

Summary of Authentication Methods

Here’s a quick comparison of the OCI SDK authentication methods supported in python-oracledb, their use cases, and how each one manages credentials.

Conclusion

Python-oracledb supports multiple OCI SDK authentication methods to fit different environments and workflows: API key-based, instance principal, and session token-based authentication.
Choose the method that best balances security, convenience, and automation for your use case.

For detailed setup instructions and step-by-step examples, check out the linked tutorial blogs for each authentication method.

FAQs

What authentication methods are supported in python-oracledb?

You can use multiple OCI SDK authentication methods, including:

• API key-based authentication
• Session token-based authentication
• Instance principals
• Resource principals

Which authentication method should I start with?

If you’re just getting started, API keys are the easiest.
But if you’re working on something more serious, it’s worth looking at session tokens or principals-based authentication.

What’s the main difference between these methods?

It mostly comes down to how credentials are handled:

• API keys → stored locally
• Session tokens → temporary credentials
• Instance/resource principals → no credentials to manage

Do I always need an OCI config file?

Not always.

• API keys and session tokens typically rely on a config file
• Instance and resource principals don’t require one since OCI manages identity internally

Can I switch between authentication methods easily?

Yes. Since python-oracledb supports these via configuration (like auth_type), switching is mostly about changing how authentication is set up, not rewriting your whole connection logic.

Which method is best for production workloads?

Generally:

• Instance principals or resource principals are preferred
• They avoid secret management and work better with IAM policies

What about local development?

Session tokens are usually the most convenient option for local work, they’re easy to generate and don’t require long-lived credentials.

Do all methods work the same way under the hood?

Not exactly, but they all end up doing the same thing, authenticating your request to OCI services. The SDK abstracts most of the differences, so from your code’s perspective, the experience is fairly consistent.

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

OCI SDK Authentication Methods Supported in Python-oracledb was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key Takeaways

• Using OCI session tokens is a safer option than keeping API keys or Oracle AI Database passwords around, mainly because they’re short-lived and expire on their own.
• You can connect to Oracle AI Database using python-oracledb without ever typing a password.
• The oci session authenticate command makes the whole setup pretty straightforward. Once you run it, everything you need gets stored in your OCI config.
• This approach keeps your code clean. No secrets hard-coded, no environment variables full of sensitive values.
• It fits really well for local development or short-lived scripts where you just need quick, secure access.
• Since it’s backed by OCI IAM, access control stays centralized , you’re not managing database credentials separately anymore.

Session token authentication is one of the cleanest ways to connect to Oracle Cloud databases without handling long-lived credentials.
It’s short-lived, secure, and fits naturally into cloud workflows where users or services already have OCI identities.

Starting with python-oracledb 4.0, session token authentication is supported, so you can connect using the same session credentials that power the OCI CLI, without needing to build custom signer logic or rely on instance principals.

This post shows how to do that, step by step.

What Is OCI Session Token Authentication?

OCI session tokens are temporary credentials issued when you authenticate using the OCI CLI or console. They replace long-lived private key and fingerprint credentials with a short-term token, stored locally in a token file. This token is then used to sign API requests, including database connection authentication.

You typically see this line in your ~/.oci/config file when you’re logged in via a session:

security_token_file=/home/user/.oci/sessions/profilename/token

Python-oracledb can now use the same token to authenticate to your Oracle Autonomous Database.

Prerequisites

To use session token authentication, ensure you have the following in place:

1. Access to an Oracle Cloud Autonomous Database(ADB) instance that supports IAM authentication.
2. An OCI account with IAM properly configured.
3. The oci Python SDK and the latest python-oracledb package installed (pip install oracledb[oci]).

I won’t go through each setup step here, as I covered them in my earlier blog, where I explained how to provision an ADB, configure IAM, prepare your environment for authentication. If you need a refresher, you can revisit that for detailed setup instructions.

Let’s jump straight into configuring the session token.

Configuring the Session Token

Configuring the session token is the first step before using it in Python-oracledb. You’ll start by creating an authenticated OCI CLI session, which generates a short-lived token and updates your OCI configuration automatically.
Let’s look at how to do this both with and without a browser.

Creating a CLI Session with a Browser

To use token-based authentication for the OCI CLI on a system with a web browser:

1. Open a terminal and run, oci session authenticate
2. Select your region
3. In the browser window that opens, sign in with your OCI credentials.
4. After successful authentication, close the browser and return to the terminal.
   Follow the interactive prompts — a session configuration file will be created automatically.

Creating a CLI Session without a Browser

If your environment doesn’t have a browser (for example, a headless server or VM), you can still generate a session token.

First, ensure you’ve authenticated with oci setup config.

Then, run the following command to create a session without a browser:

oci session authenticate --no-browser

You will be prompted to select a region.

Enter a region by index or name

Choose your region from the list.

Next, you’ll be asked to provide a profile name:

Enter the name of the profile you would like to create:

Enter any name your prefer. The CLI will then write the configuration file. Also, you will see a message suggesting how to test your new credentials:

Config written to: /home/user/.oci/config

    Try out your newly created session credentials with the following example command:

    oci iam region list --config-file /home/user/.oci/config --profile st --auth security_token

Now, open your ~/.oci/config file to verify that a new profile has been created. It will include the location of the generated session token.

[st_1]
fingerprint=<fingerprint>
key_file=/home/user/.oci/sessions/st_1/oci_api_key.pem
tenancy=<tenancy_ocid>
region=ap-mumbai-1
security_token_file=/home/user/.oci/sessions/st_1/token

Each profile under ~/.oci/sessions holds its own token and key files.

With this configuration in place, you’re ready to authenticate to your Oracle Autonomous Database using the same session token that powers your OCI CLI.

Connecting to the Oracle Autonomous Database Using Session Token Authentication

Once your OCI CLI session is authenticated and the token file is available in ~/.oci, you can use it directly with Python-oracledb. The driver reads the token and private key from your OCI configuration, builds a signer, and authenticates securely without any password.

To use the session token with Python-oracledb, the oci_tokens plugin must be imported. It’s a built-in hook that integrates session token authentication logic using the OCI Python SDK. The cloud configuration parameters are provided through the extra_auth_params argument in the connection call.

The auth_type parameter can be set to either SecurityToken or SecurityTokenSimple, depending on how you want to authenticate with the session token.

Below is an example using SecurityToken authentication. Here, auth_type, profile are passed through extra_auth_params. This example assumes the default OCI configuration file location: /home/user/.oci/config. session_profile is the profile name given during oci session authenticate.

import oracledb
import oracledb.plugins.oci_tokens

dsn = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps) \
       (port=1521)(host=host.oraclecloud.com))(connect_data= \
       (service_name=myclouddb19_high.adb.oraclecloud.com)) \
       (security=(ssl_server_dn_match=yes)))"
}

token_based_auth = {
      "auth_type": "SecurityToken",
      "profile": "session_profile"
}

connection = oracledb.connect(
               dsn=dsn, 
               extra_auth_params=token_based_auth
)

The next example demonstrates SecurityTokenSimple authentication. In this case, auth_type is set to SecurityTokenSimple, and all configuration parameters are required to be supplied via environment variables.

import oracledb
import oracledb.plugins.oci_tokens
import os

dsn = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps) \
       (port=1521)(host=host.oraclecloud.com))(connect_data= \
       (service_name=myclouddb19_high.adb.oraclecloud.com)) \
       (security=(ssl_server_dn_match=yes)))"
}

token_based_auth = {
      "auth_type": "SecurityTokenSimple",
      "key_file": os.getenv("PYO_STSA_OCI_KEYFILE"),
      "fingerprint": os.getenv("PYO_STSA_OCI_FINGERPRINT"),
      "tenancy": os.getenv("PYO_STSA_OCI_TENANCY"),
      "region": os.getenv("PYO_STSA_OCI_REGION"),
      "profile": os.getenv("PYO_STSA_OCI_PROFILE"),
      "security_token_file": os.getenv("PYO_STSA_OCI_SECURITY_TOKEN_FILE")
}

connection = oracledb.connect(
               dsn=dsn, 
               extra_auth_params=token_based_auth
)

Conclusion

Session token authentication in Python-oracledb makes connecting to Oracle Autonomous Databases simple, secure, and password-free. With just a few steps, you can use your OCI CLI session tokens to authenticate automatically, whether on a local machine or a headless server.

This approach reduces reliance on long-living and less secure credentials and fits seamlessly into modern cloud workflows.

Give it a try and see how effortless and secure session token authentication can make your database connections.

FAQs

What exactly is an OCI session token?

It’s a temporary authentication token you get after logging in through OCI (usually via a browser). Instead of using API keys, you use this token to access services.

How do I generate one?

On your terminal, run:

oci session authenticate

It’ll open a browser, ask you to log in, and then store the token details in your OCI config file. You can also generate it without browser using--no-browser option in the above command.

Is this something I should use in production?

Not usually. It’s great for development and quick scripts, but for production setups, things like instance principals are a better fit.

Do I still need the OCI config file?

Yes, that’s where the token location, key path, and other details live. Your code relies on it.

Does this mean I can completely avoid Oracle AI Database passwords?

Yep. Once IAM-based auth is set up, you don’t need to deal with database passwords at all.

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

Using OCI Session Token Authentication with Python-oracledb was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Leverage OCI Resource Principals for secure, password-less Oracle AI Database connections with your Python applications

Key Takeaways

• Resource principals let you connect to Oracle AI Database without storing any credentials at all, no config files, no API keys, nothing sitting in your code.
• Instead of authenticating as a user, your resource (like a function, or service) becomes the identity. OCI handles the authentication behind the scenes.
• Once the right dynamic group and IAM policies are in place, the database or service can securely access other OCI resources on its own.
• It works really well with python-oracledb, making it easy to build secure, cloud-native apps without worrying about credential handling.

Starting with version 1.1, python-oracledb added support for token-based authentication. This was extended in version 3.0 to include cloud-native authentication using Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) tokens. With the 4.0 release, python-oracledb now adds support for Resource Principal authentication, allowing applications running inside OCI services to connect to Oracle AI Database without managing credentials. This post explains what that means and how to use it.

ELI5: What is Resource Principal ?

Imagine your code is running inside an OCI service, like a notebook or a function, and it needs to talk to something like a Oracle AI Database. The usual way is to give it a password or key and make sure it’s stored safely. That gets messy fast and is easy to get wrong.

Resource Principals are OCI’s way of saying, “Don’t worry about that, I’ll handle it for you.” The service running your code is treated as an identity, just like a user or group, and OCI automatically gives it what it needs to prove who it is. Everything is handled behind the scenes, including creating and refreshing the credentials.

From a technical point of view, the service uses this identity to get a short-lived token from OCI IAM. That token is then used to access services like Oracle Autonomous Database, without storing any secrets in your code.

How Resource Principals work ?

Dynamic Groups

OCI uses Dynamic Groups to manage access for resources running inside OCI services. These work like IAM groups, but instead of users or instances, they group resources such as Notebook sessions, Functions, or OKE workloads. You define matching rules based on resource attributes, and anything that matches is automatically included.

Example Matching Rule:

ALL {resource.type = ‘datasciencenotebooksession’}

Once the resources are part of a dynamic group, you can assign policies to control what they can access.

IAM Policies

After defining the dynamic group, you create IAM policies that specify what those resources are allowed to do. For example:

Allow dynamic-group id <dynamicgroup_ocid> to manage autonomous-database-family in tenancy

This policy allows all resources defined by the specified dynamic group (via it’s OCID and matching rules) full access to Oracle Autonomous Database operations across the tenancy.

How Resource Principals differs from Instance Principals ?

Instance Principals work with compute instances that you manage. The identity is tied to the VM, and the application inherits that identity when it runs on the instance.

Resource Principals apply to OCI-managed services where you don’t control the underlying infrastructure. The identity is assigned to the service resource itself, such as a Notebook session or an OCI Function.

In short, Instance Principals are for machines you manage. Resource Principals are for services OCI manages for you.

If you want more background on Instance Principals, I’ve covered them in my earlier post.

How to use Resource Principals ?

Prerequisites

1. You should have an OCI resource available. In this post, we will use an OCI Data Science notebook to demonstrate Resource Principals. You will also briefly see how to create one in the Oracle cloud console.

Go to your Oracle Cloud console, Navigation Menu -> Analytics & AI -> Data Science

Click on the Create project and give a Name (optional) and Description(optional).

You can see the project created from the Project details page, click on Create notebook session.

In the Create notebook session page, you can give a Name(optional), select appropriate fields fields and click on Create.

You can see the Notebook session created. Clicking Open will take you to the Notebook.

2. Ensure the OCI CLI is available in the notebook environment. You can follow the installation instructions provided here.

3. Also, make sure python-oracledb is installed. To install or upgrade it, run:

python3 -m pip install oracledb --upgrade

See python-oracledb Installation for details.

Steps to set up Resource Principals

1. Create a Dynamic Group
   Define rules to group the resources that require access.
2. Create an IAM Policy
   Grant the dynamic group permissions to access the required OCI services (e.g., Oracle Autonomous Database).
3. Map Principals to Oracle AI Database users
   In the database, create user mappings for your resource principals or dynamic groups.
4. Run the Application from the Data Science notebook
   Developers can configure their applications to use Resource Principals from the OCI SDK. The application authenticates using the resource’s identity and makes API or database calls, no credentials or config files required.

Create a Dynamic group

Create a Dynamic group that matches a set of resources.

Login to your Oracle Cloud console. Go to Navigation menu → Identity & Security. Under Identity, select Domains. Under Identity domain, select Dynamic groups

Click Create dynamic group and enter the following details.

Name. Provide a unique name for the group. The name must be unique across all groups in your tenancy, dynamic groups, and user groups.

Description. Provide a friendly description (optional).

The next step is to create matching rules.

Resources that meet the rule criteria are members of the group.

After the Rules are added, click on ‘Create’ button to create a dynamic group. You can see the dynamic group created.

Create an IAM Policy

Now, create a policy dictating what permissions those resources should receive.

Go to Identity & Security → Policies → Create Policy.

Give Name and Description and click on ‘Show manual Editor’. You can give the statement in the text box available.

The policy gets created.

From the Statements tab, you can verify the policy statements provided, as well as, you can edit the policy further, if required.

Note: The resource principal token is cached for 15 minutes. If you change the policy or the dynamic group, you must wait for 15 minutes to see the effect of the changes.

Map Principals to Oracle Database users

So far so good, your application is authenticated using a Resource Principal. However, the database still needs to know: “Who is this principal, and what Oracle Database user should I map it to?”

This mapping is not automatic — you must explicitly create users or mappings in the database.

Map your dynamic group to a shared Oracle AI Database user.

You map a dynamic group of resources to a single shared database user.

CREATE USER shared_user IDENTIFIED GLOBALLY
AS 'IAM_GROUP_NAME=rpdg';

GRANT CREATE SESSION TO shared_user;

This means any resource in the specified dynamic group can log in as shared_user.

Also, make sure that External authentication is enabled on ADB and identity_provider_type is set to OCI_IAM. Refer ‘Enable IAM Authentication on ADB’ from this link for the steps involved.

BEGIN
 DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
 type => 'OCI_IAM' );
END;
/
 
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'identity_provider_type';

Run the Application from the Data Science notebook

Now, run your application code in the Data Science notebook. The Python example below shows how to connect to Oracle Autonomous Database using Resource Principal authentication, without any user-managed credentials or configuration files.

To enable this, the application uses the oci_tokens plugin in python-oracledb.

To use Resource Principal authentication, set the auth_type to "resourceprincipal" and pass it through extra_auth_params in the connection call.

import oracledb
import oracledb.plugins.oci_tokens
 
connect_string = '''(description= (retry_count=20)(retry_delay=3)
                    (address=(protocol=tcps)(port=1521)
                    (host=adb.sa-saopaulo-1.oraclecloud.com))(connect_data=
                    (service_name=myclouddb26_high.adb.oraclecloud.com))
                    (security=(ssl_server_dn_match=yes)))'''
 
token_based_auth = {
      "auth_type": "ResourcePrincipal"
}
 
connection = oracledb.connect(
              dsn=connect_string, 
              extra_auth_params=token_based_auth
)

curr = connection.cursor()
sqlText = "select user from dual"
curr.execute(sqlText)
record = curr.fetchone()
print(record)

When the application runs, you can verify that resource principal authentication happens successfully and the connection goes through as shared_user, because the dynamic group, rpdg, your notebook belongs to, is mapped to that user in the database.

Conclusion

Resource Principals make it easier to authenticate from OCI-managed services without managing credentials. With support now available in python-oracledb 4.0, you can connect to Oracle AI Database in a secure, cloud-native way using IAM tokens.

Give it a try and stop worrying about secrets :)

FAQs

What are resource principals in OCI?

Resource principals allow an OCI resource (like an OCI function, Data-science Notebook) to authenticate itself directly with OCI services, instead of using user credentials.

What do I need to set this up?

At a high level:

• A dynamic group that includes your resource
• IAM policies that grant required permissions

Once that’s done, the resource can act on its own behalf.

Do I need an OCI config file or wallet?

No, that’s one of the biggest advantages.
You don’t need a config file, private key, or wallet for authentication in this setup.

How does python-oracledb fit into this?

Python-oracledb supports resource principals by allowing you to set auth_type="ResourcePrincipal" in extra_auth_params during oracledb.connect(), so your application can connect without explicitly providing credentials.

Is this approach suitable for production?

Yes, this is the recommended approach for production workloads because:

• No secret management
• Automatic credential rotation
• Better alignment with IAM policies

What are the prerequisites for using resource principals?

You’ll need:

• Proper IAM configuration (dynamic groups and policies)
• Your application running inside OCI (or an OCI-managed environment)

How is Resource Principal different from Instance Principal?

• Instance Principal: Used when your application runs on an OCI compute instance (VM), the VM acts as the identity.
• Resource Principal: Used by OCI services (like Functions, OKE, etc.), the service/resource acts as the identity.

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

No More Credentials: Connect to Oracle Autonomous Database Using Resource Principals in… was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key Takeaways

• Airflow lets you run and schedule Oracle AI Database queries in a structured, repeatable way.
• The python-oracledb driver handles the actual database connection behind the scenes.
• Airflow Connections keep credentials out of your code and make setups easier to manage.
• A simple DAG is enough to verify that Airflow and Oracle AI Database are working together.

Airflow works best when you treat workflows as code. Oracle AI Database works best when it stays the system of record. Put them together and you get something predictable, which is rarer than it should be.

This blog is a practical walk-through of how to get Airflow talking to Oracle AI Database and doing useful work. We’ll keep the moving parts to a minimum and focus on what actually runs.

If you’ve been running scripts by hand or wiring basic jobs together, using Airflow will orchestrate them for you and save you time!

Why Use Airflow with Oracle AI Database

Most teams already keep their data in Oracle AI Database. The real problem isn’t storage, it’s orchestration. Scripts pile up, dependencies tangle, and sooner or later someone ends up triggering jobs by hand.

Airflow fixes that by giving your workflows structure. It turns scattered scripts into something predictable and easier to reason about. You stop stitching scripts together and start defining workflows that behave the same way every time.

This starts to matter once things grow. One script is fine. Five scripts with dependencies and retries is where things fall apart. Airflow keeps that complexity in one place.

What You Need Before You Start

You need Airflow running locally or on a server. A working Oracle AI Database helps, along with credentials that actually connect.

First, install Airflow — you can follow the steps over here. This brings a standalone Airflow instance on your machine. Airflow needs a home directory. By default, it uses ~/airflow, but you can choose a different location if needed.

Set the AIRFLOW_HOME environment variable to tell Airflow where to store its files. Do this before installing Airflow so it knows where to place everything. Below are the steps on Linux.

$ export AIRFLOW_VERSION="3.1.8"
$ export PYTHON_VERSION="3.12"
$ export AIRFLOW_HOME=~/airflow
$ export CONSTRAINT_URL="https://raw.githubusercontent.com/apache/airflow/constraints-${AIRFLOW_VERSION}/constraints-${PYTHON_VERSION}.txt"
$
$ echo $CONSTRAINT_URL
https://raw.githubusercontent.com/apache/airflow/constraints-3.1.8/constraints-3.12.txt
$ 
$ pip install "apache-airflow==${AIRFLOW_VERSION}" --constraint "${CONSTRAINT_URL}"
$
$ pip freeze | grep airflow
Using Python 3.12.3 environment at: venv_af_rag
apache-airflow                           3.1.8
apache-airflow-core                      3.1.8
apache-airflow-providers-common-compat   1.14.0
apache-airflow-providers-common-io       1.7.1
apache-airflow-providers-common-sql      1.32.0
apache-airflow-providers-smtp            2.4.2
apache-airflow-providers-standard        1.12.0
apache-airflow-task-sdk                  1.1.8
$ 
$ airflow version
3.1.8

You’ll need the python-oracledb package, since your Airflow tasks use it under the hood. If you install the Oracle provider for Airflow, it gets installed for you.

$ pip install apache-airflow-providers-oracle
 + apache-airflow-providers-oracle==4.5.1
 + oracledb==3.4.2

A Quick Primer on Airflow (Just Enough to Begin)

Airflow is a scheduler that runs Python code.

You define a workflow as a DAG(Directed Acyclic Graph). A DAG is a set of tasks with dependencies, written in Python. Each task does one thing, and Airflow handles the order, retries, and logging.

The important parts are simple. DAG defines the workflow, the tasks define the work, and the scheduler decides when to run them.

Finally, after above installation steps, run airflow standalone on the terminal. This should bring up the Airflow UI on http://localhost:8080 on your default browser (8080 is the default port for Apache Airflow). The credentials to login are available in file, simple_auth_manager_passwords.json.generated under AIRFLOW_HOME directory.

How Airflow Connects to Oracle AI Database

Airflow does not talk to Oracle AI Database directly. It uses hooks and operators, which are just wrappers around database connections.

Underneath, the actual connection is handled by the python-oracledb driver. Airflow stores connection details like host, port, schema, and credentials, then passes them to your code or its own operators when needed.

Think of Airflow as the coordinator. The driver still does the real database work.

Setting Up an Oracle AI Database Connection in Airflow

Start with the Airflow UI.

Go to Admin, then Connections. Create a new connection and set the type to Oracle. Fill in Host, Port, Login, Password and other required details.

Give it a clear Connection ID, something like oracle_conn. You’ll use that ID in your DAG instead of repeating credentials everywhere.

Once saved, Airflow can reuse this connection across tasks.

Alternatively, you can also create connections using Airflow Command Line Interface as below.

$ airflow connections add oracle_conn3 --conn-type oracle --conn-login admin --conn-password password1 --conn-host "tcps://host.oraclecloud.com:1521/myclouddb26_high.adb.oraclecloud.com"
Successfully added `conn_id`=oracle_conn3 : oracle://admin:******@tcps://host.oraclecloud.com:1521/myclouddb26_high.adb.oraclecloud.com:
$
$ airflow connections get oracle_conn3
id | conn_id      | conn_type | description | host                     | schema | login | password     | port | is_encrypted | is_extra_encrypted | extra_dejson | get_uri
===+==============+===========+=============+==========================+========+=======+==============+======+==============+====================+==============+==========================
2  | oracle_conn3 | oracle    | None        | tcps://host.oraclecloud. | None   | admin | password1    | None | True         | True               | {}           | oracle://tcps://admin:pas
   |              |           |             | .com:1521/myclouddb26_   |        |       |              |      |              |                    |              | sword1@host.oraclecloud.
   |              |           |             | high.adb.oraclecloud.com |        |       |              |      |              |                    |              | .com%3A1521%2F_mycloudd
   |              |           |             |                          |        |       |              |      |              |                    |              | b26_high.adb.oraclecloud.
   |              |           |             |                          |        |       |              |      |              |                    |              | com
   |              |           |             |                          |        |       |              |      |              |                    |              | 

A Simple DAG — Using Airflow’s Oracle Hook

You can always call python-oracledb.connect() directly inside a task to connect to Oracle AI Database. That works, and sometimes it’s all you need. Here, we’ll take a slightly cleaner route and use Airflow’s Oracle hook.

OracleHook is Airflow’s wrapper around Oracle AI Database connections. It reads connection details from Airflow, manages the plumbing, and gives you a ready-to-use connection or cursor.

Here’s a minimal DAG that uses it. This DAG connects to Oracle AI Database using above created connection identifier, oracle_conn3, and runs a simple SELECT query on Oracle AI Database, to verify that the setup works end to end.

The DAGs usually sit in theAIRFLOW_HOME/dags folder.

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.oracle.hooks.oracle import OracleHook
from datetime import datetime


def test_oracle_adb_connection():
    hook = OracleHook(oracle_conn_id="oracle_conn3")
    conn = hook.get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT 1 FROM dual")
    result = cursor.fetchone()
    print("Oracle connection works! Result:", result)


with DAG(
    dag_id="vig_test_oracle_adb_conn",
    start_date=datetime(2025, 1, 1),
    schedule = "@once",
    catchup=False,
    tags=["test"],
) as dag:
    test_task = PythonOperator(
        task_id="test_oracle_adb",
        python_callable=test_oracle_adb_connection,
    )

    test_task

This keeps your DAG focused on the work, not on connection details. Airflow handles the credentials, and the hook gives you a connection that’s ready to use.

Running and Monitoring Your DAG

Once your DAG is in place, Airflow picks it up automatically.

Use the UI to trigger it manually or wait for the schedule. You can see each task, its status, logs, and retries from the DAG Details page.

If something fails, Airflow shows you where and why. No digging through random log files.

At this point, you have a basic setup that works. Airflow can trigger a DAG, connect to Oracle AI Database, and run a query end to end.

From here, you can start layering in real work. Add more tasks, split logic where it makes sense, and build toward actual pipelines as your use case grows.

FAQs

1. Do I need special setup to connect Airflow to Oracle AI Database ?
You only need the Airflow server to be up and running, access to an Oracle AI Database, and the python-oracledb package.

2. Can I run Oracle AI Database queries directly from a DAG?
Yes. You can execute queries inside a task using either python-oracledb or Airflow’s OracleHook.

3. Why use OracleHook instead of direct connections?
It uses Airflow’s stored connection details, so you don’t have to manage credentials in your application code.

4. How do I confirm everything is working?
Run a simple DAG that executes a query and check the results and logs in the Airflow UI.

Airflow Resources

Apache Airflow is an open-source workflow orchestration tool that lets you define, schedule, and monitor pipelines as code. It helps you manage task dependencies, retries, and execution flow without relying on ad-hoc scripts.

• Installation instructions
• Documentation
• Providers-Oracle
• Source code on GitHub
• PyPI

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

From DAG to Database: Using Airflow with Oracle AI Database was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Oracle Database 23ai introduced Sessionless Transactions, a way to run multi-step transactions without holding on to a dedicated database session between steps. With version 3.3, the python-oracledb driver adds native support for this feature, enabling Python developers to write stateless, scalable transaction flows with ease.

This unlocks better pool utilization, easier scaling, and reduced overhead, especially in apps with user “think time” or back-end delays.

It delivers what the title promised: speed without strings. No sticky sessions. No wasted connections. Just clean, fast commits when you’re ready.

In this post, you’ll see how Sessionless Transactions work, what makes them different from older approaches, and how to use them in Python with real code examples. Whether you’re building a back-end service or trying to squeeze more out of your connection pool, this model gives you control without slowing you down.

The python-oracledb driver uses the term connection, but each connection maps to a database session under the hood. The terms are used interchangeably throughout this post.

The Old Way: Sessions That Wouldn’t Let Go

Before Sessionless Transactions, you had two choices when working with Oracle Database transactions.

One way was to stick with a session from start to finish. You begin a transaction on a connection, and that connection stays tied up until you commit or roll it back. Even if your application is idle in between, the database holds onto that session. This approach is simple, but it wastes connections, especially under heavy load.

The other option was to use XA transactions, which let you pause and resume a transaction across sessions. While this gave more flexibility, it introduced added complexity, especially around coordinating the final commit and handling failures. If something went wrong mid-transaction, you could end up with in-doubt states and locked resources in the database. Now you’re troubleshooting state issues, not writing features.

Both options forced you to trade off between simplicity and scalability. And neither felt quite right.

Sessionless Transactions: Stateless and Scalable

Oracle Database 23ai introduced Sessionless Transactions, a new way to manage transactions without locking a session or connection. With this model, you can begin a transaction, release the connection back to the pool, and later resume the same transaction on a completely different connection, even in a different database instance.

The transaction state travels with a unique identifier, not the connection it started on. This lets your application switch connections freely without losing context.

This gives you scalability without needing any external coordination logic. You're no longer stuck holding a connection hostage during think time or while waiting for user input. And unlike XA, there’s no risk of in-doubt transactions due to a failed coordinator.

Behind the scenes, the database tracks the transaction state. Your application just holds the transaction identifier and resumes when it’s ready to continue.

Want to dig deeper into the benefits of Sessionless transactions ? Check out this link.

How It Works

Each Sessionless Transaction is tagged with a unique identifier, transaction ID. You begin a Sessionless Transaction by passing in this ID (if none is provided, the python-oracledb driver generates one for you). After doing part of the work, you can suspend the transaction, release the session back to the pool, and later resume the same transaction on another session using the same ID. The transaction stays intact across sessions and ends only when you explicitly commit or roll it back.

New APIs That Make It Work

Python-oracledb introduces three new methods to support Sessionless Transactions:
begin_sessionless_transaction(), suspend_sessionless_transaction() and resume_sessionless_transaction().

# To Begin a Sessionless Transaction
begin_sessionless_transaction(
    transaction_id = None, 
    timeout = 60, 
    defer_round_trip = False
)


# To Suspend a Sessionless Transaction
suspend_sessionless_transaction()


# To Resume a Sessionless Transaction
resume_sessionless_transaction(
    transaction_id,
    timeout = 60,
    defer_round_trip = False
)

You use these just like they sound. Begin a transaction with a unique ID. Suspend it when you’re done with part of the work. Resume it later using the same ID on a different connection.

The transaction ID acts as the handle. You can generate your own or let the driver create one for you. The transaction itself lives in the database until you explicitly commit or roll it back. See the documentation for complete method signatures and what each function parameter mean.

Code Example

You will walk through a simple use case where a transaction starts on one session, continues on another, and commits from a third, without holding on to any session in between.

In Session1:

You begin by starting a new Sessionless Transaction. The python-oracledb driver either uses the transaction ID you provide or generates one for you. In this example, let the driver handle it. The begin_sessionless_transaction call returns a transaction ID, which you save in txn_id. This ID will be used later to resume the same transaction.

You insert a row in to the table which was already created beforehand. Then, instead of committing or rolling back, you suspend the transaction. This releases the database session back to the pool, freeing up resources.

# Start a new sessionless transaction on connection 1
txn_id = connection1.begin_sessionless_transaction()

# Insert a row
cursor1 = connection1.cursor()
cursor1.execute("INSERT INTO TBL_SLST VALUES(1, 'row1')")

# Suspend the sessionless transaction
connection1.suspend_sessionless_transaction()

In Session2:

Later, you acquire a new session from the pool and resume the transaction using the saved txn_id. You add another row and this time suspend using the new suspend_on_success flag in the execute() call, to demonstrate how the flag works as an alternative to explicitly calling suspend_sessionless_transaction(). Instead of making a separate round-trip to suspend, setting suspend_on_success=True performs the suspension right after the successful operation executed in the execute() call. This avoids the extra round-trip and lets you wrap execution and suspension in a single round-trip. Once suspended, the session is released back to the pool.

# Resume the transaction on connection 2
connection2.resume_sessionless_transaction(transaction_id=txn_id)

# Suspend using suspend_on_success flag, after successful execution of DML
cursor2 = connection2.cursor()
cursor2.execute(
    "INSERT INTO TBL_SLST VALUES(2, 'row2')", 
    suspend_on_success=True
)

In Session3

Finally, you resume the transaction from a third session. Here, you also use the defer_round_trip flag to demonstrate how it can delay the resume until the next database interaction. In this case, both the resume and the insert happen in a single round trip. This helps avoid the extra round trip that would occur with an immediate resume_sessionless_transaction() call, offering a meaningful performance optimization in round-trip-sensitive scenarios. You then query the table and see all the inserted rows—confirmation that the transaction state carried across sessions. A final commit() completes the Sessionless Transaction.

# Resume the transaction on connection 3 on the next round trip
connection3.resume_sessionless_transaction(
    transaction_id = txn_id,
    defer_round_trip = True
)

# Insert another row
cursor3 = connection3.cursor()
cursor3.execute("INSERT INTO TBL_SLST VALUES(3, 'row3')")

# Query rows
results = cursor3.execute("SELECT * FROM TBL_SLST")
rows = results.fetchall()
assert len(rows) == 3
print(rows)

# Commit
connection3.commit()

A complete runnable example is available here. It simulates three separate connections participating in the same Sessionless Transaction. The output shows how inserts happen across connections, and how the transaction state resumes cleanly — even after suspension.

When to Use It (and What to Watch For)

Sessionless Transactions shine when you want to free up Oracle Database sessions during idle time, like waiting on user input or external APIs. They let you suspend the transaction, release the session, and continue later from a different session without losing the transaction context.

However, they aren’t suitable for every use case. For instance, if a DDL statement like CREATE TABLE is executed within a Sessionless Transaction, it triggers an implicit commit and ends the transaction immediately.

It’s worth noting that suspending a Sessionless Transaction only detaches it from the current connection — it doesn’t end the transaction. The transaction continues to exist on the server and expires if it’s not resumed within the timeout you specify when starting it.

Timeout Behavior

There are two timeout values to keep in mind:

• The timeout in begin_sessionless_transaction() defines how long the transaction stays alive on the server after being suspended. If it's not resumed within that window, it expires and is rolled back.
  For example, in begin_sessionless_transaction(timeout=15), the transaction will expire if not resumed within 15 seconds after being suspended.
• The timeout in resume_sessionless_transaction() controls how long the current connection waits for another connection to finish suspending the transaction before it can resume it. If the other connection doesn't suspend in time, you get an ORA-25351 error. This timeout is short-lived and only matters at resume time.
  For example, in resume_sessionless_transaction(..., timeout=5), the connection waits up to 5 seconds for another connection to finish suspending the transaction. If it doesn't happen, it raises ORA-25351.

Be aware that client-side and server-side (PL/SQL) APIs aren’t interchangeable during an active Sessionless Transaction. For example, if you start or resume a transaction using the python-oracledb driver’s client-side API, you must also suspend and resume it using the same client-side API. Mixing contexts mid-transaction, like starting on the client and trying to suspend it from the server, results in ORA-26211. You can safely switch between client and server only after the transaction is suspended, committed, or rolled back.

To explore further constraints, please refer this link.

Wrapping Up

Sessionless Transactions in python-oracledb, powered by Oracle Database 23ai, give you precise control without holding connections hostage. You can start, suspend, and resume work across sessions, all while keeping the transaction intact.

No session hogging. No heavyweight transaction manager. Just a clean, scalable way to manage distributed work. Give it a try.

Sessionless Transactions are also supported in other Oracle client drivers, including Node.js and Java. See the official documentation for details.

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

Sessionless Transactions in Python-oracledb: Speed without Strings was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Starting with version 1.1, python-oracledb introduced support for token-based authentication. This capability was enhanced in version 3.0 to include cloud-native authentication using Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) tokens. With the 3.2 release, python-oracledb takes it a step further by adding support for Instance Principal authentication: enabling secure, credential-free access from OCI compute resources. This blog walks you through what that means, why it matters, and how to use it.

ELI5: What is Instance Principal ?

Imagine you have a bunch of computers (called compute instances) running in the cloud, and each one needs permission to talk to other Oracle Cloud services, like a database. In the old way, you’d have to give each computer a secret key (like a password) and keep track of all those keys. That’s hard to manage, not very safe, and quickly becomes a mess.

Instance Principals are OCI’s way of saying: “Let me handle that for you.” Each compute instance is treated as a new type of IAM principal (an IAM entity authorized to access OCI resources), similar to a user or group, and is automatically assigned a unique identity in the form of a digital certificate, which it can use to prove who it is. This identity is fully managed by OCI: certificates are created, refreshed, and rotated behind the scenes.

From a technical perspective, the instance authenticates with OCI IAM using this identity and obtains a short-lived token. That token can then be used to access services like Oracle Autonomous Database, without storing or managing any secrets in your application code.

How Instance Principals work ?

Dynamic Groups

OCI uses Dynamic Groups to manage access policies for instances. Think of these as IAM groups, but for compute instances. You define matching rules (based on instance attributes like compartment ID or tags), and instances that match the criteria are automatically added to the group.

Example Matching Rule:

ALL {instance.compartment.id = ‘ocid1.compartment.oc1..exampleuniqueID’}

Once instances are part of a dynamic group, you can assign policies that grant them access to specific OCI services-just like you would for user groups.

IAM Policies

After defining a dynamic group, you need to create IAM policies that specify what those instances are allowed to do. For example:

Allow dynamic-group my-dg to manage autonomous-database-family in tenancy

This policy allows all instances in the my-dg dynamic group full access to Oracle Autonomous Database operations across the tenancy.

How to use Instance Principals ?

Prerequisites

1. You should have one or more OCI compute instances already created. For provisioning compute instances, the steps in this link can be followed.
2. Ensure that OCI Command Line Interface (CLI) is installed on your compute instance(s). You can follow the installation instructions provided here for different environments.
3. Also, make sure python-oracledb is installed. To install or upgrade it, run:

python3 -m pip install oracledb --upgrade

See python-oracledb Installation for details.

Steps to set up Instance Principals

1. Create a Dynamic Group
   Define rules to group the compute instances that require access.
2. Create an IAM Policy
   Grant the dynamic group permissions to access the required OCI services (e.g., Oracle Autonomous Database).
3. Map Principals to Oracle Database users
   In the database, create user mappings for your instance principals or dynamic groups.
4. Deploy Application on the Compute Instance
   Developers can now configure their applications to use the Instance Principals provider from OCI SDK. The application will authenticate and make API/database calls — no API keys or config files required.

Create a Dynamic group

Create a Dynamic group that matches a set of instances.

Login to your Oracle Cloud console. Go to Navigation menu → Identity & Security. Under Identity, select Domains. Under Identity domain, select Dynamic groups

Click Create dynamic group and enter the following details.

Name. Provide a unique name for the group. The name must be unique across all groups in your tenancy, dynamic groups, and user groups.

Description. Provide a friendly description (optional).

The next step is to create matching rules.

For this, you can use the Rule Builder, or use the text box provided. Resources that meet the rule criteria are members of the group. In this example, let’s use the Rule Builder, which makes it very easy to define the matching rules for a dynamic group and auto-constructs the rule.

Choose the attributes that you want to group instances on and add them to the dynamic group. Keep in handy the OCIDs of compartment(Identity → Compartments → Compartment details) and instance(Compute → Instances → Instance details).

The Rule Builder will auto-populate the matching rule text box with the rule.

After the Rules are added, click on ‘Create’ button to create a dynamic group. You can see the dynamic group created.

Create an IAM Policy

Now, create a policy dictating what permissions those instances should receive.

Go to Identity & Security → Policies → Create Policy.

Give Name and Description and click on ‘Show manual Editor’. You can give the statement in the text box available.

The policy gets created.

From the Statements tab, you can verify the policy statements provided, as well as, you can edit the policy further, if required.

Map Principals to Oracle Database users

So far so good, your compute instance is authenticated using an instance principal. However, the database still needs to know: “Who is this principal, and what Oracle Database user should I map it to?”

This mapping is not automatic — you must explicitly create users or mappings in the database. Refer this link on accessing the database using instance principal.

There are two ways to map instance principals to database users:

Option1: Map your instance OCID to an Oracle Database user (exclusive mapping)

You create a dedicated database user for a specific OCI principal (e.g., a compute instance).

CREATE USER mycomputeuser2 IDENTIFIED GLOBALLY
AS 'IAM_PRINCIPAL_OCID=ocid1.instance.oc1.ap-mumbai-1.xxxyyy';

GRANT CREATE SESSION TO mycomputeuser2;

This maps the specified instance OCID to the Oracle Database user mycomputeuser2

Option2: Map your dynamic group to a shared Oracle Database user.

You map a dynamic group of instances to a single shared database user.

CREATE USER shared_user IDENTIFIED GLOBALLY
AS 'IAM_GROUP_NAME=demodg';

GRANT CREATE SESSION TO shared_user;

This means any instance in the specified dynamic group can log in as shared_user.

Also, make sure that External authentication is enabled on ADB and identity_provider_type is set to OCI_IAM. Refer ‘Enable IAM Authentication on ADB’ from this link for the steps involved.

BEGIN
 DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
 type => 'OCI_IAM' );
END;
/
 
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'identity_provider_type';

For this demonstration, I’ve used Option 2 — mapping the dynamic group to a shared database user — which allows all instances in the group to authenticate as shared_user.

Deploy Application on the Compute Instance

Now, deploy your application code to the compute instance. The Python example below demonstrates how to connect to Oracle Autonomous Database using Instance Principal authentication — with no need for user-managed credentials or configuration files.

To enable this, the application uses the oci_tokens plugin in python-oracledb.

To use Instance Principal authentication, set the auth_type to "instanceprincipal" and pass it through extra_auth_params in the connection call.

import oracledb
import oracledb.plugins.oci_tokens
 
connect_string = '''(description= (retry_count=20)(retry_delay=3)
                    (address=(protocol=tcps)(port=1521)
                    (host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=
                    (service_name=myclouddb_high.adb.oraclecloud.com))
                    (security=(ssl_server_dn_match=yes)))'''
 
token_based_auth = {
      "auth_type": "instanceprincipal"
}
 
connection = oracledb.connect(
              dsn=connect_string, 
              extra_auth_params=token_based_auth
)
 
curr = connection.cursor()
sqlText = "select user from dual"
curr.execute(sqlText)
record = curr.fetchone()
print(record)

When the application runs, you can verify that instance principal authentication happens successfully and the connection goes through as shared_user, because the dynamic group, demodg, your compute instance belongs to is mapped to that user in the database.

$ python ip_demo.py
('SHARED_USER',)

Conclusion

Instance Principals make it easier and safer to authenticate from OCI compute instances without managing user credentials. With support now available in python-oracledb 3.2, you can connect to Oracle Autonomous Database in a secure, cloud-native way using short-lived IAM tokens.
Give it a try in your next deployment, and say goodbye to hard-coded secrets :)

Python-oracledb Resources

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is used by frameworks, ORMs, SQL generation libraries, and other projects.

• Installation instructions
• Documentation
• Discussions
• Source code on GitHub
• PyPI

No More Credentials: Connect to Oracle Autonomous Database Using Instance Principals in… was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Oracle Cloud Infrastructure(OCI) provides Identity and Access Management (IAM) to securely authenticate users across its services, enhancing security and streamlining user management. While IAM token-based authentication is commonly used, OCI IAM users can also connect to Autonomous Database (ADB) using an IAM username and password. This guide outlines the steps to configure OCI IAM user credentials, set up the necessary permissions, and connect to ADB using python-oracledb. For detailed configuration steps, refer to the Oracle Database Security Guide, and explore a series of videos that provide guidance on OCI IAM.

Prerequisites

Before configuring IAM for the Oracle ADB instance, it is necessary to first provision an Autonomous Database on Oracle Cloud. This can be easily accomplished using an Always Free account, which provides access at no cost.

IAM Setup

You need to create an IAM user, group, policies, and configure a database password. Below are the detailed steps.

IAM User Creation

Login to your Oracle Cloud console.
Go to Navigation menu → Identity & Security → Identity → Domains[Domain name] → Users. Click on the ‘Create user’ button. This will take you to the screen below. Provide the details for the IAM user by filling in the details for First name, Last name, Username, Email. Click on the ‘Create’ button to create user ‘vkv’.

IAM Group Creation

A new group for the IAM Users should be created. IAM users can be added to the group.

Navigate to Identity & Security → Identity → Domains[Domain name] → Groups. Click on the ‘Create group’ button. Give the Name and Description and select the user(vkv) you want to be in this group. Then click the ‘Create’ button to create a group.

You can see the group being created and the user ‘vkv’ being in that group. More users can be added by clicking ‘Assign user to groups’ if needed.

Create Policy

In order to allow the users in the IAM group to access ADB, we need to create a policy.

Navigate to Identity & Security → Policies. Click on the ‘Create Policy’ button. In the Create Policy page, provide details for the Name, Description. Enable ‘Show manual editor’ toggle in the ‘Policy Builder’ section and insert the policy statement as ‘allow group IAMUsers19Group to use autonomous-database-family in tenancy’.

You can see the policy being created.

Enable IAM Authentication on ADB

The ADB must be configured to use OCI IAM for authentication. To do this, set the Identity Provider type to OCI IAM for the ADB.

Navigate to the provisioned ADB screen and click ‘SQL’ from the ‘Database actions’ drop down.

The SQL page is by default logged in as ADMIN. In the console, execute below commands and verify that the ‘identity_provider_type’ is set to OCI_IAM.

BEGIN
     DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
              type => 'OCI_IAM' );
END;

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'identity_provider_type';

This indicates that External Authentication is enabled on Oracle ADB.

Map User and Role

Next, we need to map a database global user schema to the IAM group. This can be done with the following command:

CREATE USER vkv IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=IAMUsers19Group';

Then, create a global role for database authorization:

CREATE ROLE nwexport_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=IAMUsers19Group';

Finally, grant the required permissions to the newly created global role for the IAM group:

GRANT CREATE SESSION TO nwexport_role;
GRANT DWROLE TO nwexport_role;

IAM User Database Password creation

To access ADB-S, IAM users must configure a new attribute in their user profile — the IAM database password. This password will be used alongside the IAM username, distinct from the existing OCI console password.

Navigate to Identity & Security → Identity → Domains[Domain name] → Users[vkv]. At the bottom left, under ‘Resources’ tab, click on the ‘Database passwords’ field.

Now, click on the ‘Create database password’ button to set a password for the ‘vkv’ user by entering the desired password in the provided field. (To delete the password, click on the three vertical dots at the bottom-right of the screen).

Now that we have the IAM user credentials (username/password), let’s explore how to connect to ADB using these credentials using python-oracledb.

Connect using python-oracledb

Connecting to ADB using IAM user credentials (username/password) follows the same process as a standard user/password connection. Below is a sample for reference that uses mTLS mode for connection.

# Example of connecting to ADB using IAM user and password credentials
 
import oracledb
 
user = "vkv"
pwd = "password1"
wallet_pwd = "password2"
wallet_location = "/home/username/Downloads/Wallet_myclouddb19"
 
dsn = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps) \
       (port=1522)(host=host.oraclecloud.com))(connect_data= \
       (service_name=myclouddb19_high.adb.oraclecloud.com)) \
       (security=(ssl_server_dn_match=yes)))"
 
conn = oracledb.connect(
        user=user, 
        password=pwd, 
        dsn=dsn, 
        wallet_location=wallet_location, 
        wallet_password=wallet_pwd
)
print("Database version is:", conn.version)
 
curr = conn.cursor()
 
sql_txt = "select sys_context('userenv', 'current_user') from dual"
curr.execute(sql_txt)
records = curr.fetchall()
print(f"current_user: {records}")
 
sql_txt = "select * from session_roles"
curr.execute(sql_txt)
records = curr.fetchall()
print(f"session_roles: {records}")
 
sql_txt = "select sys_context('userenv', 'authenticated_identity') from dual"
curr.execute(sql_txt)
records = curr.fetchall()
print(f"authenticated_identity: {records}")
 
sql_txt = "select sys_context('userenv', 'authentication_method') from dual"
curr.execute(sql_txt)
records = curr.fetchall()
print(f"authenication_method: {records}")

Here is the output of the program:

$ python3.12 iam_pwd.py
Database version is: 19.27.0.1.0
current_user: [('VKV',)]
session_roles: [('NWEXPORT_ROLE',), ('DWROLE',)]
authenticated_identity: [('vkv',)]
authentication_method: [('PASSWORD_GLOBAL',)]

To verify session-related details, you can execute the following queries and check their outputs.

The query SELECT sys_context('userenv', 'current_user') FROM dual; will display the current user, ensuring that you’re working under the correct session.

Running SELECT * FROM session_roles; will list the roles assigned to the session, allowing you to confirm the user’s privileges.

You can verify the authenticated identity with SELECT sys_context('userenv', 'authenticated_identity') FROM dual;and determine the method of authentication used with SELECT sys_context('userenv', 'authentication_method') FROM dual;.

These outputs will help you verify the relevant session and authentication details.

Conclusion

OCI IAM authentication provides a secure and centralized way to manage user access to Oracle Autonomous Database. This guide provided step-by-step instructions to set up an OCI IAM user and password and use those credentials to connect to Oracle Autonomous Database. By leveraging IAM authentication, users can centrally manage access, enforce security policies, and eliminate the need for database-specific credentials, resulting in a more secure, scalable, and streamlined authentication process.

Python-oracledb Resources

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html

Documentation: python-oracledb.readthedocs.io/en/latest/index.html

Release Notes: python-oracledb.readthedocs.io/en/latest/release_notes.html

Discussions: github.com/oracle/python-oracledb/discussions

Issues: github.com/oracle/python-oracledb/issues

Source Code Repository: github.com/oracle/python-oracledb

Guide to Creating an OCI IAM User and Password and Connecting to Oracle Autonomous Database was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Oracle Autonomous Database (ADB) supports 1-way TLS authentication, allowing secure connections without requiring a client certificate. However, while connecting using python-oracledb, users may encounter an SSLCertVerificationError, preventing successful authentication. This blog explores practical workarounds to resolve the error and establish a smooth connection.

Before diving into the SSLCertVerificationError, let’s first get a brief overview of 1-way TLS and how ADB can be configured to use it. There are other blogs, like this one, that provide a deeper dive into 1-way TLS, which you can refer to for more details.

What is 1-way TLS ?

1-way TLS (TLS) is a secure communication protocol where only the server’s identity is authenticated using its SSL certificate. The client verifies the server’s certificate to establish trust, but the server does not authenticate the client. This is typically used for secure web browsing or database connections without additional client authentication.

Applications have the capability to connect to an Oracle Cloud Autonomous Database (ADB) without using a wallet on the client side through 1-way TLS.

Configure 1-way TLS on Oracle ADB-S instances

Prerequisites

Before configuring 1-way TLS on an Oracle ADB instance, it is necessary to first provision an Autonomous Database on Oracle Cloud. This can be easily accomplished using an Always Free account, which provides access at no cost.

By default when an ADB instance is provisioned, it is set to use mTLS authentication:

In order to configure for 1-way TLS, one has to change the Access control list from the above Network configuration. Access control list → Edit.

In the Edit access control list window that pops up, enter your IP address in the Values by selecting IP notation type as IP address. An access control list blocks all IP addresses that are not in the list from accessing the database.

IP notation type → IP address → Add my IP address → Save

Now, after adding the IP address to the ACL, you can disable Mutual TLS(mTLS) authentication by unchecking below checkbox.

The Network section now looks:

Get the ADB connection string

You are now ready to use 1-way TLS for Oracle ADB in your applications. As we no longer have the tnsnames.ora file, we need to get an ADB connection string to plug into our Python app.

First, click the ‘Database connection’ button on the ADB instance details page. Under Connection strings, select TLS for TLS authentication and copy the required connection string for the given TNS name.

Database connection → Connection strings → TLS authentication → TLS → Connection string → Show → Copy

Connect using 1-way TLS in Python

Now that we have the connection string, let’s establish a connection to ADB using python-oracledb.

import oracledb
 
username = "admin"
password = "password1"
connect_string = '''(description= (retry_count=20)(retry_delay=3) \
                    (address=(protocol=tcps)(port=1521) \
                    (host=host.oraclecloud.com))(connect_data= \
                    (service_name=myclouddb_high.adb.oraclecloud.com)) \
                    (security=(ssl_server_dn_match=yes)))'''
 
connection = oracledb.connect(
              user=username, 
              password=password, 
              dsn=connect_string
)
print(connection.version)

Understanding SSLCertVerificationError

When above program is run, users may encounter SSLCertVerificationError.

  
ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self-signed certificate in certificate chain

The SSLCertVerificationError indicates that the server's certificate could not be trusted by the client. This can be caused by a missing or outdated CA certificate, an incorrectly configured SSL context, or the client not trusting the certificate presented by the server.

Ensuring that the correct root certificates are available and properly configured helps resolve this issue. For wallet-less connections, the required trust anchors must be present in the system's certificate store. Alternatively, if a wallet-based connection is used, the provided wallet must include the necessary trust points.

Below are a few alternatives to address this issue. You can choose the most suitable solution based on your specific security requirements.

Fix 1 — Ensure required certificates included in the connection configuration

The required certificate is present in the wallet location, and you can explicitly specify the wallet location in the connect() call. You can refer to this guide to learn how to download the wallet files.

connection = oracledb.connect(
              user=username, 
              password=password, 
              dsn=connect_string, 
              wallet_location="/home/username/Downloads/Wallet_myclouddb"
)

Fix 2 — Ensure the certificate is added to the trust store

For TLS (one-way) connections, if you prefer not to provide the wallet, you will need to import the server certificate into the system’s (Python’s) certificate store.

A trust store is a collection of trusted certificates used by the application or system to validate SSL/TLS connections. By adding the self-signed certificate to the trust store, it will be recognized as trusted for future connections.

On Linux, you can add the self-signed certificate to the system trust store(e.g., /etc/ssl/certs/ca-bundle.crt)

From Python, you can add the certificate to your application-specific trust store by creating an SSL context with the certificate explicitly trusted:

1. Extract the server’s certificate chain using openssl:

openssl s_client -connect host.oraclecloud.com:1521 -showcerts

This will output the entire certificate chain. Look for sections like:

-----BEGIN CERTIFICATE -----
(certificate contents)
-----END CERTIFICATE -----

Save the root certificate (the last certificate in the chain) to a file, e.g., /home/username/root_cert.pem

2. In your Python code, create an SSL context that explicitly trusts the saved root certificate. Pass this context to the connect() call when establishing the connection.

import oracledb
import ssl
 
username = "admin"
password = "password1"
connect_string = '''(description= (retry_count=20)(retry_delay=3) \
                    (address=(protocol=tcps)(port=1521) \
                    (host=host.oraclecloud.com))(connect_data= \
                    (service_name=myclouddb_high.adb.oraclecloud.com)) \
                    (security=(ssl_server_dn_match=yes)))'''
 
# Create an SSL context with the root certificate
context = ssl.create_default_context()
context.load_verify_locations("/home/username/root_cert.pem")
 
connection = oracledb.connect(
              user=username, 
              password=password, 
              dsn=connect_string, 
              ssl_context=context
)
 
print(connection.version)

Fix 3 — Use a Custom Trust Store with Certifi

certifi provides a trusted Certificate Authority (CA) bundle, which is a collection of root certificates used to verify the authenticity of server certificates. When you use certifi in your Python application, your connection verifies the server’s SSL certificate against this trusted CA bundle. If the server’s certificate is signed by a CA included in the certifi bundle, SSL verification succeeds.

Make sure you have certifi installed, if not then run ‘pip install certifi’.

Locate the certifi CA Bundle. Below command will output the path to the certifi CA bundle.

$ python3.12 -c "import certifi;print(certifi.where())"
/home/username/latest3.12python/newenv/lib/python3.12/site-packages/certifi/cacert.pem

Now, adjust your Python script to utilize the certifi CA bundle for SSL verification:

import oracledb
import ssl
import certifi
 
username = "admin"
password = "password1"
connect_string = '''(description= (retry_count=20)(retry_delay=3) \
                    (address=(protocol=tcps)(port=1521) \
                    (host=host.oraclecloud.com))(connect_data= \
                    (service_name=myclouddb_high.adb.oraclecloud.com)) \
                    (security=(ssl_server_dn_match=yes)))'''
 
# Create an SSL context using certifi's CA bundle
ssl_context = ssl.create_default_context(cafile=certifi.where())
 
# Connect using the SSL context
connection = oracledb.connect(
              user=username, 
              password=password, 
              dsn=connect_string, 
              ssl_context=ssl_context
)
print(connection.version) 

This solution is effective because:

• The certifi library provides a CA certificate bundle, allowing Python to verify the Oracle server’s certificate.
• Using ssl.create_default_context(cafile=certifi.where()) tells Python to trust the CA bundle provided by certifi, avoiding the need to manually specify root certificates.
• The certifi.where() function offers a set of globally trusted root certificates, so if the server’s certificate chain leads to one of these, the verification will succeed without additional configuration.

Conclusion

Establishing a wallet-less 1-way TLS connection to Oracle Autonomous Database may sometimes lead to SSLCertVerificationError due to missing or untrusted certificates. By ensuring the required certificates are available in the system's or Python's certificate store, you can successfully establish a secure connection without relying on a wallet. The fixes outlined above provide alternative solutions to address SSL verification issues, allowing users to select the approach that best aligns with their security requirements.

Python-oracledb Resources

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html

Documentation: python-oracledb.readthedocs.io/en/latest/index.html

Release Notes: python-oracledb.readthedocs.io/en/latest/release_notes.html

Discussions: github.com/oracle/python-oracledb/discussions

Issues: github.com/oracle/python-oracledb/issues

Source Code Repository: github.com/oracle/python-oracledb

Handling SSLCertVerificationError in 1-way TLS Connection to Oracle Autonomous Database was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Authentication is a critical aspect of database security. Traditionally, applications authenticate to databases using usernames and passwords. However, with cloud-native authentication, we can eliminate password-based authentication and instead use tokens issued by an Identity Provider (IdP). This enhances security, simplifies credential management, and improves compliance.

Cloud-native Authentication allows users to connect to Oracle Database securely without managing passwords. Python-oracledb introduced support for token authentication in version 1.1, and this functionality has now been expanded in the 3.0 release to support cloud-native authentication using Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) tokens and Microsoft Azure tokens. This type of authentication is crucial for securing applications interacting with cloud services like Oracle Cloud Infrastructure (OCI) and Azure. Instead of using static credentials, cloud-native authentication leverages temporary access tokens that are automatically generated and refreshed as needed. This approach improves security by reducing the risk of exposing long-lived credentials.

This blog will guide you on how to use this feature to connect to Oracle Database without a traditional username-password combination.

Why Cloud-native Authentication?

• Improved Security: Eliminates the need to store and manage passwords.
• Simplified Access Management: Uses IAM policies to control Database access.
• Better Integration: Works seamlessly with cloud authentication providers like OCI IAM and Azure.

Using OCI IAM Tokens for Authentication

Prerequisites

Before using cloud-native Authentication with OCI IAM with python-Oracledb, ensure you have.

1. Access to the Oracle Cloud Autonomous Database(ADB) instance that supports IAM authentication.
2. An OCI account with IAM configured.
3. The oci Python SDK installed (pip install oci).
4. The latest python-oracledb package installed(pip install oracledb).

Create Autonomous Database on Oracle Cloud

You can easily create one using an ‘Always Free’ account at no cost. There are other blogs and documentation available that explain this process in detail. You can look at this blog, for instance.

In short, log in to your cloud account, navigate to the menu, and go to ‘Oracle Database.’ Select the ‘Autonomous Transaction Processing’ section, then click ‘Create Autonomous Database.’ Follow the prompts and enter the required details, such as the database name (e.g., ‘myclouddb’).

After creating the database, you need to establish a connection with the Autonomous Database (ADB). You can do this using either mTLS or one-way TLS. You can look at the steps mentioned in this blog for mTLS mode and this one for 1-way TLS.

IAM Setup

The next step is to setup IAM for the ADB created above.

One needs to create an IAM user, group, policies before enabling IAM authentication on ADB. Below are the detailed steps.

IAM User Creation

Login to your Oracle Cloud console.
Go to Navigation menu → Identity & Security → Identity → Domains[Domain name] → Users. This will bring you to below screen, click on the ‘Create user’ button.

Provide the details for the IAM user by filling in the details for First name, Last name, Username, Email. Click on the ‘Create’ button to create user ‘vk23c’.

IAM Group Creation

A new group for the IAM Users should be created. IAM users can be added to the group.

Navigate to Identity & Security → Identity → Domains[Domain name] → Groups. This will bring you the the following page. Click on the ‘Create group’ button.

Give the Name and Description and select the user you want to be in this group. Then click the ‘Create’ button to create a group.

You can see the group being created and the user ‘vk23c’ being in that group. More users can be added by clicking ‘Assign user to groups’ if needed.

Create Policy

In order to allow the users in the IAM group to access ADB, we need to create a policy.

Navigate to Identity & Security → Policies. Click on the ‘Create Policy’ button.

In the ‘Create Policy’ page, provide details for the Name, Description. Enable ‘Show manual editor’ toggle in the ‘Policy Builder’ section and insert the policy statement as ‘allow group IAMUsers23c to use autonomous-database-family in tenancy’

You can see the policy being created.

Enable IAM Authentication on ADB

The ADB needs to be told to use OCI IAM. For this we need to set the Identity Provider type to OCI IAM for the ADB.

Navigate to the provisioned ADB screen and click ‘SQL’ from the ‘Database actions’ drop down.

The SQL page is by default logged in as ADMIN. In the console, execute below commands and verify that the ‘identity_provider_type’ is set to OCI_IAM.

BEGIN
 DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION( 
 type => 'OCI_IAM' );
END;
/

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'identity_provider_type';

This indicates that External Authentication is enabled on Oracle ADB.

Map User and Role

Now we need to map a database global user schema to the IAM group

CREATE USER vk23c IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME = IAMUsers23c';

Create a global role for database authorization

CREATE ROLE export_role IDENTIFIED GLOBALLY AS 'IAM_GROUP_NAME=IAMUsers23c';

And finally, grant the required permissions to the newly created global role for the IAM group.

GRANT CREATE SESSION TO export_role;
GRANT DWROLE TO export_role;

OCI-CLI Configuration

OCI-CLI is a command line interface, it provides the same core functionality as the Oracle Cloud Console.

You may follow the instructions on this page to install the OCI-CLI. Alternatively, you can use the command pip install oci for installation. Once installed, you can verify the success of the installation.

$ oci --version
3.51.1

You need to create an OCI configuration file. For this run,

$ oci setup config

It will ask for few inputs:.

Enter a location for your config[/home/username/.oci/config]:

Give a new location or let it take the default location

Enter user OCID:

One can copy the OCID of the user from Identity & Security -> Domains[Domain name] -> Users[vk23c] -> OCID

Enter a tenancy OCID:

One can copy the tenancy OCI from Profile(top right) -> Tenancy -> OCID

Enter a region by index or name:

You can select from the given list. It’s found on the top right of the OCI Cloud console. Ex: India West(Mumbai)

Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: y

Enter y

Enter a directory for your keys to be created [/home/username/.oci]:

You can give a new location or let it be default.

Enter a name for your key [oci_api_key]:

You can let it to be default value.

Now, you will get the following output. Note down the path to the public key file.

Public key written to: /home/username/.oci/oci_api_key_public.pem

It will now ask for a passphrase for private key. Press Enter.

Enter a passphrase for your private key (empty for no passphrase):

You can see the following output. Both the public and private keys are now generated.

Private key written to: /home/username/.oci/oci_api_key.pem
Config written to /home/username/.oci/conf

The public key is to be uploaded to the OCI cloud. For this, navigate to Identity & Security -> Domains[Domain name] -> Users[vk23c] page.
Go to the API Keys -> Add API Keys -> Paste a public key. Paste the contents of public key file(/home/username/.oci/oci_api_key_public.pem) in there.

As a final step, cross-check that the following details are present in the OCI config file(~/.oci/config)

[DEFAULT]
tenancy=<tenancy_ocid>
user=<user_ocid>
fingerprint=<fingerprint>
key_file=~/.oci/oci_api_key.pem
region=<region>

OCI-CLI configuration is now successful.

Connecting to ADB using Cloud-Native Authentication

Now in order to use the cloud-native authentication with python-oracledb, ensure you have the latest package. You can install or upgrade python-oracledb by running:

python3 -m pip install oracledb --upgrade

See python-oracledb Installation for details.

After successfully installing python-oracledb, you can now connect to ADB without the need for a password. Let’s examine a Python example that demonstrates how authentication is performed using OCI IAM tokens to establish a connection to ADB. To facilitate this, the oci_tokens plugin is imported. It is a built-in hook that handles IAM token generation using the OCI Python SDK. The configuration parameters necessary for the cloud provider APIs are supplied via an additional parameter, extra_auth_params, in the connection call.

Below examples uses TLS mode where you don’t need to provide the wallet location. The configuration parameters are identical to those in the configuration file and are set within the environment, making them available for the application.

import oracledb
import oracledb.plugins.oci_tokens
import os

connect_string = '''(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=xx.xx.oraclecloud.com))(connect_data=(service_name=xxx_myclouddbname.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'''

token_based_auth = {
      "auth_type": "simpleAuthentication",
      "user": os.getenv("PYO_OCI_USER_OCID"),
      "key_file": os.getenv("PYO_OCI_KEYFILE"),
      "fingerprint": os.getenv("PYO_OCI_FINGERPRINT"),
      "tenancy": os.getenv("PYO_OCI_TENANCY"),
      "region": os.getenv("PYO_OCI_REGION"),
      "profile": os.getenv("PYO_OCI_PROFILE")
}

connection = oracledb.connect(dsn=connect_string, extra_auth_params=token_based_auth)

Another supported authentication type for the oci_tokens plugin is shown below. In this method, you only need to provide the configuration file location(default location ~/.oci/config) instead of listing all configuration parameters. The built-in plugin automatically reads the required parameters from the specified file. If no location is provided, the default path (~/.oci/config) is used. This configuration file contains all the essential parameters required for the cloud-provider APIs to generate authentication tokens.

import oracledb
import oracledb.plugins.oci_tokens
import os

connect_string = '''(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=xx.xx.oraclecloud.com))(connect_data=(service_name=xxx_myclouddbname.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'''

token_based_auth = {
      "auth_type": "configFileAuthentication",
      "file_location": os.getenv("PYO_OCI_CONFIG_FILE_LOCATION")
}

connection = oracledb.connect(dsn=connect_string, extra_auth_params=token_based_auth)

Python-oracledb also supports Azure authentication using MSAL (Microsoft Authentication Library). Users can utilize the azure_tokens plugin to connect with Azure OAuth2 tokens.

Here is a sample program illustrating this method (Azure setup is required to run the program. For configuration parameters, more details, refer to the python-oracledb user documentation).

import oracledb
import oracledb.plugins.azure_tokens
import os

connect_string = '''(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=xx.xx.oraclecloud.com))(connect_data=(service_name=xxx_myclouddbname.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'''

token_based_auth = {
        "auth_type": os.getenv("PYO_AZURE_AUTHTYPE"),
        "authority": os.getenv("PYO_AZURE_AUTHORITY"),
        "client_id": os.getenv("PYO_AZURE_CLIENTID"),
        "client_credential": os.getenv("PYO_AZURE_CLIENTSECRET"),
        "scopes": os.getenv("PYO_AZURE_SCOPES")
}

connection = oracledb.connect(dsn=connect_string, extra_auth_params=token_based_auth)

Conclusion

Cloud-native authentication in python-oracledb enhances security by eliminating stored passwords and leveraging OCI IAM tokens for secure database access. This method aligns with modern cloud security standards, ensuring authorized access through identity-based policies. With support for Azure authentication, it also offers flexibility for multi-cloud environments.

As cloud-native applications evolve, adopting modern authentication methods like IAM tokens will reduce security risks, improve operational efficiency, and future-proof your applications against credential-based vulnerabilities. Start using cloud-native authentication in python-oracledb today and take advantage of a simpler, safer, and more scalable authentication mechanism!

Python-oracledb Resources

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html

Documentation: python-oracledb.readthedocs.io/en/latest/index.html

Release Notes: python-oracledb.readthedocs.io/en/latest/release_notes.html

Discussions: github.com/oracle/python-oracledb/discussions

Issues: github.com/oracle/python-oracledb/issues

Source Code Repository: github.com/oracle/python-oracledb

Cloud Native Authentication in python-oracledb using OCI-IAM Tokens was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.