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.

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

• Scenario 2: Locally Managed End User

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 application supplies the database-access token and, optionally, an end-user 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 a database-access token and, optionally, an IAM-issued end-user token.

• Two-token flow

  Behind the scenes, when both tokens are supplied, 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 the configurable OAuth 2.0 provider) 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 reads the user name from the upn claim for Entra ID, the sub claim for OCI IAM, or the claim specified by username_claim for the configurable OAuth 2.0 provider.

  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, a configured group claim for OCI IAM, or the top-level array specified by roles_claim for the configurable OAuth 2.0 provider) 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 identified by the payload’s database-access token. 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.

     When the connection and the two-token payload identify the same application, data roles mapped from the application’s original logon token are not added to the attached security context.

     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.

• Single-token flow

  When the payload contains only the database-access token, the database validates that token and uses it for authentication and authorization. The single-token variants are listed in About the End-User Security Context.

  • For a client-credentials database-access token, the security context represents the application or service identified by the token.
  • For an OBO database-access token, the security context represents the end user and retains the application identity information carried in the token.
  • Data roles that are externally mapped are enabled from the corresponding IAM role claims in the database-access token. The USER.TOKEN context is also populated from that token. See Default (System-Defined) Contexts.
  • Data roles granted directly to the application identity identified by the database-access token are resolved according to the rules in step 4.

  The application can attach the payload to a database connection established using a password-authenticated connection pool user account or an application identity. The attachment, detachment, and cleanup behavior described in step 6 applies.

• An application-identity connection carrying another application’s token

  Application A (AppA) can establish the database connection and attach a single-token payload containing Application C’s (AppC) client-credentials database-access token.

  The database validates AppC’s database-access token and establishes the security context using AppC’s identity and roles. Data roles mapped to AppC’s application roles in IAM and those granted directly to AppC’s registered application identity are enabled. Data roles granted directly to AppA are not enabled in AppC’s security context. Register AppC’s application identity in the database before granting data roles directly to it; see Create Application Identity.

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 that identifies the application and includes the database as its audience. The server checks the token’s signature and expiration and validates the aud claim. For the configurable OAuth 2.0 provider, the application’s client identifier comes from the claim specified by application_id_claim and can differ from sub.

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.

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), the sub claim (OCI IAM), or the claim specified by username_claim for the configurable OAuth 2.0 provider. For an application-only session (Scenario 1c, or a security context payload with only client-credentials database-access token in Scenario 1a), the active principal is the application, available as ORA_END_USER_CONTEXT.username.

• 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.

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.