Configure Python Applications

10.3 Configure Python Applications

The python-oracledb driver supports two approaches for propagating the end-user security context payload from your Python application to the database: API extension methods and a configuration-driven Service Provider Interface (SPI). API calls take precedence over provider-based configurations.

Topics:

10.3.1 Use the API Extension Methods

If you want to supply the end-user security context payload directly in your application code, use the API extension methods.

The python-oracledb driver extends the standard Python Database API with the Connection methods to set and clear the end-user security context payload. Currently, you can use this feature only in python-oracledb's Thin mode.

To supply the security context payload using the python-oracledb driver, perform the following tasks.

Build the security context payload.
In your application logic, use the create_end_user_security_context method to construct the security context object. You must provide both the end-user identity and the database-access token using the following supported values. Other parameters are optional, such as additional data roles to activate and end-user context attributes.
- End-user identity (end_user_identity): Provide one of the following:
  - end_user_token: For users managed in IAM.
  - end_user_name and key: For users managed in the application's own user store. You can create the user name using the create_end_user_pk command, and key is the server-side string (also referred to as the security context lookup key).
- Database-access token (database_access_token): Provide one of the following:
  - On-behalf-of (OBO) token: Obtained using the end-user token as an assertion.
  - Client credentials token: Obtained using the application's credentials.
- Data roles (data_roles): Optional. Additional data roles that the application can enable for the end-user security context, beyond those mapped to application roles in IAM and those enabled by default for the application identity.
- End-user context attributes (attributes): Optional. A dictionary of application-defined key-value pairs to include in the security context.
```
import oracledb
# ... authentication logic to retrieve tokens ...
data_roles = ["hr_role"]
attrs = {
    "emp_id": 3,
    "org_id": 5
}
ctx_attrs = {
        "hr.hcm_context": attrs
}  
# Build the end-user security context
user_context = oracledb.create_end_user_security_context(
    end_user_identity = <user_token_or_local_end_user_name_and_key>,  
    database_access_token = <db_token_issued_by_an_IAM>,
    data_roles = data_roles,   #optional
    attributes = ctx_attrs     #optional
)
```

Obtain a connection.

You can obtain a connection using oracledb.connect() (standalone connection) or pool.acquire() (connection pool).

connection = oracledb.connect(user="db_user", password=userpwd,
    dsn="orclpdb", config_dir="/opt/oracle/config",
    wallet_location="location_of_pem_file", wallet_password=walletpw)

Set and clear the security context payload on the connection.

Add the security context payload to the connection before executing queries, and clear it afterward to return the connection to its original state. The security context payload is piggybacked on the next database operation.
1. Set the security context payload.
```
connection.set_end_user_security_context(user_context)
```
2. Execute your SQL queries.
```
with connection.cursor() as cursor:
    cursor.execute("select 1 from dual")
    row = cursor.fetchone()
    print(row)
```
3. Clear the security context payload.
  As a best practice, clear the end-user security context payload explicitly before closing or releasing the connection. This ensures the next user does not inherit the previous user's privileges. If you do not call this method, the driver clears the security context payload only when the connection is closed with connection.close() or released back to the pool with pool.release().
```
connection.clear_end_user_security_context()
connection.close()
```

Note:

If no end-user identity is present, the plug-in does not attach the end-user security context payload, and the application receives a least-privileged connection or session (standard behavior) and no error is thrown.

For more information about the python-oracledb driver's support for Oracle Deep Data Security, see the python-oracledb documentation.

10.3.2 Use the Service Provider Interface

The Service Provider Interface (SPI) approach enables your Python application to supply the end-user security context payload with no code changes to your SQL or Object Relational Mapping (ORM) layer.

The python-oracledb driver's end_user_sec_provider plug-in acts as the SPI provider. By importing the plug-in and adding a configuration block to your application's database settings, you can enable your application to automatically propagate the security context payload on every database operation. The plug-in supports both the client_credentials and on_behalf_of (OBO) authentication flows for Microsoft Entra ID, and the client_credentials flow for OCI IAM.

To supply the security context payload using the SPI approach, perform the following tasks.

Install the driver.
Ensure the python-oracledb driver is installed. The plug-in ships with the driver and uses the driver’s Thin mode for security context payload propagation.
```
python -m pip install oracledb
```
Import the Oracle Deep Data Security plug-in.

Import the plug-in to register its parameter hook with the driver.
```
import oracledb.plugins.end_user_sec_provider
```

Configure the Oracle Deep Data Security plug-in.

Provide the identity provider settings in the end_user_sec_params configuration block. This block specifies the token provider type, authentication flow, IAM credentials, and optional data roles or context attributes. The location of this configuration block depends on your integration mode, which is detailed in the subsequent step.

Example configuration:

"end_user_sec_params": {
    "spi_type": "azure_tokens",
    "auth_flow": "client_credentials",
    "authority": "https://login.microsoftonline.com/<tenant_id>",
    "client_id": <client_id>,
    "client_credential": <secret>,
    "scopes": <scopes>,
    "data_roles": ["hr_role"],  #optional
    "attributes": {
        "hr.hcm_context": {"emp_id": 3, "org_id": 5}
    }
}  #optional

Parameter	Description
`spi_type`	The name of the pre-supplied python-oracledb plug-in to use for database-access token acquisition. Valid values: `azure_tokens` (Microsoft Entra ID) or `oci_tokens` (OCI IAM).
`auth_flow`	The OAuth 2.0 grant type for acquiring the database-access token. Valid values: `client_credentials` or `on_behalf_of`. OCI IAM supports `client_credentials` only.
`authority`	The token endpoint authority URL. For example, for Entra ID, this is: `https://login.microsoftonline.com/<tenant_id>`.
`client_id`	The Application (Client) ID of the application registered in your IAM.
`client_credential`	The client secret for the application. Retrieve this value from an approved secret storage mechanism.
`scopes`	The scope required to access the database resource.
`data_roles`	(Optional) A list of additional data roles that the application can enable for the end-user security context.
`attributes`	(Optional) A dictionary of application-defined key-value pairs to include in the security context.

Configure the integration mode.

For every request or execution context that interacts with the database, your application must supply an end-user identity to the plug-in. The identity payload is either an end-user token (for IAM-managed users) or a user name and key (for users managed in the application's own user store). How you supply this identity and where you place the end_user_sec_params configuration block depends on your application framework.

If both the end-user identity and end_user_sec_params are present, database calls execute under a delegated end-user security context. If either is missing, database calls proceed without a delegated end-user security context, and the application receives a standard connection with default privileges.

The following subsections describe three common integration patterns: Django, Flask, and other Python web frameworks. Pick the one that matches your application and adapt the example accordingly.
1. Django applications
  Register the plug-in middleware in your MIDDLEWARE setting, and place the end_user_sec_params block inside your database configuration under DATABASES['default']['OPTIONS']['extra_auth_params'] in the settings.py file. Your application is responsible for authenticating the end user and setting the end-user identity as a cookie named identity. The identity should be available on each request or execution. Its value is either the end-user token issued by your IAM system (for IAM-managed users) or a user name and key (for users managed in the application's own user store).
  
  The middleware reads this cookie from the request context (for example, request.COOKIES["identity"]) and makes it available to the plug-in for each database operation.
  
  Alternatively, after the end user authenticates with your application, your application can include the end user's bearer token in the Authorization header of the REST API call. The middleware then retrieves this token from the header (for example, request.headers.get("Authorization")).
```
# settings.py
import oracledb.plugins.end_user_sec_provider

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    ...
    'oracledb.plugins.end_user_sec_provider.EndUserSecMiddleware',
]
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'db_name',
        'USER': 'hr_db',
        'PASSWORD': '<hr_db connection pool user password>',
        'OPTIONS': {
            "extra_auth_params": {
                "end_user_sec_params": {
                    "spi_type": "azure_tokens",
                    "auth_flow": "client_credentials",
                    "authority": "https://login.microsoftonline.com/<tenant_id>",
                    "client_id": <client_id>,
                    "client_credential": <secret>,
                    "scopes": <scopes>,
                    "data_roles": ["hr_role"],             # optional
                    "attributes": { "hr.hcm_context": {"emp_id": 3, "org_id": 5}} # optional
                }
            }
        }
    }
}
```
2. Flask applications
  
  Unlike Django, Flask does not use a middleware chain. So, python-oracledb's Oracle Deep Data Security plug-in is wired up inside the view function for each request. In each handler that talks to the database, extract the end-user identity from the incoming request, pass it to set_end_user_identity(), and then call oracledb.connect() with the end_user_sec_params block supplied as extra_auth_params.
  The end-user identity is typically an OAuth 2.0 access token carried in the Authorization request header when the application receives the token from an upstream caller, or a token the Flask application acquires directly using a library such as MSAL when the application authenticates the end user itself. Either way, the token becomes the value passed to set_end_user_identity().
```
from flask import Flask, request, jsonify
import oracledb
import oracledb.plugins.end_user_sec_provider as deep_data_sec_provider

app = Flask(__name__)

@app.route("/myapi", methods=["GET"])
def myfun():
    token = #get token from request headers if frontend passes token in authorization headers or
            #get token from msal if frontend is not there

    deep_data_sec_provider.set_end_user_identity(token)

    conn = oracledb.connect(
                              user="hr_db",
                              password="<database password>",
                              dsn="<your connect string>",
                              wallet_location="path to wallet file",
                              wallet_password="<wallet password>",
                              extra_auth_params={
                                  "end_user_sec_params": {
                                      "spi_type": 'azure_tokens',
                                      "auth_flow": 'client_credentials',
                                      "client_id":"<client_id>",
                                      "client_credential":"<secret>",
                                      "authority":"https://login.microsoftonline.com/<tenant_id>",
                                      "scopes":"<scopes>",
                                      "data_roles": ["hr_role"],        # optional
                                      "attributes": { "hr.hcm_context": {"emp_id": 3, "org_id": 5}}   # optional
                                  }
                              }
                            )
    
    data = mygetdata(conn)
    
    return jsonify(data), 200
```
  The plug-in reads the identity set by set_end_user_identity(), acquires the database-access token using the configured end_user_sec_params, and attaches the end-user security context payload to every database call made on that connection.
3. Other web frameworks
  For other Python web frameworks, such as FastAPI, apply the same pattern as the Flask example above: set the end-user identity for the current execution context with set_end_user_identity() before acquiring a connection, and pass the end_user_sec_params block in the extra_auth_params parameter of your connection or pool-creation call. The hook point (middleware, dependency, before-request handler) depends on your framework; the identity-and-configuration handoff to the plug-in is the same.
  
  The following example shows this pattern applied to a FastAPI application.
```
import oracledb
import oracledb.plugins.end_user_sec_provider as deepsec_provider
from fastapi import FastAPI, Header

app = FastAPI()

@app.get("/myapi")
def myfun(authorization: Optional[str] = Header(default="")):
    token = #get token from request headers if frontend passes token in authorization headers

    deepsec_provider.set_end_user_identity(token)
    conn = oracledb.connect(
                              user="hr_db",
                              password="<database password>",
                              dsn="<your connect string>",
                              wallet_location="path to wallet file",
                              wallet_password="<wallet password>",
                              extra_auth_params={
                                  "end_user_sec_params": {
                                      "spi_type": 'azure_tokens',
                                      "auth_flow": 'client_credentials',
                                      "client_id":"<client_id>",
                                      "client_credential":"<secret>",
                                      "authority":"https://login.microsoftonline.com/<tenant_id>",
                                      "scopes":"<scopes>"
                                  }
                              }
                            )
    
    data = mygetdata(conn)
    return data
```
  The plug-in acquires the database-access token and attaches the security context payload automatically.

For more information about the python-oracledb driver's support for Oracle Deep Data Security, see the python-oracledb documentation.