The same customer from my previous
Node.js client post also wanted a python client example that authenticates to the
Oracle AI Database via access token by
Oracle Cloud Identity & Access Management (OCI IAM) integration. This time around, I decided to take a different approach. Instead of starting with a template and refining from there, I used an Artificial Intelligence (AI) Code Generation Tool to create two python scripts where one was based on the thick driver and the second is based on the thin driver.
Driver Differences
There are two fundamental differences between thick and thin drivers. The most significant difference is that the thick driver requires an installation of the
Oracle AI Database Instant Client and the thin driver does not. The second difference is that the thick driver can read and use the TLS certificate from the
Oracle client wallet for TLS connection to the
Oracle AI Database. This is helpful if the TLS certificate is self-signed because the thin driver does not support self-signed certificates. Or, more specifically, the
underlying OpenSSL driver leveraged by the thin driver does not support self-signed certificates.
Benefits Of Using AI Code Generator
In the past, I've used AI code generation tools for refining or understanding a variety of shell scripting use cases. However, I had never asked an AI Code Generator to write sophisticated code like this. Given that I am not proficient in python, this felt like a great opportunity to learn. Therefore, I started with the following prompt:
Write python3.14 script that opens a connection to Oracle database using oracledb module and authenticates using OCI IAM token via oracledb.plugins.oci_tokens module and the database connect string is read in from a TNS alias from tnsnames.ora and executes the following SQL: SELECT SYS_CONTEXT ('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL
The OCI IAM access token is acquired independently of the python script by "oci iam db-token get". The python script can read the orivate key written at /Users/dbuser/.oci/db-token/oci_db_key.pem and the db-token written at: /Users/dbuser/.oci/db-token/token.
I was shocked that it produced solid code and that it almost worked from the first prompt. I iterated a few times to add additional clarity before arriving at the the first script that leverages the thick driver.
The thing that really impressed me beyond getting the core functionality to work was how easy that it was through prompting to add features such as adding arguments to pass to the script to specify things like the TNS alias name to use, where to find the tnsnames.ora, add SQL statement to execute, and much more. Given my lack of python proficiency, that was amazing how quickly and simply that it added those features. And if you watch the process that it goes through to craft the code, it is quite an impressive decision tree and revision process that it goes through all on its own.
The last thing that I'll say is that I was shocked how well it created built-in documentation and usage for each script.
Please note that I didn't write a single line of code for either script.
Python3.14 Virtual Environment
mkdir $HOME/.oracle/pytoken
cd $HOME/.oracle/pytoken
python3.14 -m venv .venv
source .venv/bin/activate
pip install oracledb
pip install oci
pip install requests
Whenever you want to run the script, you will need to activate the virtual environment so that it can find the requisite libraries.
cd $HOME/.oracle/pytoken
source .venv/bin/activate
Having said that, after everything was done, I went back and changed the script headers from python3.14 to python3 and for reasons not yet clear to me, it worked.
From:
#!/usr/bin/env python3.14
To:
I'm not yet sure why it worked but it did for both the thick and thin scripts.
Thick Oracle DB Driver Client
Here's the resulting script
$ cat ociiam-python-thick.py
#!/usr/bin/env python3.14
"""
Connect to Oracle Database using an OCI IAM token, a TNS alias, and an
auto-login Oracle wallet in Thick mode.
Expected locations:
- Instant Client: /Users/dbuser/.oracle/instant_client
- Wallet directory: /Users/dbuser/.oracle/admin/client_wallet
- Token file: /Users/dbuser/.oci/db-token/token
- Private key file: /Users/dbuser/.oci/db-token/oci_db_key.pem
"""
from __future__ import annotations
import argparse
import sys
from pathlib import Path
import oracledb
DEFAULT_TOKEN_FILE = Path("/Users/dbuser/.oci/db-token/token")
DEFAULT_KEY_FILE = Path("/Users/dbuser/.oci/db-token/oci_db_key.pem")
DEFAULT_WALLET_DIR = Path("/Users/dbuser/.oracle/admin/client_wallet")
DEFAULT_INSTANT_CLIENT_DIR = Path("/Users/dbuser/.oracle/instant_client")
def normalize_private_key(key_text: str) -> str:
"""
Return the key in the format expected by Thick-mode OCI IAM auth.
"""
key_lines = []
for line in key_text.splitlines():
line = line.strip()
if not line:
continue
if line == "-----BEGIN PRIVATE KEY-----":
continue
if line == "-----END PRIVATE KEY-----":
continue
key_lines.append(line)
return "".join(key_lines).strip()
def read_oci_iam_token_pair(token_file: Path, key_file: Path) -> tuple[str, str]:
"""Read the OCI IAM access token and private key."""
if not token_file.is_file():
raise FileNotFoundError(f"Token file not found: {token_file}")
if not key_file.is_file():
raise FileNotFoundError(f"Private key file not found: {key_file}")
token = token_file.read_text(encoding="utf-8").strip()
key_text = key_file.read_text(encoding="utf-8").strip()
if not token:
raise ValueError(f"Token file is empty: {token_file}")
if not key_text:
raise ValueError(f"Private key file is empty: {key_file}")
private_key = normalize_private_key(key_text)
if not private_key:
raise ValueError(
f"Private key content was invalid after normalization: {key_file}"
)
return token, private_key
def get_args() -> argparse.Namespace:
parser = argparse.ArgumentParser(
description="Execute SQL using OCI IAM authentication."
)
parser.add_argument(
"--alias",
required=True,
metavar="DB_ALIAS",
help="TNS alias from tnsnames.ora (for example DEVDB_PDB1_SSL_TOKEN).",
)
parser.add_argument(
"--sql",
required=True,
metavar="SQL",
help="SQL statement to execute. Enclose in quotes if it contains spaces.",
)
parser.add_argument(
"--tns-admin",
default="/Users/dbuser/.oracle/admin",
help="Directory containing tnsnames.ora and optionally sqlnet.ora.",
)
parser.add_argument(
"--wallet-dir",
default=str(DEFAULT_WALLET_DIR),
help="Directory containing the auto-login wallet (cwallet.sso).",
)
parser.add_argument(
"--instant-client-dir",
default=str(DEFAULT_INSTANT_CLIENT_DIR),
help="Directory containing the Oracle Instant Client libraries.",
)
parser.add_argument(
"--token-file",
type=Path,
default=DEFAULT_TOKEN_FILE,
help=f"OCI IAM token file (default: {DEFAULT_TOKEN_FILE})",
)
parser.add_argument(
"--key-file",
type=Path,
default=DEFAULT_KEY_FILE,
help=f"OCI IAM private key file (default: {DEFAULT_KEY_FILE})",
)
return parser.parse_args()
def validate_paths(
tns_admin: Path,
wallet_dir: Path,
instant_client_dir: Path,
) -> None:
if not tns_admin.is_dir():
raise FileNotFoundError(
f"TNS admin directory not found: {tns_admin}"
)
if not wallet_dir.is_dir():
raise FileNotFoundError(
f"Wallet directory not found: {wallet_dir}"
)
if not instant_client_dir.is_dir():
raise FileNotFoundError(
f"Instant Client directory not found: {instant_client_dir}"
)
cwallet = wallet_dir / "cwallet.sso"
if not cwallet.is_file():
raise FileNotFoundError(
f"Auto-login wallet not found: {cwallet}"
)
libclntsh = instant_client_dir / "libclntsh.dylib"
if not libclntsh.exists():
raise FileNotFoundError(
f"Oracle Client library not found: {libclntsh}"
)
def print_result_set(cursor: oracledb.Cursor) -> None:
"""Pretty-print a result set."""
columns = [col[0] for col in cursor.description]
print("\t".join(columns))
print("-" * (len(columns) * 16))
for row in cursor:
print("\t".join("" if v is None else str(v) for v in row))
def main() -> int:
args = get_args()
tns_admin = Path(args.tns_admin).expanduser().resolve()
wallet_dir = Path(args.wallet_dir).expanduser().resolve()
instant_client_dir = Path(args.instant_client_dir).expanduser().resolve()
try:
validate_paths(
tns_admin,
wallet_dir,
instant_client_dir,
)
except Exception as exc:
print(f"ERROR: {exc}", file=sys.stderr)
return 2
try:
oracledb.init_oracle_client(
lib_dir=str(instant_client_dir),
config_dir=str(tns_admin),
)
except oracledb.Error as exc:
print(
f"ERROR: Failed to initialize Oracle Client: {exc}",
file=sys.stderr,
)
return 1
try:
token, private_key = read_oci_iam_token_pair(
args.token_file.expanduser().resolve(),
args.key_file.expanduser().resolve(),
)
except Exception as exc:
print(f"ERROR: {exc}", file=sys.stderr)
return 2
sql = args.sql.strip().rstrip(";")
try:
with oracledb.connect(
dsn=args.alias,
access_token=(token, private_key),
externalauth=True,
wallet_location=str(wallet_dir),
) as conn:
with conn.cursor() as cur:
cur.execute(sql)
if cur.description:
print_result_set(cur)
else:
print(
f"Statement executed successfully. "
f"Rows affected: {cur.rowcount}"
)
except oracledb.Error as exc:
print(f"Oracle error: {exc}", file=sys.stderr)
return 1
return 0
if __name__ == "__main__":
raise SystemExit(main())
Here's the usage as provided by the script:
$ ./ociiam-python-thick.py --help
usage: ociiam-python-thick.py [-h] --alias DB_ALIAS --sql SQL [--tns-admin TNS_ADMIN] [--wallet-dir WALLET_DIR]
[--instant-client-dir INSTANT_CLIENT_DIR] [--token-file TOKEN_FILE] [--key-file KEY_FILE]
Execute SQL using OCI IAM authentication.
options:
-h, --help show this help message and exit
--alias DB_ALIAS TNS alias from tnsnames.ora (for example DEVDB_PDB1_SSL_TOKEN).
--sql SQL SQL statement to execute. Enclose in quotes if it contains spaces.
--tns-admin TNS_ADMIN
Directory containing tnsnames.ora and optionally sqlnet.ora.
--wallet-dir WALLET_DIR
Directory containing the auto-login wallet (cwallet.sso).
--instant-client-dir INSTANT_CLIENT_DIR
Directory containing the Oracle Instant Client libraries.
--token-file TOKEN_FILE
OCI IAM token file (default: /Users/dbuser/.oci/db-token/token)
--key-file KEY_FILE OCI IAM private key file (default: /Users/dbuser/.oci/db-token/oci_db_key.pem)
Here's an example flow that incorporates starting an OCI IAM session, getting an OCI IAM access token and executing the script using an existing tnsnames.ora file and client wallet.
First, initiate an OCI IAM session with the OCI command line tool.
oci session authenticate --profile-name DBUSER --tenancy-name <oci_tenancy_name> --region <oci_region> --identity-provider-name <oci_identity_domain_name>
Please switch to newly opened browser window to log in!
You can also open the following URL in a web browser window to continue:
https://login.us-phoenix-1.oraclecloud.com/v1/oauth2/authorize?action=login...
Service
Completed browser authentication process!
Config written to: /Users/dbuser/.oci/config
Next, I request an OCI IAM access token.
oci iam db-token get --profile DBUSER --auth security_token --scope urn:oracle:db::id::<oci_compartment_ocid>::*
Once authenticated and have the access token, then we can run the the python script with requisite arguments. First, lets authenticate and show the database user name:
$ ./ociiam-python-thick.py --alias DEVDB_PDB1_SSL_TOKEN --tns-admin /Users/dbuser/.oracle/admin --wallet-dir /Users/dbuser/.oracle/admin/client_wallet --instant-client-dir /Users/dbuser/.oracle/instant_client --sql "SELECT SYS_CONTEXT ('USERENV','CURRENT_USER') FROM DUAL"
SYS_CONTEXT('USERENV','CURRENT_USER')
----------------
ALLDBUSERS
Next, lets authenticate and show the authenticated user:
$ ./ociiam-python-thick.py --alias DEVDB_PDB1_SSL_TOKEN --tns-admin /Users/dbuser/.oracle/admin --wallet-dir /Users/dbuser/.oracle/admin/client_wallet --instant-client-dir /Users/dbuser/.oracle/instant_client --sql "SELECT SYS_CONTEXT ('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL"
SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
----------------
<oci_identity_domain_name>/dbuser@dbauthdemo.com
Next, lets authenticate and show the enterprise user:
$ ./ociiam-python-thick.py --alias DEVDB_PDB1_SSL_TOKEN --tns-admin /Users/dbuser/.oracle/admin --wallet-dir /Users/dbuser/.oracle/admin/client_wallet --instant-client-dir /Users/dbuser/.oracle/instant_client --sql "SELECT SYS_CONTEXT ('USERENV','ENTERPRISE_IDENTITY') FROM DUAL"
SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
----------------
<oci_user_ocid>
Thin Oracle DB Driver Client
With the thin driver client python script, I had to iterate through several prompts to refine to a usable script. The key issue that I had to wrestle with was how connect over TLS to the Oracle AI Database server where the server's certificate is a self-signed certificate. The short answer is that it is not possible without overriding the trust model to ignore certificate verification. Please note that disregarding certificate verification is NOT SECURE and should not be done. That said, my test environment did not have a Certificate Authority (CA) signed certificate. Therefore, I had to implement the override parameter (--insecure) to demonstrate successful connection and SQL execution.
Here's the resulting script
$ cat ./ociiam-python-thin.py
#!/usr/bin/env python3.14
"""
Execute SQL against Oracle Database using OCI IAM token auth in Thin mode,
with a TNS alias and optional TLS certificate verification bypass.
This version does NOT use Oracle wallets or client-side mutual TLS.
Expected locations:
- TNS admin directory: /Users/dbuser/.oracle/admin
- Token file: /Users/dbuser/.oci/db-token/token
- Private key file: /Users/dbuser/.oci/db-token/oci_db_key.pem
Use --insecure only for testing.
"""
from __future__ import annotations
import argparse
import ssl
import sys
from pathlib import Path
import oracledb
DEFAULT_TOKEN_FILE = Path("/Users/dbuser/.oci/db-token/token")
DEFAULT_KEY_FILE = Path("/Users/dbuser/.oci/db-token/oci_db_key.pem")
DEFAULT_TNS_ADMIN = Path("/Users/dbuser/.oracle/admin")
def read_oci_iam_token_pair(token_file: Path, key_file: Path) -> tuple[str, str]:
if not token_file.is_file():
raise FileNotFoundError(f"Token file not found: {token_file}")
if not key_file.is_file():
raise FileNotFoundError(f"Private key file not found: {key_file}")
token = token_file.read_text(encoding="utf-8").strip()
private_key = key_file.read_text(encoding="utf-8").strip()
if not token:
raise ValueError(f"Token file is empty: {token_file}")
if not private_key:
raise ValueError(f"Private key file is empty: {key_file}")
if "-----BEGIN" not in private_key or "-----END" not in private_key:
raise ValueError(
f"Private key file does not appear to be PEM-formatted: {key_file}"
)
return token, private_key
def get_args() -> argparse.Namespace:
parser = argparse.ArgumentParser(
description="Execute SQL using OCI IAM authentication in Thin mode."
)
parser.add_argument(
"--alias",
required=True,
metavar="DB_ALIAS",
help="TNS alias from tnsnames.ora (for example DEVDB_PDB1_SSL_TOKEN).",
)
parser.add_argument(
"--sql",
required=True,
metavar="SQL",
help="SQL statement to execute. Enclose in quotes if it contains spaces.",
)
parser.add_argument(
"--tns-admin",
default=str(DEFAULT_TNS_ADMIN),
help="Directory containing tnsnames.ora and optionally sqlnet.ora.",
)
parser.add_argument(
"--trust-cert",
type=Path,
default=None,
metavar="PEM_FILE",
help=(
"Optional PEM file containing a trusted CA certificate chain. "
"Ignored when --insecure is used."
),
)
parser.add_argument(
"--insecure",
action="store_true",
help=(
"Disable TLS server certificate verification. "
"FOR TESTING ONLY."
),
)
parser.add_argument(
"--token-file",
type=Path,
default=DEFAULT_TOKEN_FILE,
help=f"OCI IAM token file (default: {DEFAULT_TOKEN_FILE})",
)
parser.add_argument(
"--key-file",
type=Path,
default=DEFAULT_KEY_FILE,
help=f"OCI IAM private key file (default: {DEFAULT_KEY_FILE})",
)
return parser.parse_args()
def validate_paths(tns_admin: Path, trust_cert: Path | None) -> None:
if not tns_admin.is_dir():
raise FileNotFoundError(f"TNS admin directory not found: {tns_admin}")
if trust_cert is not None and not trust_cert.is_file():
raise FileNotFoundError(f"Trust certificate file not found: {trust_cert}")
def build_ssl_context(trust_cert: Path | None, insecure: bool) -> ssl.SSLContext:
"""
Build the SSL context.
insecure=True disables certificate verification and hostname verification.
This should only be used for testing.
"""
if insecure:
context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
context.check_hostname = False
context.verify_mode = ssl.CERT_NONE
return context
context = ssl.create_default_context()
if trust_cert is not None:
context.load_verify_locations(cafile=str(trust_cert))
return context
def print_result_set(cursor: oracledb.Cursor) -> None:
columns = [col[0] for col in cursor.description]
print("\t".join(columns))
print("-" * max(8, len(columns) * 16))
for row in cursor:
print("\t".join("" if v is None else str(v) for v in row))
def main() -> int:
args = get_args()
tns_admin = Path(args.tns_admin).expanduser().resolve()
trust_cert = args.trust_cert.expanduser().resolve() if args.trust_cert else None
try:
validate_paths(tns_admin, trust_cert)
except Exception as exc:
print(f"ERROR: {exc}", file=sys.stderr)
return 2
if args.insecure:
print(
"WARNING: TLS certificate verification is DISABLED. "
"This should only be used for testing.",
file=sys.stderr,
)
try:
token, private_key = read_oci_iam_token_pair(
args.token_file.expanduser().resolve(),
args.key_file.expanduser().resolve(),
)
except Exception as exc:
print(f"ERROR: {exc}", file=sys.stderr)
return 2
# Configure tnsnames.ora lookup for Thin mode.
oracledb.defaults.config_dir = str(tns_admin)
ssl_context = build_ssl_context(trust_cert, args.insecure)
sql = args.sql.strip().rstrip(";")
try:
with oracledb.connect(
dsn=args.alias,
ssl_context=ssl_context,
access_token=(token, private_key),
externalauth=True,
) as conn:
with conn.cursor() as cur:
cur.execute(sql)
if cur.description:
print_result_set(cur)
else:
print(
f"Statement executed successfully. Rows affected: {cur.rowcount}"
)
except oracledb.Error as exc:
print(f"Oracle error: {exc}", file=sys.stderr)
return 1
return 0
if __name__ == "__main__":
raise SystemExit(main())
Here's the usage as provided by the script:
$ ./ociiam-python-thin.py --help
usage: ociiam-python-thin.py [-h] --alias DB_ALIAS --sql SQL [--tns-admin TNS_ADMIN] [--trust-cert PEM_FILE] [--insecure]
[--token-file TOKEN_FILE] [--key-file KEY_FILE]
Execute SQL using OCI IAM authentication in Thin mode.
options:
-h, --help show this help message and exit
--alias DB_ALIAS TNS alias from tnsnames.ora (for example DEVDB_PDB1_SSL_TOKEN).
--sql SQL SQL statement to execute. Enclose in quotes if it contains spaces.
--tns-admin TNS_ADMIN
Directory containing tnsnames.ora and optionally sqlnet.ora.
--trust-cert PEM_FILE
Optional PEM file containing a trusted CA certificate chain. Ignored when --insecure is used.
--insecure Disable TLS server certificate verification. FOR TESTING ONLY.
--token-file TOKEN_FILE
OCI IAM token file (default: /Users/dbuser/.oci/db-token/token)
--key-file KEY_FILE OCI IAM private key file (default: /Users/dbuser/.oci/db-token/oci_db_key.pem)
Here's an example flow that incorporates starting an OCI IAM session, getting an OCI IAM access token and executing the script using an existing tnsnames.ora file and client wallet.
First, initiate an OCI IAM session with the OCI command line tool.
oci session authenticate --profile-name DBUSER --tenancy-name <oci_tenancy_name> --region <oci_region> --identity-provider-name <oci_identity_domain_name>
Please switch to newly opened browser window to log in!
You can also open the following URL in a web browser window to continue:
https://login.us-phoenix-1.oraclecloud.com/v1/oauth2/authorize?action=login...
Service
Completed browser authentication process!
Config written to: /Users/dbuser/.oci/config
Next, I request an OCI IAM access token.
oci iam db-token get --profile DBUSER --auth security_token --scope urn:oracle:db::id::<oci_compartment_ocid>::*
Once in the python3.14 virtual environment, authenticated with oci iam, and have the OCI IAM access token, then we can run the the python script with requisite arguments.
First, lets authenticate and show the database user name:
$ ./ociiam-python-thin.py --insecure --alias DEVDB_PDB1_SSL_TOKEN --tns-admin /Users/dbuser/.oracle/admin --sql "SELECT SYS_CONTEXT ('USERENV','CURRENT_USER') FROM DUAL"
WARNING: TLS certificate verification is DISABLED. This should only be used for testing.
SYS_CONTEXT('USERENV','CURRENT_USER')
----------------
ALLDBUSERS
Next, lets authenticate and show the authenticated user:
$ ./ociiam-python-thin.py --insecure --alias DEVDB_PDB1_SSL_TOKEN --tns-admin /Users/dbuser/.oracle/admin --sql "SELECT SYS_CONTEXT ('USERENV','AUTHENTICATED_IDENTITY') FROM DUAL"
WARNING: TLS certificate verification is DISABLED. This should only be used for testing.
SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
----------------
<oci_identity_domain_name>/dbuser@dbauthdemo.com
Next, lets authenticate and show the enterprise user:
$ ./ociiam-python-thin.py --insecure --alias DEVDB_PDB1_SSL_TOKEN --tns-admin /Users/dbuser/.oracle/admin --sql "SELECT SYS_CONTEXT ('USERENV','ENTERPRISE_IDENTITY') FROM DUAL"
WARNING: TLS certificate verification is DISABLED. This should only be used for testing.
SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
----------------
<oci_user_ocid>
Thanks for going along this AI Code Generation journey with me.
I hope that you found this post informative and beneficial.
Blessings
