ORA_END_USER_CONTEXT

Purpose

Use ORA_END_USER_CONTEXT operator to refer to a context attribute as a variable (or expression). You can retrieve the following:

• A specific attribute within a specified context.

• All the attributes and values for one specified context. The returned data is in JSON format.

• All the attributes and values for all the contexts that are accessible to the user. The returned data is in JSON format.

Prerequisites

Calling ORA_END_USER_CONTEXT operator to access the pre-defined, system-managed contexts do not require any specific privilege. To access custom (user-defined) contexts, you must have either SELECT ANY END USER CONTEXT system privilege, or, have the SELECT privilege granted through a data grant on SYS.END_USER_CONTEXT view for the specific context.

Syntax

Description of the illustration ora_end_user_context.gif

Semantics

• schema: The owner of the context. Omit for USER.DEFAULT system attributes (which are elevated to the top level).

• name: The name of the context. If omitted, all the contexts under the schema are returned.

• attribute: The specific attribute within the context. If omitted, the entire context is returned as a JSON object.

• system_attribute: Includes the following: USERNAME, LOGON_END_USER, CURRENT_END_USER, and all the predefined attributes in USERENV namespace from SYS_CONTEXT.

Note that ORA_END_USER_CONTEXT does not take any arguments. It returns a complete list of pre-defined system attributes along with all instantiated contexts and their associated attributes.

The dot-annotation is supported because this function returns data in JSON type.

If HR.HCM context has been created and instantiated in the session, SELECT ORA_END_USER_CONTEXT from dual; returns the following:

{
  "USERNAME" : "SCOTT",
  "LOGON_END_USER": "SCOTT",
  "CURRENT_END_USER": "SCOTT",
  "USER" : {
      "TOKEN" : {
          "TOKEN_ID" : xxx
          ...
      }
   },
  "HR" : {
    "HCM" : {
      "emp_id": 3
      "service_center_id": 5
     }
  }
}

Note that the top-level attributes as shown in above example, USERNAME, LOGON_END_USER, CURRENT_END_USER, and the context USER.TOKEN, are the pre-defined system attributes. They are by default available to all end users.

There are two pre-defined, system-managed contexts available in every end-user security context and do not requre explicit instantiation. You cannot modify attributes in these contexts directly, they are initialized by the database server based on the user identity propagated from IAM.

• USER.DEFAULT: Contains system attributes that identify the end user, such as USERNAME, LOGON_END_USER, and CURRENT_END_USER. It also includes all predefined database session attributes from the SYS_CONTEXT namespace USERENV, such as AUTHENTICATED_IDENTITY and DB_NAME. For ease of use, USER.DEFAULT attributes are elevated to the top level of the ORA_END_USER_CONTEXT namespace, so you can reference them directly without specifying USER.DEFAULT in the path. For example, ORA_END_USER_CONTEXT.username returns the end user’s name.

• USER.TOKEN: Contains claims extracted directly from the end user’s OAuth 2.0 access token. Only the following claims are available: iss [issuer], sub [subject], and aud [audience]. You must prefix these attributes with the full path in the operator, for example, ORA_END_USER_CONTEXT.USER.TOKEN.iss.

You can reference the attributes of context USER.DEFAULT directly using ORA_END_USER_CONTEXT .

The syntax follows the standard JSON dot path notation, so, SELECT ORA_END_USER_CONTEXT.HR.HCM FROM dual returns:

{
  "emp_id": 3,
  "service_center_id": 5
}

SELECT ORA_END_USER_CONTEXT.HR FROM dual returns:

{
      "HCM" :    {
      "emp_id": 3
      "service_center_id": 5
   }
}

To reference a particular attribute, owner, name, and attribute all need to exist. For example, SELECT ORA_END_USER_CONTEX.HR.HCM.emp_id FROM dual returns 3.