Oracle Deep Data Security in Oracle AI Database 26ai: End Users and Data Roles
Oracle AI Database 26ai answers this with a new feature called Oracle Deep Data Security, or “Deep Sec.” Deep Sec is a database-enforced authorization framework: instead of hoping application code, middleware, or an AI agent behaves correctly, you declare exactly which rows, columns, and even individual cells a given user, role, or application is allowed to touch — and the database enforces that rule on every single query, no matter who or what sent it. It works alongside familiar tools such as Oracle Label Security and Data Masking, but it introduces a cleaner, SQL-native way to define fine-grained access that scales far better than row-level security written in PL/SQL.
The easiest way to understand Deep Sec is through three new building blocks that this article will use hands-on:
● Local End users — lightweight identities for application users that do not own schemas or objects, unlike traditional database users.
● Data roles — roles created specifically to carry fine-grained privileges, either mapped to roles in an external identity provider (Microsoft Entra ID, OCI IAM) or managed entirely inside the database.
● Data grants — the actual policy objects. A data grant says who can SELECT, INSERT, UPDATE, or DELETE which rows and which columns, using ordinary, readable SQL instead of hidden procedural logic.
Together, these pieces let you enforce least-privilege access at the row, column, and cell level directly at the source of the data — so the same protection applies whether the request comes from a human user, a reporting tool, or an autonomous AI agent. The rest of this article walks through a first, practical example: creating a local end user, seeing why it behaves differently from a normal database user, and using a data role and a data grant to control what that user can actually do.
Setting the Stage: End Users vs. Standard Users
Oracle AI Database 26ai introduces a second kind of user alongside the traditional database (“standard”) user: the local end user. A local end user is created and managed inside the database, but — unlike a standard user — it owns no schema and no objects. It exists purely as an identity that Deep Sec can authorize through data roles and data grants.
Creating a Local End User
You create a local end user with the new CREATE END USER statement, which mirrors the familiar CREATE USER syntax:
SQL> CREATE END USER vahid IDENTIFIED BY vahid;
End user created.Oracle AI Database also adds a dedicated dictionary view, DBA_END_USERS, so administrators can monitor end users the same way they would monitor DBA_USERS for standard accounts:
SQL> desc dba_end_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(128)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
CREATED_DATE DATE
PROFILE VARCHAR2(128)
AUTHENTICATION_TYPE VARCHAR2(8)
PASSWORD_CHANGE_DATE DATE
SCHEMA VARCHAR2(128)
START_TIME VARCHAR2(37)
END_TIME VARCHAR2(37)
MANDATORY_PROFILE_VIOLATION BOOLEAN
MFA VARCHAR2(8)SQL> select username,user_id,account_status,created_date,profile from dba_end_users;
USERNAME USER_ID ACCOUNT_STATUS CREATED_D PROFILE
---------- ---------- --------------- --------- ----------
VAHID 2147493788 OPEN 13-SEP-26 DEFAULTThis is the first behavior that trips people up coming from traditional Oracle security: creating an end user does not grant it the ability to connect. End users are not full database sessions with their own privilege set; they need to be authorized through a data role before they can do anything, including logging on.
SQL> conn vahid/vahid@OL10:1521/pdb1
ERROR:
ORA-01045: Login denied. User VAHID does not have CREATE SESSION privilege.
Help: https://docs.oracle.com/error-help/db/ora-01045/
Warning: You are no longer connected to ORACLE.This is expected, not a bug: an end user starts with no privileges at all. To give VAHID the ability to connect and to do useful work, we need to hand out privileges the Deep Sec way — through a data role and a data grant — rather than granting privileges to VAHID directly.
Authorizing an End User with Data Roles
A data role is a role created specifically for Deep Sec’s fine-grained model. You can grant it standard database roles (like CREATE SESSION) and data grants, then assign the data role to one or more end users. This keeps authorization declarative and centralized, instead of scattering privilege grants across individual users.
Creating and Granting a Data Role:
SQL> CREATE DATA ROLE db_data_role_vahid;
Data role created.
SQL> CREATE ROLE db_standard_role;
Role created.
SQL> GRANT CREATE SESSION TO db_standard_role;
Grant succeeded.
SQL> GRANT db_standard_role TO db_data_role_vahid;
Grant succeeded.
SQL> GRANT DATA ROLE db_data_role_vahid TO vahid;
Grant succeeded.Notice the pattern: the standard database role db_standard_role carries an ordinary privilege (CREATE SESSION), that standard role is granted to the data role, and the data role is what actually gets assigned to the end user with GRANT DATA ROLE. Now VAHID can connect:
SQL> CONNECT vahid/vahid@OL10:1521/pdb1
Connected.Once connected, VAHID can query the current end user’s own name through USER_END_USERS, but notice what USER_USERS reports instead:
SQL> SELECT username FROM user_end_users;
USERNAME
---------------
VAHID
SQL> SELECT username FROM user_users;
USERNAME
----------------
XS$NULLThis is a direct consequence of end users not owning a schema: under the covers, the session runs as the reserved, schema-less account XS$NULL, and the end-user security context layered on top of it (VAHID, plus whatever data roles are enabled) is what actually determines data access. You can see the same thing by inspecting the active session and the enabled data role directly:
SQL> SELECT username, server, type FROM v$session WHERE username = 'XS$NULL';
USERNAME SERVER TYPE
---------- ---------- ----------
XS$NULL DEDICATED USER
SQL> SELECT * FROM v$end_user_data_role;
ROLE_NAME CON_ID
---------------------- ------
DB_DATA_ROLE_VAHID 4Data Roles Can Still Over-Grant
Deep Sec’s data grants are designed for fine-grained, row/column-level access, but data roles can still be given broad, traditional privileges — including DBA — and that privilege flows down to every end user who holds the role. This is worth demonstrating precisely because it is dangerous:
SQL> GRANT dba TO db_data_role_vahid;
Grant succeeded.With this single grant, VAHID now inherits DBA privileges indirectly and can run any DML against any table in the database — exactly the kind of “excessive agency” risk that Deep Sec exists to prevent. In a real environment, never grant powerful system roles like DBA to a data role; instead, use narrowly scoped data grants (SELECT/UPDATE/INSERT/DELETE on specific rows and columns) so that VAHID — or an AI agent acting as VAHID — can only touch the data it genuinely needs. The example below shows just how much that one grant exposes:
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_NAME PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
1001 VAHID PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1003 RAMZON PRODDB MAZANDARAN_PDB PRODUCTION RESOURCE APPROVED 02-SEP-26
1004 SARA TESTDB NEKA_PDB TEST DEVELOPER_ROLE APPROVED 03-SEP-26
1005 REZA PRODDB EZZARON_PDB PRODUCTION READ ONLY PENDING 04-SEP-26
1006 VAHID TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1007 ALI PRODDB MARZIKOLA_PDB PRODUCTION READ WRITE PENDING 06-SEP-26
1008 RAMZON PRODDB BABOL_PDB PRODUCTION DBA APPROVED 07-SEP-26
1009 REZA TESTDB MAZANDARAN_PDB TEST DEVELOPER_ROLE REJECTED 08-SEP-26
1010 VAHID PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-26Being over-privileged, VAHID can freely modify or remove data that a properly scoped data grant would have blocked:
SQL> DELETE admin.database_access_requests;
10 rows deleted.
SQL> ROLLBACK;
Rollback complete.
SQL> DROP TABLE admin.dg_test;
Table dropped.
SQL> CREATE TABLE admin.tb (id NUMBER, name VARCHAR2(100));
Table created.
SQL> INSERT INTO admin.tb VALUES (1, 'Vahid Yousefzadeh');
1 row created.
SQL> DROP USER usef;
User dropped.
SQL> CREATE USER usef IDENTIFIED BY usef;
User created.
SQL> GRANT dba TO usef;
Grant succeeded.What End Users Still Cannot Do
Even with DBA privileges inherited through a data role, an end user still cannot create objects, because it has no schema to create them in. Deep Sec deliberately blocks DDL that would require object ownership for the reserved XS$NULL principal:
SQL> CREATE TABLE tb (id NUMBER, name VARCHAR2(100));
*
ERROR at line 1:
ORA-28222: Operations cannot be performed on Oracle reserved user or
role "XS$NULL".
Help: https://docs.oracle.com/error-help/db/ora-28222/This single error captures the core idea of Deep Sec end users well: they can be authorized to read and write data through data roles and data grants, but they never become schema owners, and the database keeps that boundary even when a role has been over-granted.
Coming Up Next
This walkthrough covered the basics: creating a local end user, understanding why it starts with zero privileges, connecting it through a data role, and seeing why broad grants like DBA defeat the purpose of fine-grained authorization. In the next part, we will replace that all-or-nothing DBA grant with proper data grants — restricting VAHID to specific rows and columns using CREATE DATA GRANT, so access is scoped exactly the way Deep Sec is designed to be used.
Comments
Post a Comment