DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle Deep Data Security in Oracle AI Database 26ai: End Users and Data Roles

#oracle #ai #database #26ai

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.
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode 
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 DEFAULT
Enter fullscreen mode Exit fullscreen mode

This 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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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$NULL
Enter fullscreen mode Exit fullscreen mode

This 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     4
Enter fullscreen mode Exit fullscreen mode

Data 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.
Enter fullscreen mode Exit fullscreen mode

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-26
Enter fullscreen mode Exit fullscreen mode

Being 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.
Enter fullscreen mode Exit fullscreen mode

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/
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (1)

Collapse
 
raknaos profile image
Raknaos •

The XS$NULL detail is what makes the whole model click for me. If user_users reports a reserved schema-less principal, then the identity most tooling and audit logic keys on is not the identity that was actually authorized — anything written around "one DB user = one schema owner" silently drifts, which is exactly the assumption old connection pools and grant scripts carry.

I also like that the over-grant path ends on a hard boundary instead of a warning: ORA-28222 on CREATE TABLE after inheriting DBA through a data role is the database refusing to let authorization turn into ownership. Curious about the operational side you haven't written yet — if the end user can read and write but never own, where do teams put the migration and DDL credential, given it can't be the same one?