Modify Custom End-User Context Attributes

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

Note:

Updating a context attribute implicitly instantiates the custom context if it has not already been instantiated. This implicit instantiation requires the SELECT privilege on the context, in addition to the UPDATE privilege required to modify the attribute. Therefore, when updating an end-user context for the first time, you must have both SELECT and UPDATE privileges.

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’s-rights or definer’s-rights model. For an invoker’s-rights handler, the invoker must have UPDATE privilege on the end-user context, either through a data grant or through the UPDATE ANY END USER CONTEXT system privilege. When the application identity instantiates the end-user context, it becomes the invoker of any o:onFirstRead handler, which runs with invoker’s rights.
For a definer’s-rights handler, the owner schema of the handler function must have the UPDATE ANY END USER CONTEXT system privilege. You cannot use data grants for this purpose because the handler function is owned by a standard database user, and you cannot grant data grants to standard database users.
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 16-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 16-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;
/