Create Data Grants

16.2 Create Data Grants

Use the CREATE DATA GRANT statement to create a named policy that authorizes Oracle Deep Data Security (Deep Sec) end users or data roles to perform CRUD operations on targeted rows and columns of a table or view.

Required privilege

The CREATE DATA GRANT system privilege to create data grants in the user's own schema.
The CREATE ANY DATA GRANT system privilege to create data grants in any schema (can also be granted at the schema level).
The ADMINISTER ANY DATA GRANT system privilege to create data grants on objects in any schema (can also be granted at the schema level for non-SYS schema).

For example, to create a data grant in schema A that targets a table in schema B:

CREATE DATA GRANT A.datagrant 
   AS SELECT 
   ON B.table1 
   WHERE <predicate> TO grantee;

You must have the CREATE DATA GRANT privilege (or

CREATE ANY
                    DATA GRANT

) on schema A, and you must have the

ADMINISTER ANY
                    DATA GRANT

privilege at the system level or on schema B.

Note:

Data grants are not supported for objects owned by SYS, with the exception of the SYS.END_USER_CONTEXT view.
To create data grants on the SYS.END_USER_CONTEXT view, the data grant creator must have the ADMINISTER ANY DATA GRANT system privilege at the system level.

Syntax

CREATE [ OR REPLACE ] DATA GRANT [ IF NOT EXISTS ]
  [schema. ] grant_name AS
  privilege_list 
  ON [ schema. ] object
  [ WHERE predicate ]
  TO user_role_list
  [ START TIME timestamp ] [ END TIME timestamp ];
  
privilege_list::= privilege_item [, privilege_item]…

privilege_item::= privilege [ ( [ALL COLUMNS EXCEPT] column_list ) ] 

privilege::= SELECT | UPDATE | DELETE | INSERT

column_list::= column [, column ]… 

user_role_list::= user_role [, user_role ]… 

user_role::= end_user | data_role

Parameters

Parameter/Keyword	Description
`schema`	The schema name of the data grant being created. Must be a valid database user. If omitted, the current schema is used. Error `ORA-52553` is raised if the schema (owner) of the data grant is invalid, that is: The owner is not a valid database user, or A Deep Sec user (whose schema is `XS$NULL`) tries to create a data grant without specifying a schema name.
`grant_name`	The name of the data grant object. If the data grant already exists and neither `OR REPLACE` nor `IF NOT EXISTS` is specified, `ORA-52550` is raised.
`privilege_list`	One or more CRUD operations (`SELECT`, `UPDATE`, `INSERT`, `DELETE`) granted by this data grant. A privilege cannot appear more than once in the list; duplicate privileges raise `ORA-01711`.
`ALL COLUMNS EXCEPT`	A setting that grants the specified privilege on all columns of the target object except those listed in `column_list`. Future columns added to the object are also granted to the grantees.
`column_list`	A list of columns to be specified for `SELECT`, `UPDATE`, and `INSERT` only. If omitted, the privilege is granted on all columns in the table or view. Column-level privileges cannot be specified for `DELETE`; if specified, `ORA-52502` is raised. If the same column appears more than once in the list, `ORA-00957` is raised.
`object`	The target table, view, or materialized view. If the schema is omitted, the current schema is assumed. If the object doesn't exist, an error is raised. There can be only one object per `ON` clause.
`predicate`	A SQL `WHERE` clause that identifies the rows to which the data grant applies. It represents a subset of rows within a table or view. If omitted, the grantee can access all rows. Supports subqueries and references to the end-user context (`ORA_END_USER_CONTEXT`). Predicates in data grants have the following limitations: They cannot exceed 4,000 characters. `GROUP BY` extension operators (`GROUPING SETS`, `CUBE`, `ROLLUP`) are not supported. If cyclic predicates (referencing the same object as the `ON` clause) are used, `ORA-52561` is raised at query-execution time. Note: The owner (schema) of the data grant must have sufficient privileges on any objects referenced in the predicate; for example, `SELECT` on tables and views, and `EXECUTE` on PL/SQL functions or SQL macros. These privileges are enforced at runtime, not at DDL creation time. However, the grant owner isn’t required to have privileges on the target object specified in the `ON` clause.
`user_role_list`	One or more Deep Sec end users or data roles receiving the privilege. Standard database users and roles cannot be grantees. An end user or a data role cannot appear more than once in the list; if it does, `ORA-52501` is raised. If a grantee does not exist or is not a Deep Sec end user or data role, `ORA-52551` is raised.
`end_user`	The name of an end user created in the database. See Create End User.
`data_role`	The name of a data role. See Create Data Role.
`START TIME`	The time from which the data grant is effective. Must not be later than `END TIME`; if it is, `ORA-52503` is raised. If omitted, the data grant takes effect immediately.
`END TIME`	The time at which the data grant becomes inactive. If omitted, the grant remains in effect indefinitely.

Usage notes and restrictions

When IF NOT EXISTS is specified:
- If the data grant exists, the statement is a no-op. No error is raised.
- If the data grant does not exist in the schema, it is created.
When IF NOT EXISTS is omitted:
- If the data grant already exists, ORA-52550 is raised.
- If the data grant does not exist in the schema, it is created.
When OR REPLACE is specified:
- If the data grant already exists, its definition is replaced without dropping the grant.
- If the data grant does not exist, it is created.
When OR REPLACE is omitted:
- If the data grant already exists, an error is raised.
- If the data grant does not exist, it is created.
With OR REPLACE:
- You can update the privilege_list, user_role_list, predicate, START TIME, and END TIME of an existing data grant.
- You cannot change the target object (the ON clause object). If you attempt to change, ORA-52556 is raised.
OR REPLACE and IF NOT EXISTS cannot be combined in the same statement. If combined, ORA-11541 is raised.
WITH GRANT OPTION is not supported for data grants.
The predicate is validated for syntax and object existence at CREATE DATA GRANT time. Cyclic reference checks (for example, where the predicate references the same table as the ON clause) are performed at query-execution time.
You can specify START TIME and END TIME independently of each other. If START TIME is not specified, the data grant is effective immediately. If END TIME is not specified, the data grant is effective until it is dropped.
You cannot create data grants on views that reference remote objects through database links. You also cannot reference remote objects in a data grant predicate. If you attempt either, the database raises ORA-52554.
For data grants on the SYS.END_USER_CONTEXT view, only row-level SELECT and UPDATE privileges are supported. Other privileges and column-level privileges are not permitted.

For syntax diagrams and additional details, see CREATE DATA GRANT in Oracle AI Database SQL Language Reference.

Example 16-1 Grant full SELECT access on a table without a predicate

The following data grant provides the data role employee_role full access to the hr.employees table.

CREATE OR REPLACE DATA GRANT hr.employees_full_grant
  AS SELECT
  ON hr.employees
  TO employee_role;

Example 16-2 Grant row-level SELECT access to an employee's own record

The following data grant allows employees to view only their own record in hr.employees. The predicate compares the employee's email against the current end user's name from the end-user security context.

CREATE OR REPLACE DATA GRANT hr.employees_own_record
    AS SELECT
    ON hr.employees
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;

When Emma, who has the employee_role data role, queries the table, only her own row is returned.

EMMA> SELECT * FROM hr.employees;
 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL   MANAGER    SSN         SALARY PHONE
----------- ---------- --------- ------- ---------- ----------- ------ --------
400         Emma       Baker     ebaker  manderson  733-02-9821  8200  555-0400
 
1 row selected.

Example 16-3 Grant cell-level UPDATE access to an employee's own phone number

The following data grant allows employees to view their record and update only their own phone number.

CREATE OR REPLACE DATA GRANT hr.employee_update_record
    AS SELECT, UPDATE (phone)
    ON hr.employees
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;

Example 16-4 Grant hierarchical access with column masking for managers

The following data grant allows managers to view their direct reports' records, excluding the social security number (SSN) column, and to update their direct reports' salary.

CREATE OR REPLACE DATA GRANT hr.manager_direct_reports
    AS SELECT (ALL COLUMNS EXCEPT ssn), UPDATE (salary)
    ON hr.employees
    WHERE manager = ORA_END_USER_CONTEXT.username
    TO manager_role;

Marvin, who holds both employee_role (own-record) and manager_role (direct reports) data roles, sees the following result:

MARVIN> SELECT * FROM hr.employees;
 
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL      MANAGER    SSN         SALARY PHONE
----------- ---------- --------- ---------- ---------- ----------- ------ --------
200         Marvin     Anderson  manderson  vwilliams  457-55-5462 12030  555-0200
400         Emma       Baker     ebaker     manderson              8200   555-0400
500         Taylor     Mills     tmills     manderson              9000   555-0500
 
3 rows selected.

Emma and Taylor's SSN values are returned as NULL because Marvin does not hold the SELECT privilege for the SSN column in the manager_direct_reports data grant.

Example 16-5 Grant INSERT access with column exclusions

The following data grant allows employees to insert values for their own record into all columns except SSN and SALARY.

CREATE OR REPLACE DATA GRANT hr.employees_own_record
    AS SELECT, UPDATE (first_name, phone),
       INSERT (ALL COLUMNS EXCEPT ssn, salary)
    ON hr.employees
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role;

Example 16-6 Create a time-bound data grant

The following data grant is active only within a specified time window.

CREATE OR REPLACE DATA GRANT hr.temp_access_grant
    AS SELECT
    ON hr.employees
    WHERE email = ORA_END_USER_CONTEXT.username
    TO employee_role
    START TIME TO_TIMESTAMP('2026-03-01 19:30:00',
                            'YYYY-MM-DD HH24:MI:SS')
    END TIME TO_TIMESTAMP_TZ('2026-09-01 19:30:00',
                           'YYYY-MM-DD HH24:MI:SS TZH:TZM');

Example 16-7 Avoid cyclic references in data grant predicates

The following command is valid at DDL time but raises an error at query-execution time due to a cyclic reference in the predicate (the predicate queries the same table as the ON clause).

CREATE OR REPLACE DATA GRANT hr.loop_grant
    AS SELECT
    ON hr.employees
    WHERE employee_id IN (
        SELECT employee_id FROM hr.employees
    )
    TO employee_role;

-- At query time, this raises:
-- ORA-52561: Invalid predicate in data grant.

Example 16-8 Grant access to a specific end user

The following data grant targets a specific end user instead of a data role.

CREATE OR REPLACE DATA GRANT hr.marvin_emp_access
    AS SELECT
    ON hr.employees
    TO marvin_user;

Example 16-9 Grant access to multiple grantees

The following data grant includes multiple grantees.

CREATE OR REPLACE DATA GRANT hr.admin_access
    AS SELECT, UPDATE (salary)
    ON hr.employees
    TO employee_role, manager_role;

Example 16-10 Create a data grant on a view

The following data grant's target object is a view.

CREATE VIEW hr.employees_view AS
    SELECT * FROM hr.employees;

CREATE OR REPLACE DATA GRANT hr.employees_view_grant
    AS SELECT
    ON hr.employees_view
    TO employee_role;

Example 16-11 Control access to end-user context attributes using a data grant

The following data grant allows hcm_admin_role to access attributes in the end-user context named hr.hcm_context.

CREATE DATA GRANT update_hcm_attr
    AS SELECT, UPDATE
    ON SYS.END_USER_CONTEXT
    WHERE OWNER = 'HR' AND NAME = 'HCM_CONTEXT'
    TO hcm_admin_role;