Load and Use Custom End-User Context Attributes for Analytics

18.4 Load and Use Custom End-User Context Attributes for Analytics

Custom end-user context attributes allow you to store frequently evaluated business values, such as emp_id, org_id, analyst_id, or tenant_id. By loading these attributes into the end-user context once per session, you can eliminate the need for repetitive database lookups during analytical queries and data grant evaluations, thereby improving performance.

For complete information about configuring and using custom end-user contexts, see Configure End-User Contexts and Attributes.

Example: Configure and use a custom context

This example builds on the SALES_APP.SALES table and SALES_ANALYST_ROLE used earlier in this chapter. It demonstrates how to map an end user to a tenant (tenant 10), load the tenant_id attribute into the custom context on first read, and apply that value to a data grant.

Note:

Ensure you have configured the privileges required to read and update the custom context. See Read End-User Context Attributes and Modify Custom End-User Context Attributes.
In your actual application, replace SALES_ANALYST with the exact value returned by ORA_END_USER_CONTEXT.username.
After you create the load-handler package in step 2 below, grant EXECUTE on SALES_APP.SALES_SECURITY to the applicable application identity or database role.

Create the tenant mapping
First, establish an application source table that maps the current end user to their designated tenant, and insert a sample record.
```
CREATE TABLE sales_app.analyst_tenants (
  end_user_name VARCHAR2(128) PRIMARY KEY,
  tenant_id     NUMBER NOT NULL
);

INSERT INTO sales_app.analyst_tenants (end_user_name, tenant_id)
VALUES ('SALES_ANALYST', 10);

COMMIT;
```

Create the load-handler package

Create a PL/SQL package that looks up the tenant for the current user and dynamically updates the end-user context instance. In this example, the handler sets the tenant_id to 10.

CREATE OR REPLACE PACKAGE sales_app.sales_security AUTHID DEFINER AS
  PROCEDURE init_user_context;
END sales_security;
/

CREATE OR REPLACE PACKAGE BODY sales_app.sales_security AS
  PROCEDURE init_user_context IS
    l_tenant_id sales_app.analyst_tenants.tenant_id%TYPE;
    l_sql_stmt  VARCHAR2(1000);
  BEGIN
    -- Look up the tenant for the current user
    SELECT tenant_id
      INTO l_tenant_id
      FROM sales_app.analyst_tenants
     WHERE end_user_name = ORA_END_USER_CONTEXT.username;

    -- Set the value in the end-user context instance
    l_sql_stmt :=
      'UPDATE SYS.END_USER_CONTEXT t ' ||
      'SET t.CONTEXT.tenant_id = ' ||
      TO_CHAR(l_tenant_id, 'TM9') ||
      ' WHERE owner = ''SALES_APP'' ' ||
      'AND name = ''SALES_CONTEXT''';

    EXECUTE IMMEDIATE l_sql_stmt;
  END init_user_context;
END sales_security;
/

Create the custom context
Define the context using a JSON Schema. By defining the o:onFirstRead property, the database automatically invokes the init_user_context handler the first time a statement reads the tenant_id attribute in the current session.
```
CREATE OR REPLACE END USER CONTEXT sales_app.sales_context
  USING JSON SCHEMA
'{
  "type": "object",
  "properties": {
    "tenant_id": {
      "type": "integer",
      "o:onFirstRead": "sales_app.sales_security.init_user_context"
    }
  }
}';
```
Once loaded, the value becomes available for the session through the path: ORA_END_USER_CONTEXT.sales_app.sales_context.tenant_id.
Use the attribute in the data grant
Update your previous data grant (created in Determine the Data Available to Analytical Queries) to replace a fixed tenant predicate with the dynamic context attribute.
```
CREATE OR REPLACE DATA GRANT sales_app.tenant_sales_analytics AS
  SELECT (ALL COLUMNS EXCEPT secret_note)
  ON sales_app.sales
  WHERE tenant_id = ORA_END_USER_CONTEXT.sales_app.sales_context.tenant_id
  TO sales_analyst_role;
```
For this mapping, the data grant returns the six rows associated with tenant 10 and masks secret_note as NULL.
Run the analytical query
You can now run analytical queries securely without having to explicitly join the tenant mapping table. The data grant automatically enforces the filter.
```
SELECT category,
       COUNT(*) AS sales_rows,
       COUNT(amount) AS amount_values,
       SUM(NVL(amount, 0)) AS total_amount
FROM sales_app.sales
GROUP BY ALL
ORDER BY category;
```
The query returns four category A rows (totaling 70) and two category B rows (totaling 20). Because the handler performs the initial tenant lookup and caches the retrieved tenant ID in the context, the analytical query does not incur the overhead of repeating the lookup.

Applications can also reference this attribute path in standard query predicates and DML statements when they require the tenant value. The data grants continue to enforce access to the target objects.