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
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 DUALThe 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.
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
cd $HOME/.oracle/pytoken
source .venv/bin/activate
#!/usr/bin/env python3.14
#!/usr/bin/env python3
Thick Oracle DB Driver Client
$ 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())
$ ./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)
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
oci iam db-token get --profile DBUSER --auth security_token --scope urn:oracle:db::id::<oci_compartment_ocid>::*
$ ./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
$ ./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
$ ./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
$ 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())
$ ./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)
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
oci iam db-token get --profile DBUSER --auth security_token --scope urn:oracle:db::id::<oci_compartment_ocid>::*
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
$ ./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
$ ./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>
I hope that you found this post informative and beneficial.
Blessings
No comments:
Post a Comment