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

When a client driver sends an EndUserSecurityContext payload during a SQL operation, or when a principal logs on directly, 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.

15.4.1 Scenario 1: Token-Based (IAM-Managed) Principal

If your end users or applications 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 three variants, depending on how an end user or application 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 as the end user: 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 1c — Direct logon as the application identity: An application or AI agent connects directly as its own application identity, and the security context's identity is the application identity itself.

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 requested additional data 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 performs 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 exactly 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 as the end user

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:

About OCI IAM auth token sessions

For OCI IAM, a session can be established with an OCI IAM auth token, scoped to the end user and supplied by the end user's client, instead of OAuth 2.0 tokens. The database validates the token at runtime by calling OCI IAM (the identity data plane, IDDP), not by checking the aud claim as it does for an OAuth 2.0 token. It then resolves data roles from the end user's OCI IAM groups mapped with IAM_GROUP_NAME. If no mapped global database user or application identity of the same name as the connecting user exists, and a resolved data role carries the CREATE SESSION privilege through a granted database role, the database permits the session creation as a direct logon of the end user and establishes the end-user security context. The security context can then enforce data grants without requiring a mapped database schema for the end user.

An OCI IAM auth token is a scoped proof-of-possession token issued by OCI IAM for database authentication. See Key Terminology.

Scenario 1c — Direct logon as the application identity

If an application or AI agent connects directly as its own application identity, the database establishes an end-user security context whose identity is the application identity itself. The server performs the following steps:

  1. Validates the database-access token: The application authenticates to IAM with its client credentials and presents a database-access token whose subject (sub claim) is the application's client ID and whose audience (aud claim) is the database. The server checks the token's signature and expiration and validates the aud claim.
  2. Resolves the application identity: The server looks for a global schema mapped to the token subject. If no mapped schema exists, the server looks for a database application identity mapped to the token's application client ID, and creates a schema-less lightweight session for the application identity.
  3. Establishes the security context: The server establishes an end-user security context whose identity is the application identity. It activates the data roles granted directly to the application identity, together with the data roles mapped to the application's IAM roles. SQL executed on the connection is enforced according to those data roles, so the application retrieves only the data its application identity is authorized to access. The application identity must hold the CREATE SESSION and CREATE END USER SECURITY CONTEXT privileges, granted through a data role. When the connection closes, the database destroys the application identity's lightweight session and its security context.

    If the application later attaches an end user's security context payload to this session, the database replaces the application identity's security context with the end user's security context and manages this security context as described in Scenario 1a. When the end user's security context is detached, the server reattaches the original application identity security context. For each attached end user, the database enables the data roles granted directly to the application identity, but not the data roles mapped from the application identity's own direct-logon token.

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 other USERENV attributes remain unchanged.
  • End-user identity: For an end-user session, ORA_END_USER_CONTEXT.username reflects the upn claim (Entra ID) or the sub claim (OCI IAM). For an application-identity session (Scenario 1c, or an application-identity connection in Scenario 1a), the active principal is the application identity, available as ORA_END_USER_CONTEXT.name.
  • View active roles: Query the V$END_USER_DATA_ROLE view to list the data roles currently active.
  • 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.

15.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 and a second factor, if configured.

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, an optional security context lookup key, a database-access token, and any valid application-supplied data roles. 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. Completes multifactor authentication (MFA): If the end user has a configured second factor, the server sends a push notification to the configured external authenticator. Authentication continues only after the end user approves the push notification.
  3. Identifies data roles: The server identifies the end user's assigned data roles.
  4. Retrieves database roles: For each identified data role, the server retrieves all standard database roles granted to it.
  5. 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.