Mortgage Lending: Deep Data Security

The Mortgage Lending use case shows how the AI Optimizer combines application data with lending-policy documents. Oracle Deep Data Security (DDS) adds a practical constraint: the assistant can use only the data available to the person asking.

In this extension, LEEDS_REVIEWER can review applications in Leeds and use their LTV and LTI measures, but cannot see detailed income or expenditure values. Create that policy in the AI Optimizer, then compare direct SQL and NL2SQL results. Both respect the same policy because the database enforces it.

What you'll build

Object	Name (suggested)	Purpose
Data role	MORTGAGE_REVIEWER_ROLE	The principal the policy authorizes.
Data grant	DG_LEEDS_APPLICATION_SUMMARY	On MORTGAGE_APPLICATION_SUMMARY: SELECT on all columns except detailed income and expenditure, filtered to Leeds applications.
End user	LEEDS_REVIEWER	A reviewer who can assess local application risk measures without access to detailed financial inputs.

The end user can see only rows where property_location = 'Leeds'. For those rows, TOTAL_VERIFIED_ANNUAL_INCOME, MONTHLY_COMMITTED_EXPENDITURE, and MONTHLY_HOUSEHOLD_EXPENDITURE are masked. The schema owner still sees all rows and columns, so test as the end user.

Prerequisites

1. Oracle AI Database 26ai. DDS is available only in Oracle AI Database 26ai. If the connected database does not support it, the Deep Data Security tab shows a not available notice.

2. The mortgage schema is loaded. Complete Setup so MORTGAGE_APPLICATION_SUMMARY exists in the AI Optimizer's configured database user (referred to below as <SCHEMA>).

3. DDS privileges on <SCHEMA>. As a DBA, grant the configured user the Deep Data Security privileges below. Replace <DATABASE_USER> with <SCHEMA>.

   To use all Deep Data Security management actions, grant the following privileges to the configured database user. Replace <DATABASE_USER> with that username. The privileges are optional: unsupported databases show an availability warning, and a missing privilege disables only its related action.

   GRANT CREATE DATA ROLE TO "<DATABASE_USER>";
   GRANT DROP DATA ROLE TO "<DATABASE_USER>";
   GRANT CREATE END USER TO "<DATABASE_USER>";
   GRANT DROP END USER TO "<DATABASE_USER>";
   GRANT CREATE DATA GRANT TO "<DATABASE_USER>";
   GRANT CREATE END USER CONTEXT TO "<DATABASE_USER>";
   GRANT CREATE END USER SECURITY CONTEXT TO "<DATABASE_USER>";
   GRANT ADMINISTER ANY DATA GRANT TO "<DATABASE_USER>";
   GRANT GRANT ANY DATA ROLE TO "<DATABASE_USER>";
   -- Read access so the tool can list data roles, role grants, and end users
   GRANT SELECT ON DBA_DATA_ROLES TO "<DATABASE_USER>";
   GRANT SELECT ON DBA_DATA_ROLE_GRANTS TO "<DATABASE_USER>";
   GRANT SELECT ON DBA_END_USERS TO "<DATABASE_USER>";

   To let Vector Search and NL2SQL connect as a Deep Data Security end user, the end user must be able to log in. CREATE SESSION cannot be granted directly to an end user; a standard database role carries it through a data role. As a privileged user, create that role once and let <DATABASE_USER> grant it:

   CREATE ROLE AIO_DDS_ROLE;
   GRANT CREATE SESSION TO AIO_DDS_ROLE;
   GRANT AIO_DDS_ROLE TO "<DATABASE_USER>" WITH ADMIN OPTION;

   The tool grants AIO_DDS_ROLE to each locally managed data role it creates, so an end user assigned that data role can be connected as.

Step 1: Confirm DDS is available

1. In the AI Optimizer, open Tools > Deep Data Security.
2. The page shows Data Roles, End Users, and Data Grants.
   • If it instead says DDS is not available on the selected database, switch Configuration > Databases to an Oracle AI Database 26ai connection.
   • If an action is disabled, the page identifies the missing privilege. Grant that privilege, then reload the page.

Step 2: Create the data role

1. In Data Roles, select Create Data Role.
2. Set Data role name to MORTGAGE_REVIEWER_ROLE and leave Mapped to blank.
3. Select Create.

The role is now available to assign to an end user.

Step 3: Create the data grant

In Data Grants, create the policy that defines the reviewer’s access:

1. Set Data grant name to DG_LEEDS_APPLICATION_SUMMARY.

2. Set Object (table/view) to MORTGAGE_APPLICATION_SUMMARY.

3. Select the SELECT privilege.

4. For Columns, choose All columns except, then select:

   • TOTAL_VERIFIED_ANNUAL_INCOME
   • MONTHLY_COMMITTED_EXPENDITURE
   • MONTHLY_HOUSEHOLD_EXPENDITURE

5. Set Row predicate to:

   property_location = 'Leeds'

6. Set Grant to data role to MORTGAGE_REVIEWER_ROLE.

7. Review the generated statement. It should be close to:

   CREATE DATA GRANT DG_LEEDS_APPLICATION_SUMMARY
     AS SELECT (
       ALL COLUMNS EXCEPT
         "TOTAL_VERIFIED_ANNUAL_INCOME",
         "MONTHLY_COMMITTED_EXPENDITURE",
         "MONTHLY_HOUSEHOLD_EXPENDITURE"
     ) ON MORTGAGE_APPLICATION_SUMMARY
     WHERE property_location = 'Leeds'
     TO MORTGAGE_REVIEWER_ROLE;

8. Select Create Data Grant.

This policy leaves LTV, LTI, employment type, requested loan amount, and location available. The reviewer can assess a case against the lending criteria without receiving the detailed financial inputs used to calculate those measures.

Step 4: Create the end user

1. In End Users, select Create End User.
2. Set End user name to LEEDS_REVIEWER.
3. Leave Schema at the default <SCHEMA> so unqualified object names resolve in the mortgage schema.
4. Under Assigned data roles, select MORTGAGE_REVIEWER_ROLE.
5. Select Create.

The end user is provisioned with the configured database user's password. Use that password only for the direct-SQL comparison in the next step.

Step 5: Test the policy

The schema owner is not subject to its own data grants, so compare the owner with LEEDS_REVIEWER.

Compare direct SQL

As <SCHEMA>, query the full summary view:

SELECT mortgagor_label,
       property_location,
       total_verified_annual_income,
       monthly_committed_expenditure,
       monthly_household_expenditure,
       loan_to_value_pct,
       loan_to_income_multiple
FROM mortgage_application_summary
ORDER BY mortgagor_label;

Then connect as LEEDS_REVIEWER and query the same owner view:

SELECT mortgagor_label,
       property_location,
       total_verified_annual_income,
       monthly_committed_expenditure,
       monthly_household_expenditure,
       loan_to_value_pct,
       loan_to_income_multiple
FROM <SCHEMA>.mortgage_application_summary
ORDER BY mortgagor_label;

What to look for: the reviewer sees only the Leeds applications. The three detailed financial columns return NULL; the LTV and LTI measures remain visible. The owner sees the whole portfolio and the unmasked values.

Ask through Chatbot

The prompt does not change; only the database identity changes.

1. Keep <SCHEMA> as the active database and select NL2SQL under Tool Selection.

2. In Tools > Deep Data Security, set Connect tools as to LEEDS_REVIEWER.

3. Return to Chatbot and enable Deep Data Security in the sidebar.

4. Ask:

   List the mortgage applications, their location, LTV, LTI, and annual income.
   
   Which applications require enhanced review based on LTV or LTI?
   
   What is the average annual salary of mortgagors in Leeds?

What to look for: the agent sees Leeds applications only. It can use LTV and LTI, but annual-income values are unavailable and it cannot answer the average-salary question from the masked data. This is the same policy you saw in direct SQL, enforced on the agent’s generated query.

Turn Deep Data Security off and ask the questions again. The agent now uses the owner connection and can access the full summary view.

More policies to try

Goal	Object	Columns	Row predicate
Let a Bristol reviewer see only Bristol cases	MORTGAGE_APPLICATION_SUMMARY	All columns except detailed income and expenditure	property_location = 'Bristol'
Let a policy analyst see portfolio measures only	MORTGAGE_APPLICATION_SUMMARY	PROPERTY_LOCATION, EMPLOYMENT_TYPE, LOAN_TO_VALUE_PCT, LOAN_TO_INCOME_MULTIPLE	(none)

Re-run the direct-SQL and Chatbot checks after each change to see the effect of the policy.

Troubleshooting

• Deep Data Security shows not available: the active database is not Oracle AI Database 26ai. Switch connections in Configuration > Databases.
• Controls are disabled: the configured user is missing a DDS privilege. The disabled control identifies the needed grant.
• LEEDS_REVIEWER sees all rows or unmasked columns: confirm that DG_LEEDS_APPLICATION_SUMMARY was created for MORTGAGE_REVIEWER_ROLE, and that the role is assigned to LEEDS_REVIEWER.
• LEEDS_REVIEWER sees no rows: DDS is deny-by-default. Confirm the grant exists and that the row predicate matches the seeded value Leeds.