14.4 How the Database Server Manages an End-User Security Context

When a client driver sends an EndUserSecurityContext payload during a SQL operation, the database server automatically executes the steps described in this section.

Use the information provided here for reference and diagnostic purposes; no administrator action is required during this process.

14.4.1 Scenario 1: Token-Based (IAM-Managed) End User

If your end users authenticate using OAuth 2.0 access tokens issued by IAM (such as Microsoft Entra ID or OCI IAM), the database must validate these tokens to ensure that the request originates from a trusted source.

This scenario has the following two variants, depending on how an end user connects to the database:
  • Scenario 1a — Through an application: The end user accesses the database through an application, and the application supplies both the end-user token and the database-access token through client drivers.
  • Scenario 1b — Direct logon (no application): The end user connects directly to the database using their end-user token, with no application involved (often used by data analysts or developers).

Scenario 1a — Through an application

This flow triggers when your application sends the EndUserSecurityContext payload using a client driver. The payload contains an IAM-issued end-user token and a database-access token that identifies the application. Behind the scenes, the database server performs the following steps:
  1. Extracts the payload: The server pulls the end-user token, the database-access token, the application roles, and any end-user context attributes from the encoded payload.
  2. Validates the database-access token: The server verifies the database-access token (which must be an on-behalf-of (OBO) token or a client credentials token). It checks the token's signature and expiration, and validates the aud (audience) claim against the database's initialization parameter, identity_provider_config (for Entra ID) or identity_provider_oauth_config (for OCI IAM). If validation fails, the server rejects the attachment attempt.
  3. Validates the end-user token: The server checks the end-user token's signature and expiration, then extracts the user name. It looks for the upn claim for Entra ID, or the sub claim for OCI IAM.
  4. Resolves data roles: The server determines which data roles to activate by combining two sources:
    • IAM roles: The server extracts the role claims from the end-user token (the roles claim for Entra ID, or a configured group claim for OCI IAM) and finds the corresponding data roles in the database. For each claim value, the server looks for a data role with a MAPPED TO clause matching the claim. Matching data roles are enabled. For details on role resolution across different IAM systems, see examples in Configure Data Roles.
    • Application data roles: The server compares the additional or common data roles requested by the application against the data roles granted to the application identity. Granted data roles that are enabled (the default option) are always included in the security context (even if not requested), while granted data roles that are disabled on creation are only included if explicitly requested, allowing privilege elevation. Requests for ungranted data roles are ignored. See Grant and Revoke Data Roles for examples of granting data roles to an application identity.

    Note:

    • In a data role definition (for example, CREATE DATA ROLE manager_role MAPPED TO 'AZURE_ROLE=manager'), the comparison between the role name in the IAM token and the role name specified after the prefix (for example, manager in AZURE_ROLE=manager) is case-insensitive.
    • With Microsoft Entra ID claims, your application roles appear in the roles claim. If you need to use group information, you must explicitly configure your IAM token to include it.
    • With OCI IAM claims, your group information appears in a custom claim, provided you have configured your IAM domain to populate it.
  5. Manages the security context cache: To optimize performance, the server does the following actions:
    • The server uniquely identifies an end-user security context by the combination of its end-user token, database-access token, and data role fields.
    • If an exact matching security context (same end-user token, database-access token, and data roles) is already attached to the database session, the server leaves it attached. If the tokens or data roles have changed, the server detaches the existing security context and attaches the new one.
    • If a matching security context exists but isn't currently attached, the server reattaches the security context to the database session.
    • If an existing security context has a stale role-graph (for example, a data role was modified), the server detaches it and reattaches a refreshed security context.
    • If no match exists, the server creates a new security context, assigns the extracted user name as the external user identity, enables the resolved set of data roles, and attaches the security context with the provided end-user context attributes.
  6. Handles security context attach, detach, and cleanup: The database automatically manages the detachment and reattachment of an end-user security context to a database session through the following mechanisms:
    • Connection release: When your application code releases the connection, the security context is detached from the database session.
    • Security context replacement: When your application reuses a connection, the database seamlessly replaces the previous security context with the new one. If the incoming request does not use an end-user security context, the server simply detaches the old one.
    • Garbage collection: The database automatically garbage-collects any end-user security contexts that remain inactive (not attached to any database session) after a timeout duration of one hour.

Scenario 1b — Direct logon (no application)

If your users require direct SQL access without an application, they can log in using their end-user token. When a user presents an end-user token for direct logon, the database first looks for a standard schema mapped to that user. If no schema match is found, the server executes the direct-logon flow. It performs the following tasks:

  1. Matches data roles: The server retrieves all the data roles whose MAPPED TO clause matches the role claims inside the end-user token.
  2. Retrieves database roles: For every matching data role, the server retrieves all standard database roles granted to it.
  3. Evaluates privileges and establishes security context: If any of those database roles contain the CREATE SESSION privilege, the server permits the direct logon and establishes the end-user security context with those data roles active.

    With direct logon, the end-user security context is destroyed when the database session closes.

Note:

Oracle Deep Data Security (Deep Sec) session behavior
  • Current user state: After the security context attaches successfully, querying sys_context('userenv', 'current_user') returns XS$NULL. All of your other USERENV attributes remain entirely unchanged.
  • End-user identity: You can identify the current user by checking ORA_END_USER_CONTEXT.username, which reflects the upn claim if you use Microsoft Entra ID, or the sub claim if you use OCI IAM.
  • View active roles: You can query the V$END_USER_DATA_ROLE view to list the specific data roles currently active (derived from the end-user token's role claims).
  • View all end-user security contexts: A DBA can view the list of end-user security contexts in the database using the DBA_END_USER_SECURITY_CONTEXTS view.

14.4.2 Scenario 2: Locally Managed End User

When end users are managed locally (with no IAM system involved), the database server resolves the end-user identity and data roles entirely within the database.

This scenario has the following two variants, depending on how an end user connects to the database:
  • Scenario 2a — Through an application: An end user from the application's own user store accesses the database through the application. The application supplies the EndUserSecurityContext payload through client drivers, identifying the end user through a user name and a security context lookup key, rather than an IAM token. Additionally, it provides a database-access token.
  • Scenario 2b — Direct logon (password authentication): The local end user connects directly to the database using password authentication.

Scenario 2a — Through an application

This flow triggers when your application sends the EndUserSecurityContext payload using a client driver. The payload contains an end-user name, a security context lookup key, and a database-access token. Behind the scenes, the database server performs the following steps:

  1. Verifies the local application user: The server checks if a local end user matching the user name in the EndUserSecurityContext payload exists in the database. If no match is found, the server raises an error.
  2. Searches for an existing security context: If the end user is present, the server looks for an existing end-user security context that matches the provided security context lookup key.
  3. Creates a new security context (if necessary): If no matching security context exists, the server creates a new one using the local end user's identity and their explicitly granted data roles.
  4. Attaches the security context: Finally, the server attaches the security context (along with any provided end-user context attributes) to the active session.
  5. Handles security context attach, detach, and cleanup: The database automatically manages the detachment and reattachment of an end-user security context to a database session through the following mechanisms:
    • Connection release: When your application code releases the connection, the security context is detached from the database session.
    • Security context replacement: When your application reuses a connection, the database seamlessly replaces the previous security context with the new one. If the incoming request does not use an end-user security context, the server simply detaches the old one.
    • Garbage collection: The database automatically garbage-collects any end-user security contexts that remain inactive (not attached to any database session) after a timeout duration of one hour.

Scenario 2b — Direct logon (password authentication)

If your end users created in the database log in directly using a SQL client, the database server performs the following tasks:

  1. Validates identity: The server verifies the end user using credential-based password authentication.
  2. Identifies data roles: The server identifies the end user's assigned data roles.
  3. Retrieves database roles: For each identified data role, the server retrieves all standard database roles granted to it.
  4. Evaluates privileges and establishes security context: If any of those database roles contain the CREATE SESSION privilege, the server permits the direct logon and establishes the end-user security context with those data roles active.

    With direct logon, the end-user security context is destroyed when the database session closes.