Read End-User Context Attributes

15.4 Read End-User Context Attributes

Use the ORA_END_USER_CONTEXT function to retrieve end-user context attributes from the current end-user security context.

This function returns a JSON object containing all end-user context attributes for the current security context. You can use dot notation to navigate the JSON hierarchy. Depending on the provided field parameters, the function can return a single attribute value, all attributes within a specific end-user context, or all attributes across all instantiated end-user contexts.

In addition to the ORA_END_USER_CONTEXT function, the SYS.END_USER_CONTEXT view exposes all instantiated end-user contexts and their attributes in a table format, with each row representing one context's content as a JSON document. This view is the primary interface for querying or updating end-user context attributes using standard DML syntax.

Required privilege

The SELECT ANY END USER CONTEXT system privilege to read attributes from any end-user context.
The SELECT privilege on the SYS.END_USER_CONTEXT view for a specific end-user context, granted through a data grant. For example:
```
CREATE DATA GRANT [schema.]grant_name AS
  SELECT
  ON SYS.END_USER_CONTEXT
  WHERE owner = 'HR' AND name = 'HCM_CONTEXT'
  TO <end user or data role>;
```
Note:
To create data grants on the SYS.END_USER_CONTEXT view, the data grant creator must have the ADMINISTER ANY DATA GRANT system privilege at the system level. In addition, they must have all the other required privileges listed in Create Data Grants.

Syntax

ORA_END_USER_CONTEXT[.schema[.name[.attribute]]]

Parameters

Parameter	Description
`schema`	The owner of the end-user context. Omit for `USER.DEFAULT` system attributes (which are elevated to the top level).
`name`	The name of the end-user context. If omitted, all end-user contexts under the schema are returned.
`attribute`	The specific attribute within the end-user context. If omitted, the entire end-user context is returned as a JSON object.

Usage notes and restrictions

The ORA_END_USER_CONTEXT function does not take function arguments. The dot-path notation is possible because the function returns a JSON type, and the JSON field navigation syntax applies.
If you call SELECT ORA_END_USER_CONTEXT without a dot-path, it returns a complete JSON document containing all predefined system attributes and all instantiated custom contexts with their attributes.
Attributes of the USER.DEFAULT context are elevated to the top level of the JSON return value. To access them, use ORA_END_USER_CONTEXT.username rather than ORA_END_USER_CONTEXT.USER.DEFAULT.username.
Attributes of the USER.TOKEN context require the full prefix: ORA_END_USER_CONTEXT.USER.TOKEN.iss.
Custom context attributes require both schema and name in the path: ORA_END_USER_CONTEXT.hr.hcm_context.emp_id.
You must have defined an end-user context (using the CREATE END USER CONTEXT command) before its attributes can be referenced. Instantiation occurs automatically on first reference.
The SYS.END_USER_CONTEXT view provides an alternative table-based interface. You can use the standard SELECT with a WHERE predicate on the OWNER and NAME columns to filter to a specific context. The CONTEXT column contains the JSON payload for that end-user context.

For syntax diagrams and additional details, see ORA_END_USER_CONTEXT in Oracle AI Database SQL Language Reference.

Example 15-5 Return all available context attributes for the current session

SELECT ORA_END_USER_CONTEXT FROM dual;

If hr.hcm_context has been instantiated with emp_id = 3 and org_id = 5, the above statement returns a JSON document similar to the following example:

{
    "USERNAME": "SCOTT",
    "LOGON_END_USER": "SCOTT",
    "CURRENT_END_USER": "SCOTT",
    "USER": {
        "TOKEN": {
            "TOKEN_ID": "...",
            ...
        }
    },
    "HR": {
        "HCM_CONTEXT": {
            "emp_id": 3,
            "org_id": 5
        }
    }
}

Example 15-6 Return all attributes for a specific context

Return all attributes for hr.hcm_context.

SELECT ORA_END_USER_CONTEXT.hr.hcm_context FROM dual;

Output:

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

Example 15-7 Return all contexts under a schema

Return all contexts under the hr schema.

SELECT ORA_END_USER_CONTEXT.hr FROM dual;

Example 15-8 Return a specific attribute value

Return the emp_id attribute from hr.hcm_context.

SELECT ORA_END_USER_CONTEXT.hr.hcm_context.emp_id FROM dual;

Example 15-9 Access system-level attributes

SELECT ORA_END_USER_CONTEXT.username FROM dual;
SELECT ORA_END_USER_CONTEXT.DB_NAME FROM dual;
SELECT ORA_END_USER_CONTEXT.USER.TOKEN.iss FROM dual;

Example 15-10 Query a specific attribute using the SYS.END_USER_CONTEXT view

Use the SYS.END_USER_CONTEXT view to query a specific attribute using the standard table syntax.

SELECT t.CONTEXT.org_id
FROM   SYS.END_USER_CONTEXT t
WHERE  owner = 'HR' AND name = 'HCM_CONTEXT';

This is equivalent to the following command:

SELECT ORA_END_USER_CONTEXT.hr.hcm_context.org_id FROM dual;

Example 15-11 Use a context attribute in a query predicate

Use a subquery against the SYS.END_USER_CONTEXT view in a predicate.


SELECT * FROM hr.employees
WHERE  emp_id = (
    SELECT t.CONTEXT.emp_id
    FROM   SYS.END_USER_CONTEXT t
    WHERE  owner = 'HR' AND name = 'HCM_CONTEXT'
);

Example 15-12 Use a context attribute in a data grant predicate

CREATE DATA GRANT hcm_dg AS
  SELECT ON hr.employees
  WHERE emp_id = ORA_END_USER_CONTEXT.hr.hcm_context.emp_id
  TO john;