Modify Custom End-User Context Attributes

15.5 Modify Custom End-User Context Attributes

After a custom context is defined and instantiated in an end-user security context, you can update individual attribute values using a standard UPDATE statement on the SYS.END_USER_CONTEXT view. This allows application code or PL/SQL handlers to set dynamic attribute values during a session.

Required privilege

The UPDATE ANY END USER CONTEXT system privilege, or
The UPDATE privilege on the SYS.END_USER_CONTEXT view for the target end-user context, granted through a data grant. For example:
```
CREATE DATA GRANT [schema.]grant_name AS
  UPDATE
  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

UPDATE SYS.END_USER_CONTEXT t
SET    t.CONTEXT.attribute = value
WHERE  OWNER = owner AND NAME = name;

Parameters

Parameter	Description
`owner`	The schema name (owner) of the custom context to update.
`name`	The name of the custom context to update.
`attribute`	The attribute within the custom context whose value is being changed.
`value`	The new value to assign to the attribute. Must conform to the attribute's declared data type.

Usage notes and restrictions

You can enforce fine-grained access to specific end-user contexts through data grants. The following example grants hcm_admin_role the ability to instantiate and update hr.hcm_context.
```
CREATE DATA GRANT update_hcm_attr AS
  SELECT, UPDATE
  ON SYS.END_USER_CONTEXT
  WHERE owner = 'HR' AND name = 'HCM_CONTEXT'
  TO hcm_admin_role;
```
There is no physical table behind SYS.END_USER_CONTEXT. The UPDATE statement is rewritten internally to operate on the in-memory end-user context cache of the current security context.
End-user context instances are session-specific. An update made in one connection is not visible to another connection that shares the same end-user security context ID.
If two parallel connections share the same security context ID and both make updates, the end-user context attribute values stored at detachment follow the last-write-wins rule: the end-user context belonging to the connection that detaches last persists.
For data grants on SYS.END_USER_CONTEXT, only row-level SELECT and UPDATE privileges are supported. Other privileges and column-level privileges are not permitted. The SELECT privilege allows instantiation of an end-user context at runtime; UPDATE allows attribute modification.
Authorization for PL/SQL handler functions follows the standard invoker or definer rights model. When the application identity instantiates the end-user context, it becomes the invoker of any o:onFirstRead handler. The handler must have UPDATE access on the context (through a data grant or UPDATE ANY END USER CONTEXT system privilege) to successfully write attribute values.
The updated attribute value must match the attribute type defined in the end-user context schema. If the value does not match the defined type, an error is raised.
Bind variables are not supported. You cannot use bind variables from either PL/SQL or a JDBC client.

For syntax diagrams and additional details, see MODIFY END USER CONTEXT in Oracle AI Database SQL Language Reference.

Example 15-13 Set an end-user context attribute for the current security context

Set the emp_id attribute in hr.hcm_context to 3 for the current end-user security context.

UPDATE SYS.END_USER_CONTEXT t
SET    t.CONTEXT.emp_id = 3
WHERE  owner = 'HR' AND name = 'HCM_CONTEXT';

Example 15-14 Implement a PL/SQL callback to populate an attribute value at runtime

If a PL/SQL callback is specified, create and implement the packages and procedures as needed to populate the attribute value at runtime.

CREATE OR REPLACE PACKAGE BODY hr.hcm_core AS
  PROCEDURE init_user_context IS
   sql_stmt VARCHAR2(1000);
  BEGIN
   -- application logic...
   sql_stmt := 'UPDATE END_USER_CONTEXT t SET t.CONTEXT.emp_id = 100 WHERE owner = ''HR'' and name = ''HCM_CONTEXT''';
   EXECUTE IMMEDIATE sql_stmt;
  END;
END;
/