Deep Data Security is here to stay #JoelKallmanDay

TL;DR: Deep Data Security (DDS) in Oracle 26ai lets you describe in SQL who may read or change which rows and columns, and the database enforces it for every query, whether it comes from your app, a report or an AI agent. What is still missing is easy identity propagation for real applications and APEX.

Imagine an employee portal. Employees can see their own details. Managers can see their team’s salaries, but not their social security numbers.

You can implement that in application code. Then you build a reporting tool, an API, or a second application. Each needs the same rules. Every new query becomes another place where someone can forget a filter.

We already trust the database to enforce primary keys and foreign keys. A forgotten check in the application cannot violate a foreign key, because the database refuses the row. Access rules can work the same way: a query only reaches the data its caller is allowed to use, without an explicit WHERE in the query.

Deep Data Security (DDS) does that. The application tells the database who the end user is, and the SQL no longer has to repeat the access rules.

DDS introduces three main building blocks as DDL:

This fits naturally into how we manage the data model. Keep the DDL in Git, review a policy change in a pull request, and deploy it with the corresponding table changes. The database also exposes the policies in its data dictionary.

I like being able to read a grant and understand the intended permission directly. The example below needs no PL/SQL policy functions, unlike VPD (more on that later), and no separate views per audience.

Alice manages Bob and Carol. Everybody can read their own record and update their own phone number. Alice can additionally read her team’s records, with their SSNs hidden, and change their salaries. Carol appears only as a row; we create end users just for Alice and Bob.

To run this you need Oracle 26ai 23.26.2. The script assumes you are connected as the schema owner dds_demo, who needs these privileges:

• CREATE END USER
• CREATE DATA ROLE
• CREATE DATA GRANT
• ADMINISTER ANY DATA GRANT
• GRANT ANY DATA ROLE
• CREATE SESSION with admin option (only for the demo login role below)

create table employees (
  username varchar2(30 char) primary key,
  manager  varchar2(30 char),
  ssn      varchar2(11 char),
  phone    varchar2(20 char),
  salary   number
);

insert into employees values ('alice', null,    '111-11-1111', '555-0100', 10000);
insert into employees values ('bob',   'alice', '222-22-2222', '555-0200',  6000);
insert into employees values ('carol', 'alice', '333-33-3333', '555-0300',  7000);
commit;

-- end users: the people whose access is evaluated
create end user "alice" identified by Demo_Only_12345;
create end user "bob"   identified by Demo_Only_12345;

-- data roles: groups of data permissions that end users can hold
create data role employee_role;
create data role manager_role;

-- Demo only: end users need CREATE SESSION to connect directly.
-- In a real app, the application connects and passes the end user's identity.
create role demo_login;
grant create session to demo_login;
grant demo_login to employee_role;
grant demo_login to manager_role;

grant data role employee_role to "alice";
grant data role manager_role to "alice";
grant data role employee_role to "bob";

Now the interesting part: the entire access policy is two grants.

-- a data grant defines what a data role may do on an object

-- employees may read their own row and update its phone
create data grant own_record
  as select, update (phone) on employees
  where username = ora_end_user_context.username
  to employee_role;

-- managers may read their direct emps, excluding SSN, and update their salaries
create data grant direct_reports
  as select (all columns except ssn), update (salary) on employees
  where manager = ora_end_user_context.username
  to manager_role;

ora_end_user_context.username returns the name of the current DDS end user. Oracle sets this context when the end user connects, or when an application passes the identity through. In our example, it returns bob or alice.

One detail to keep in mind when evolving the schema: ALL COLUMNS EXCEPT ssn includes future columns too. If you later add another sensitive field, review this grant or use an explicit list of readable columns.

These examples follow the employee and manager patterns in Oracle’s data grant examples.

Connect as Bob in SQLcl or SQL*Plus, then run the query. Replace localhost:1521/FREEPDB1 with your database host, port, and service name:

connect "bob"/Demo_Only_12345@localhost:1521/FREEPDB1

select username, ssn, phone, salary
  from employees
 order by username;

Connect as Alice and run exactly the same query:

Alice holds both roles, so both grants apply to her. She sees her own SSN through the employee grant; the manager grant exposes her reports, with their SSNs returned as NULL.

As Bob:

update employees set phone = '555-9999';
-- 1 row updated: Bob's own phone.

update employees set salary = 999999;
-- 0 rows updated: Bob cannot change salaries.

As Alice:

update employees set salary = salary + 500;
-- 2 rows updated: Bob and Carol. Alice's salary stays unchanged.

There is no WHERE clause here. The grant scopes the operation.

In real application code, still include your intended filter. It makes the intent clear, and privileged accounts bypass the grants: the same statement run as one of them would touch every row.

Also check the affected row count. An unauthorized update does not fail, it silently skips the rows. Other violations, such as an unauthorized insert, raise an error. So a successful statement does not prove that the change happened. See the DML behavior docs for details.

The same grant model covers INSERT and DELETE. For example, an employee-owned notes table could allow all four operations with one rule:

create table employee_notes (
  username varchar2(30 char),
  note     varchar2(200 char)
);

create data grant own_notes
  as select, insert, update (note), delete on employee_notes
  where username = ora_end_user_context.username
  to employee_role;

Bob can create and delete his own notes; he cannot insert a note owned by Alice.

Temporary access can have an expiry built into its DDL. This separate grant gives managers read access to notes during November:

create data grant november_notes_review
  as select on employee_notes
  to manager_role
  start time timestamp '2026-11-01 00:00:00'
  end time timestamp '2026-12-01 00:00:00';

You can also derive access from a related table. This grant says: a note is visible to whoever can see the matching row in employees. It is an alternative to the notes grants above:

create data grant notes_follow_employee
  as select on employee_notes
  when select (username) granted on employees
  where employee_notes.username = employees.username;

Bob would see his notes; Alice would see hers and her reports’ notes. Grants only add permissions, so installing several of these examples together combines their access. The grant syntax reference covers time windows and related-table grants.

For tables that DDS end users should access exclusively through data grants, an administrator can enable:

set use data grants only on employees enabled;

This tells Oracle: DDS end users may access this table only as allowed by their data grants. Without it, Bob could still reach the data through other paths, for example a view over the table that someone else created and granted to him.

The setting only constrains DDS end users. Ordinary database accounts with access, such as the table owner, are not affected. So if your application connects without passing Bob’s identity, Oracle uses the connecting account’s permissions and Bob’s rules do not apply. Oracle explains this setting here.

Oracle says VPD and RAS remain fully supported (DDS FAQ). But the same FAQ describes DDS as modernizing “the capabilities introduced with Oracle Virtual Private Database (VPD) and Oracle Real Application Security (RAS)”, which positions it as the new default.

For custom application-context solutions I see the biggest win: nobody has to maintain the plumbing that sets the context and appends the filters.

Oracle’s marketing says DDS goes beyond rows and columns to individual cells. As far as I can tell from the documentation, there is no separate cell syntax. A grant combines a row filter (WHERE) with a column list, and a cell is accessible when a grant covers both its row and its column. Our example already shows it: the ssn column is readable on Alice’s own row, but returns NULL on her reports’ rows. Same column, different rows, different result.

One thing to watch when migrating step by step: you cannot combine a VPD column policy and a DDS column-level data grant on the same object (OCI-52563).

In my example, Bob logs in with a database login. That is not how a real application works. A web app or API usually runs a shared connection pool with one technical account, and the end user is somebody else. Oracle’s drivers solve this by attaching an end-user security context to a pooled connection for the duration of a request.

I looked into how this works with a JavaScript app using node-oracledb 7.0 (Thin mode only). I did not test it myself. As far as I understand the docs, the flow is:

1. The pool is created as usual, with the technical account.
2. Per request, the app gets a connection with pool.getConnection().
3. It builds an EndUserSecurityContext and attaches it with connection.setEndUserSecurityContext(ctx).
4. It runs the SQL, then clears the context with clearEndUserSecurityContext() before releasing the connection.

const connection = await pool.getConnection();
connection.setEndUserSecurityContext(
  new oracledb.EndUserSecurityContext({
    databaseAccessToken, // OAuth token that proves the app may access the database
    endUserToken, // token from the IAM system that proves who the end user is
  }),
);
// ... run queries ...
connection.clearEndUserSecurityContext();
await connection.close();

The catch is the tokens. The context always needs a database access token (OAuth client credentials or on-behalf-of), so your app needs an identity provider even if the end user is a local one.

Always clear the context before returning the connection to the pool. A context left behind would leak one user’s identity into the next request.

One of the prominent features of APEX 26.2 is Deep Data Security support. I was extremely happy to see this, but looking deeper I realized that it is not usable for anyone outside of OCI IAM or Entra. It only works with Social Sign-In in combination with OCI IAM or Microsoft Entra. That matches the token requirement above: APEX needs an identity provider that can hand out these tokens.

I hope that general support comes in the future. In WWV_FLOW_DEEPSEC (undocumented, so it may change), the comment for set_end_user_context says that DBMS_SYS_SQL.PARSE_AS_END_USER runs SQL as a specific end user. APEX uses DBMS_SQL for (almost?) everything, so I naively hope it is not hard to use a standard APEX authentication scheme as the identity provider. Then DDS could work in APEX 27.1 without an external cloud identity provider.

I am also a bit disappointed in Oracle’s communication: this big limitation is only mentioned in the detailed documentation, while the new feature guides barely mention it.

• The policies are DDL. We can version them in Git, review them in pull requests and deploy them with the tables they protect.
• The database enforces them on every query. Applications change, reporting tools come and go, and more and more clients write their own SQL, including AI agents. A policy stored with the data outlives all of them.

There are caveats. DDS is new and needs Oracle 26ai 23.26.2. Identity propagation is limited to supported drivers and an identity provider, and in APEX to OCI IAM or Entra. I have only tried small examples, not complex policies or performance at scale. If propagating identity stays tied to cloud identity providers, adoption will be slower than the idea deserves.

Still, a few lines of readable DDL replace a lot of scattered filters, and the database makes sure nobody forgets one. For a new application on a DDS-capable database, I would evaluate data grants before building another custom authorization framework. That is why I think Deep Data Security is here to stay.

This post was published in the context of #JoelKallmanDay. It is the Oracle Community day where many people in the community share their knowledge about Oracle PL/SQL and APEX. You can find more about it in Tim Hall’s announcement post.