Blog

Oracle Database 26ai Deep Data Security

Written by Anas Darkal | May 14, 2026 3:12:31 PM

As AI agents move from “chatting” to “acting,” the security stakes have never been higher. Traditional application-level security isn’t enough when an AI agent can dynamically generate SQL or bypass fixed UI flows.

Introducing Oracle Deep Data Security — a flagship feature of Oracle AI Database 26ai designed to provide bulletproof, database-native authorization for the AI era.

 

How it works

Oracle Deep Data Security moves the guardrails into the data layer itself:

  1. End User Identity: Oracle Database end users are a new class of identity — distinct from traditional database schema users. They authenticate directly to the database via external IAM (such as OCI IAM or Azure AD) or with a password. Unlike schema users, they do not own tables or other objects. Instead, they access data through data grants.

  2. Create Data Role: A data role is a named policy holder. You attach data grants to it, then grant the role to end users.

  3. Create Data Grant: A declarative access policies that define exactly which rows and columns each user can read or modify, based on their identity. A data grant defines two things: which columns an end user can SELECT or UPDATE, and which rows, through a predicate (a SQL WHERE condition), are evaluated at query time.

  4. End User Authentication: When an end user authenticates, Oracle Database automatically activates their DATA ROLE, which triggers DATA GRANT enforcement on every query. The database enforces the boundary — neither the application nor the agent.

  5. Dynamic Security Context: The predicate can use ORA_END_USER_CONTEXT.username, a built-in function that resolves to the authenticated end user's identity. The same grant works for every employee — no hardcoded names, no per-user logic.

  6. Transparent Enforcement: At runtime, Oracle Database rewrites every query to add the data grant condition — the result is automatically limited to the rows and columns the user is authorized to see, regardless of how the query was written.

This blog is a walkthrough of Oracle Database Deep Data Security.

 

Prerequisites

An Oracle AI Database 26ai (Autonomous or on-premises)

 

Tasks

Task 1: Create HR Schema and Employee Data

CREATE USER hr NO AUTHENTICATION default tablespace users;
ALTER USER hr QUOTA UNLIMITED ON users

CREATE TABLE hr.employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
job_code VARCHAR2(10),
department_id NUMBER,
ssn VARCHAR2(20),
photo BLOB,
phone_number VARCHAR2(30),
salary NUMBER(10,2),
user_name VARCHAR2(128),
manager_id NUMBER);

-- CEO
INSERT INTO hr.employees VALUES (1, 'Cristiano', 'Fernaldo', 'CEO', NULL, '111-11-1111', NULL, '555-100-0001', 235000, 'cristiano', NULL);

-- Manager
INSERT INTO hr.employees VALUES (2, 'Scott', 'Tiger', 'SWE_MGR', 1, '222-22-2222', NULL, '555-100-0002', 175000, 'scott', 1);

-- Software engineering team
INSERT INTO hr.employees VALUES (3, 'Chris', 'Revans', 'SWE2', 1, '333-33-3333', NULL, '555-100-0003', 120000, 'chris', 2);
INSERT INTO hr.employees VALUES (4, 'Tommy', 'Leed', 'SWE1', 1, '444-44-4444', NULL, '555-100-0004', 95000, 'tommy', 2);
INSERT INTO hr.employees VALUES (5, 'Tony', 'Kroosny', 'SWE3', 1, '555-55-5555', NULL, '555-100-0005', 130000, 'tony', 2);

-- Other departments
INSERT INTO hr.employees VALUES (6, 'Hany', 'Smith', 'SALES_REP', 2, '666-66-6666', NULL, '555-100-0006', 145000, 'hany', 1);
INSERT INTO hr.employees VALUES (7, 'Jeny', 'Chen', 'HR_REP', 3, '777-77-7777', NULL, '555-100-0007', 92000, 'jeny', 1);

-- Build a Manager lookup table

CREATE TABLE hr.managers (
manager_id NUMBER,
employee_id NUMBER,
mgr_user_name VARCHAR2(128),
mgr_first_name VARCHAR2(50),
mgr_last_name VARCHAR2(50));

-- Add the managers
INSERT INTO hr.managers (manager_id, employee_id, mgr_user_name, mgr_first_name, mgr_last_name)
SELECT
e.manager_id,
e.employee_id,
m.user_name AS mgr_user_name,
m.first_name AS mgr_first_name,
m.last_name AS mgr_last_name
FROM hr.employees e
JOIN hr.employees m
ON e.manager_id = m.employee_id
WHERE e.manager_id IS NOT NULL;

COMMIT; 

 

 

Task 2: Create End User

Before introducing data roles, you will create “Chris” and “Scott” as end users. The new Oracle Database identity type that data grants are built around. This is the foundation of how Deep Data Security works: identity-first access, with no schema ownership required. This lab uses password-based end-user authentication.

-- Create Scott as the new type of database user, an end user. MScott is an employee and a manager.
CREATE END USER scott IDENTIFIED BY *****;

-- Create Emma as the new type of database user, an end user. Emma is an employee.
CREATE END USER chris IDENTIFIED BY *****; 

 

Task 3: Create Database Roles

Create a database role that grants CREATE SESSION. This role allows your end users (Chris and Scott) to open a direct connection to the database.

CREATE ROLE db_direct_logon_role;
GRANT CREATE SESSION TO db_direct_logon_role; 

 

Task 4: Create Data Roles:

Create the data roles. The data grants will be granted to these data roles. Two data roles (employee and manager roles).

CREATE DATA ROLE HR_EMP_DATA_ROLE;
CREATE DATA ROLE HR_MAN_DATA_ROLE; 

 

Task 5: Grant Data Roles to End Users

Grant “HR_EMP_DATA_ROLE” to “Chris” & “Scott”. Grant “HR_MAN_DATA_ROLE” to “Scott” (the manager).

GRANT DATA ROLE HR_EMP_DATA_ROLE to chris;
GRANT DATA ROLE HR_EMP_DATA_ROLE to scott;
GRANT DATA ROLE HR_MAN_DATA_ROLE to scott; 

 

Task 6: Grant Database Role to Data Role

Grant the “DB_DIRECT_LOGON_ROLE” database role to the "HR_EMP_DATA_ROLE” data role.

GRANT db_direct_logon_role TO HR_EMP_DATA_ROLE; 

 

Verify the data role grants are in place.

SELECT data_role, role_type, grantee, grantee_type
FROM dba_data_role_grants; 

 

 

Task 7: Create Data Grant Based on Employee Role

-- Create a data grant that identifies employee.
CREATE OR REPLACE DATA GRANT hr.HR_EMPLOYEE_ACCESS
AS SELECT, UPDATE(phone_number)
ON hr.employees
WHERE upper(user_name) = upper(ORA_END_USER_CONTEXT.username)
TO HR_EMP_DATA_ROLE; 

 

The predicate (WHERE user_name = ORA_END_USER_CONTEXT.username) is evaluated at query time. A built-in SQL function (ORA_END_USER_CONTEXT.username) resolves the identity of the authenticated end user — no setup, no configuration required. When “Chris” runs any query on hr.employees, Oracle Database silently rewrites it to add this predicate.

-- Create a data grant that identifies the manager of each employee.
CREATE OR REPLACE DATA GRANT hr.HR_MANAGER_ACCESS
AS SELECT (ALL COLUMNS EXCEPT ssn), UPDATE (salary, department_id)
ON hr.employees
WHERE manager_id IN (SELECT m.manager_id
FROM hr.managers m
WHERE upper(m.mgr_user_name) = upper(ORA_END_USER_CONTEXT.username))
TO HR_MAN_DATA_ROLE; 

 

This data grant should have a limited number of columns a manager (Scott) can SELECT, as well as a limited number of columns they can UPDATE.

Verify the data grants are in place. The query returns 14 rows — one per column per privilege.

Note: SSN does not appear in HRAPP_MANAGER_ACCESS — the ALL COLUMNS EXCEPT ssn clause excluded it. The blank COLUMN_NAME for the employee SELECT row reflects SELECT (ALL COLUMNS).

SELECT column_name, grant_name, privilege, grantee
FROM dba_data_grants
WHERE object_owner = 'HR'
AND object_name = 'EMPLOYEES'
ORDER BY grant_name, privilege, column_name; 

 

Verify the predicate by running the query below.

SELECT DISTINCT grant_name, predicate
FROM dba_data_grants
WHERE object_owner = 'HR'
AND object_name = 'EMPLOYEES'
ORDER BY grant_name; 

 

 

Task 8: Connect as Employee End User

Connect as “Chris” end user (employee).

Note: ORA_END_USER_CONTEXT produces a JSON object containing attributes about the authenticated end user — USERNAME is one of those attributes.

SELECT ORA_END_USER_CONTEXT.username FROM DUAL; 

 

When “Chris” runs a query on the employee table without a WHERE clause, the query will return only the “Chris” employee record. Data grant allows SELECT (ALL COLUMNS) for rows where user_name matches the end user identity.

select * from hr.employees; 

 

Now, as “Chris”, try updating his phone number. The data grant includes UPDATE(phone_number), so this should succeed.

UPDATE hr.employees SET phone_number = '555-555-5555' WHERE first_name = 'Chris'; 

 

If “Chris” attempted to update his salary, the data grant has no UPDATE privilege on the salary column. No error. Oracle Database silently blocks the update because the data grant does not include UPDATE(salary). Even if an AI agent were tricked into generating this statement, the database enforces the boundary.

 UPDATE hr.employees SET salary = 200000 WHERE first_name = 'Chris'; 

 

If “Chris” attempted to delete rows from the employees table, the delete statement would raise an error.

 

Task 9: Connect as Manager End User

Connect as “Scott” end user (manager).

SELECT ORA_END_USER_CONTEXT.username FROM DUAL; 

 

When “Scott” runs a query on the employee table without a WHERE clause, the query will return his three direct reports and himself. “Scott” sees his own row because he is an employee — HR_EMPLOYEE_ACCESS matches his identity. That result is joined with the rows returned by HR_MANAGER_ACCESS, which match employees who report to him.




But if “Scott” tries to query the Social Security Number, instead of the phone number, he only sees his own SSN.

Now, as “Scott”, try updating Chris’s salary. The data grant includes UPDATE(salary, department_id) for managers, so this should succeed.

UPDATE hr.employees SET salary = salary*1.5 WHERE first_name = 'Chris'; 


If “Scott” attempts to update Chris’s phone number. No error — but no rows changed. Oracle Database silently blocks the update because the data grant does not include UPDATE(phone_number).

 UPDATE hr.employees SET phone_number = '555-444-4444' WHERE first_name = 'Chris'; 

 

If “Scott” attempted to delete rows from the employees table, the delete statement would raise an error.

To learn more about Oracle Database 26ai and how DSP-Eclipsys can help you modernise and secure your Oracle environment, contact us today.
View full post