Oracle Deep Data Security: Data Grants
Last week, I published an article about Oracle Deep Data Security (Deep Sec) in Oracle AI Database 26ai, where I covered End Users and Data Roles and demonstrated how they work together. In this article, I want to continue from that point and focus on Data Grants.
1. What is a Data Grant?
A Data Grant is one of the main mechanisms in Oracle Deep Data Security for defining fine-grained access to data.
Instead of simply granting access to an entire table:
GRANT SELECT ON table TO user;a Data Grant can define which rows a user can access, which columns are available, and which operations the user can perform, such as SELECT, UPDATE, or DELETE.
2. Test Environment
For the practical tests, I created an ADMIN.DATABASE_ACCESS_REQUESTS table containing access requests from several end users. This is the main table used throughout the examples.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_NAME PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1003 RAMZON_EU PRODDB MAZANDARAN_PDB PRODUCTION RESOURCE APPROVED 02-SEP-26
1004 SARA_EU TESTDB NEKA_PDB TEST DEVELOPER_ROLE APPROVED 03-SEP-26
1005 REZA_EU PRODDB EZZARON_PDB PRODUCTION READ ONLY PENDING 04-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1007 ALI_EU PRODDB MARZIKOLA_PDB PRODUCTION READ WRITE PENDING 06-SEP-26
1008 RAMZON_EU PRODDB BABOL_PDB PRODUCTION DBA APPROVED 07-SEP-26
1009 REZA_EU TESTDB MAZANDARAN_PDB TEST DEVELOPER_ROLE REJECTED 08-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-263. Create the End Users and Roles
I first create VAHID_EU, the Data Role, and the standard database role used in the examples. The same Data Role is then assigned to the other end users.
SQL> create end user vahid_eu identified by a;
End user created.
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_EU;
Grant succeeded.In addition, I will create three other users that will be useful for some of the examples later in this article:
SQL> create end user RAMZON_EU identified by r;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO RAMZON_EU;
Grant succeeded.
SQL> create end user ALI_EU identified by a;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO ALI_EU;
Grant succeeded.
SQL> create end user SARA_EU identified by s;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO SARA_EU;
Grant succeeded.4. Row-Level Access
4.1 Start with No Data Grant
Before creating a Data Grant, VAHID_EU cannot access the table:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
ERROR at line 1:
ORA-00942: table or view "ADMIN"."DATABASE_ACCESS_REQUESTS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/This gives us a clear starting point: the end user has the Data Role, but there is no Data Grant allowing access to this table.
4.2 Restrict Access to a User’s Rows
Now, I will give VAHID_EU SELECT access only to the rows related to this user.
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;
Data grant created.When VAHID_EU queries the table, only rows where USERNAME is VAHID_EU are returned.
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;
Data grant created.After creating the Data Grant, I will connect again as VAHID_EU and query the table:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_N PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-264.3 Add More Conditions
The Data Grant can be made more restrictive by combining conditions. For example, the following grant allows VAHID_EU to see only its rows from the TEST environment.
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU' and ENVIRONMENT='TEST'
TO VAHID_EU;
Data grant created.After connecting as VAHID_EU again:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_N PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26Now, only the row that satisfies both conditions is returned.
5. Column-Level Access
Data Grants can also control which columns are available to an end user. For example, we can exclude specific columns while also applying row conditions.
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT(ALL COLUMNS EXCEPT ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE REQUEST_ID>=1006 and REQUESTED_DATE >= '04-SEP-26'
TO VAHID_EU;
Data grant created.When VAHID_EU queries the table, the Data Grant controls both the rows and the columns available to the user.
Data Grants are not limited to SELECT. They can also control DML operations such as UPDATE and DELETE.
SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS delete,update,select
ON admin.database_access_requests
WHERE username='VAHID_EU' and ENVIRONMENT='TEST'
TO VAHID_EU;
Data grant created.6.1 UPDATE
The following UPDATE has no WHERE clause, but only the row permitted by the Data Grant is affected:
SQL> update admin.database_access_requests set REQUEST_STATUS='REJECTED' ;
1 row updated.We can verify the result:
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_N PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE REJECTED 05-SEP-26The REQUEST_STATUS of the accessible row has changed to REJECTED.
6.2 DELETE
The same principle applies to DELETE:
SQL> delete admin.database_access_requests;
1 row deleted.Only the row accessible through the Data Grant is deleted. Rows outside that scope are not affected.
SQL> select * from admin.database_access_requests;
no rows selected7. Column-Specific DML Privileges
A Data Grant can also specify different column permissions for different operations. In this example, VAHID_EU can update ENVIRONMENT and ACCESS_TYPE, while SELECT can expose ENVIRONMENT, ACCESS_TYPE, and REQUEST_STATUS.
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS UPDATE (ENVIRONMENT, ACCESS_TYPE),
SELECT(ENVIRONMENT,ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;
Data grant created.For example, updating ACCESS_TYPE is allowed because that column is included in the UPDATE portion of the Data Grant:
UPDATE admin.database_access_requests
SET ACCESS_TYPE='DBA';
4 rows updated.This example demonstrates that row-level and column-level restrictions can be combined with operation-specific permissions.
8. Using ORA_END_USER_CONTEXT for Multiple End Users
In the previous examples, the Data Grant was created specifically for VAHID_EU. However, we can also create a single Data Grant that works for multiple end users.
For this purpose, I will use ORA_END_USER_CONTEXT.USERNAME. This allows the Data Grant to identify the current end user and return only the rows that belong to that user.
First, I will connect as ADMIN and verify the original data:
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_N PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1003 RAMZON_EU PRODDB MAZANDARAN_PDB PRODUCTION RESOURCE APPROVED 02-SEP-26
1004 SARA_EU TESTDB NEKA_PDB TEST DEVELOPER_ROLE APPROVED 03-SEP-26
1005 REZA_EU PRODDB EZZARON_PDB PRODUCTION READ ONLY PENDING 04-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1007 ALI_EU PRODDB MARZIKOLA_PDB PRODUCTION READ WRITE PENDING 06-SEP-26
1008 RAMZON_EU PRODDB BABOL_PDB PRODUCTION DBA APPROVED 07-SEP-26
1009 REZA_EU TESTDB MAZANDARAN_PDB TEST DEVELOPER_ROLE REJECTED 08-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-26
10 rows selected.The table contains requests for different end users.
Now, I will create the Data Grant:
SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS DELETE,SELECT,
UPDATE (ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username = ORA_END_USER_CONTEXT.username
TO VAHID_EU,RAMZON_EU,SARA_EU,ALI_EU;
Data grant created.The important part of this Data Grant is:
WHERE username = ORA_END_USER_CONTEXT.usernameInstead of specifying one particular username, the condition uses the username of the currently connected end user.
Now, I will connect as each end user and query the table.
For VAHID_EU:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_N PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-26
VAHID_EU can see only the rows belonging to VAHID_EU.
For SARA_EU:
For RAMZON_EU:
The two rows belonging to RAMZON_EU are returned.
Finally, for ALI_EU:
9. When Standard Database Privileges Can Bypass the Data Grant(USE DATA GRANTS ONLY)
By default, Data Grants are applied in addition to standard database privileges. This means that if a privilege is granted directly to an end user through a database role, the user can use that privilege even when a Data Grant imposes a more restrictive condition.
For example, I remove the existing Data Grant and grant SELECT ANY TABLE through the standard role:
SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.
SQL> GRANT select any table 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_EU;
Grant succeeded.VAHID_EU can now query ADMIN.DATABASE_ACCESS_REQUESTS without a row-level Data Grant restriction:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_NAME PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1003 RAMZON_EU PRODDB MAZANDARAN_PDB PRODUCTION RESOURCE APPROVED 02-SEP-26
1004 SARA_EU TESTDB NEKA_PDB TEST DEVELOPER_ROLE APPROVED 03-SEP-26
1005 REZA_EU PRODDB EZZARON_PDB PRODUCTION READ ONLY PENDING 04-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1007 ALI_EU PRODDB MARZIKOLA_PDB PRODUCTION READ WRITE PENDING 06-SEP-26
1008 RAMZON_EU PRODDB BABOL_PDB PRODUCTION DBA APPROVED 07-SEP-26
1009 REZA_EU TESTDB MAZANDARAN_PDB TEST DEVELOPER_ROLE REJECTED 08-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-26The query returns all rows from the table, including rows belonging to other end users.
Now, let’s create a Data Grant that restricts VAHID_EU to rows where the USERNAME matches the current end user:
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS DELETE,SELECT, UPDATE (ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username = ORA_END_USER_CONTEXT.username
TO VAHID_EU;
Data grant created.After creating the Data Grant, I will connect again as VAHID_EU and query the table:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_NAME PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1003 RAMZON_EU PRODDB MAZANDARAN_PDB PRODUCTION RESOURCE APPROVED 02-SEP-26
1004 SARA_EU TESTDB NEKA_PDB TEST DEVELOPER_ROLE APPROVED 03-SEP-26
1005 REZA_EU PRODDB EZZARON_PDB PRODUCTION READ ONLY PENDING 04-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1007 ALI_EU PRODDB MARZIKOLA_PDB PRODUCTION READ WRITE PENDING 06-SEP-26
1008 RAMZON_EU PRODDB BABOL_PDB PRODUCTION DBA APPROVED 07-SEP-26
1009 REZA_EU TESTDB MAZANDARAN_PDB TEST DEVELOPER_ROLE REJECTED 08-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-26The result is still the complete table. The Data Grant does not restrict the rows because VAHID_EU already has the SELECT ANY TABLE database privilege.
This demonstrates an important point: by default, Data Grants are additive with standard database privileges. A direct database privilege can therefore provide access beyond the restrictions defined in a Data Grant.
Enforcing Data Grants
USE DATA GRANTS ONLY is particularly useful when we want to ensure that the row- and column-level restrictions defined by Data Grants cannot be bypassed by other database privileges or alternative access paths such as views.
If we want the Data Grant to be mandatory for a specific table or view, we can enable USE DATA GRANTS ONLY.
I will enable it on ADMIN.DATABASE_ACCESS_REQUESTS:
SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> SET USE DATA GRANTS ONLY ON admin.database_access_requests ENABLED;
Use data grants only set.With this setting enabled, access to the table by Deep Sec end users must be authorized through a Data Grant. Standard database privileges such as SELECT ANY TABLE are no longer sufficient for accessing this object.
Now, I will connect again as VAHID_EU:
SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME DATABASE_N PDB_NAME ENVIRONMENT ACCESS_TYPE REQUEST_STATUS REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
1001 VAHID_EU PRODDB PARS_PDB PRODUCTION DBA APPROVED 01-SEP-26
1002 VAHID_EU PRODDB DERAZKASH_PDB PRODUCTION SELECT ANY TABLE APPROVED 02-SEP-26
1006 VAHID_EU TESTDB DERAZKASH_PDB TEST DEVELOPER_ROLE APPROVED 05-SEP-26
1010 VAHID_EU PRODDB DERAZKOLAH_PDB PRODUCTION READ ONLY APPROVED 09-SEP-26As you can see, even though VAHID_EU has the SELECT ANY TABLE privilege, the access to this table is now controlled by the Data Grant.
This is the main purpose of USE DATA GRANTS ONLY: it enables mandatory access control for the specified table or view. For Deep Sec users, the database privilege alone is no longer enough; the required access must be provided through a Data Grant.
We can disable this feature when it is no longer required:
SQL> SET USE DATA GRANTS ONLY ON admin.database_access_requests DISABLED;
Use data grants only set.After disabling the setting, the normal access behavior is restored, and standard database privileges can again provide access to the object.
Comments
Post a Comment