17.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 a 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.
For a cross-table data grant, the requirement extends to the schema of the parent object. For example, to create a cross-table data grant in schema A on a child object in schema B, where the required privileges are defined on a parent object in schema C:
CREATE DATA GRANT A.datagrant
   AS SELECT
   ON B.table1
   WHEN SELECT GRANTED ON C.table2
   WHERE <predicate>;

You must have the CREATE DATA GRANT privilege (or the CREATE ANY DATA GRANT privilege at the system level or for schema A), and you must have the ADMINISTER ANY DATA GRANT privilege at the system level or for both schema B and schema C.

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

The CREATE DATA GRANT statement provides two mutually exclusive options. Use datagrant_option to grant the data privileges directly to end users or data roles. Use cross_table_datagrant_option to derive the authorization from privileges that are held on a parent object. 

CREATE [ OR REPLACE ] DATA GRANT [ IF NOT EXISTS ]
  [ schema. ] grant_name AS
  privilege_list
  ON [ schema. ] object
  { datagrant_option | cross_table_datagrant_option };
 
datagrant_option::=
    [ WHERE predicate ]
    TO user_role_list
    [ START TIME timestamp ] [ END TIME timestamp ]
 
cross_table_datagrant_option::=
    WHEN parent_privilege_list
    GRANTED ON [ parent_schema. ] parent_object
    WHERE predicate
 
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
 
parent_privilege_list::=
    privilege [ ( column_list ) ]
  [, privilege [ ( column_list ) ] ]…

Parameters

Parameters common to both options

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.

In a cross-table data grant, these are the privileges granted on the child object. They can be different from the privileges required on the parent object (parent_privilege_list), and a privilege granted on the child object does not have to be granted on the parent object.

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.

You can use the ALL COLUMNS EXCEPT clause only in privilege_list (the AS clause). It is not allowed in parent_privilege_list. For details, see the WHEN parent_privilege_list parameter.

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 (ON clause)

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.

In a cross-table data grant, this object is the child object: the object whose authorization depends on the authorization held on the parent object.

Parameters of datagrant_option

Parameter/Keyword Description

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.
  • 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. 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.

Parameters of cross_table_datagrant_option

Parameter/Keyword Description

WHEN parent_privilege_list

One or more privileges that an end user must have on the parent object to receive the privileges in privilege_list on the matching rows in the child object. Each privilege can optionally be scoped to specific columns of the parent object by listing the columns explicitly, for example WHEN SELECT (customer_id) GRANTED ON.

The parent_privilege_list does not support the ALL COLUMNS EXCEPT clause; using it leads to an error. You must list the required parent column privileges explicitly.

GRANTED ON [ parent_schema. ] parent_object

The parent object: the table or view whose authorizations determine the child object’s authorization. Together with the WHEN clause, GRANTED ON expresses the inheritance relationship between the parent and the child. The privileges on the child object are valid only when the required privileges are granted on the parent object.

The schema of the parent object can be different from the schema of the child object. If the schema is omitted, the current schema is assumed.

predicate

A SQL WHERE clause that specifies the join relationship between the parent and child objects. The predicate is required in a cross-table data grant. It can also include conditions on the child object or the parent object to further restrict the rows to which the cascading access privileges apply.

Reference the parent and child objects in the predicate by their fully qualified names.

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. However, the grant owner isn’t required to have privileges on the target object specified in the ON clause.

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 it, 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.
  • Using an IF EXISTS clause with CREATE DATA GRANT is invalid and raises ORA-11543.
  • 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.
  • The two options, datagrant_option and cross_table_datagrant_option, are mutually exclusive. A cross-table data grant (a data grant with a WHEN … GRANTED ON clause) cannot include a TO clause, a START TIME clause, or an END TIME clause.
  • In a cross-table data grant, the WHERE clause is mandatory and must specify the join relationship between the parent and child objects.
  • A cross-table data grant authorizes access at runtime only when the required privileges have been granted on the parent object, for example through a data grant on the parent object. Grant the required privileges on the parent object for the cross-table data grant to take effect.
  • Access to rows in a child object can be based on more than one parent object: create one cross-table data grant on the child object for each parent object.
  • Cross-table data grants can be hierarchical: the parent object of a cross-table data grant can itself be the child object of another cross-table data grant. At runtime, the required privileges are evaluated along the chain of grants.
  • With OR REPLACE, you can update the privilege_list, parent_privilege_list, and predicate of an existing cross-table data grant. As for all data grants, you cannot change the target object in the ON clause.

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

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

The following data grant provides the employee_role data role with 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 17-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 17-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 17-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's 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 17-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 17-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 17-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 17-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 17-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 17-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 17-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;

Reference tables: Order Entry (oe) schema

The cross-table data grant examples that follow use the following order entry (oe) schema tables: oe.customers, oe.orders, and oe.order_items. These tables form the following parent-child hierarchy: oe.customers > oe.orders > oe.order_items. The oe.orders table is related to its parent oe.customers through the customer_id column, and the oe.order_items table is related to its parent oe.orders through the order_id column. The oe.orders table includes a status column, and the oe.order_items table includes product_id, quantity, unit_price, and discount columns. For an entity-relationship (ER) diagram representing this scenario, see Understand Cross-Table Data Grants.

Example 17-12 Grant access to child rows based on parent authorization

The following pair of data grants allows members of the sales_rep_role data role to view order items based on the orders they can access. The first data grant authorizes SELECT on pending orders. The second (cross-table) data grant authorizes SELECT on oe.order_items for any user who holds SELECT on the corresponding parent row in oe.orders. Note that the cross-table data grant contains no TO clause; the grantees are determined by the privileges held on the parent object. 

-- Grant the SELECT data privilege on all pending orders
CREATE OR REPLACE DATA GRANT oe.customers_orders
    AS SELECT
    ON oe.orders
    WHERE oe.orders.status = 'PENDING'
    TO sales_rep_role;
 
-- Derive SELECT on oe.order_items from SELECT on oe.orders
CREATE OR REPLACE DATA GRANT oe.order_items_by_orders
    AS SELECT
    ON oe.order_items
    WHEN SELECT GRANTED ON oe.orders
    WHERE oe.order_items.order_id = oe.orders.order_id;

The required privilege must be granted on the parent object (here, through the customers_orders data grant) for the cross-table data grant to authorize access to the child object.

Example 17-13 Restrict child rows with additional conditions

Conditions on the child object can further restrict the cascading access privileges to a subset of rows. The following cross-table data grant authorizes access only to order items with a quantity greater than five, for the orders that the user can access.

CREATE OR REPLACE DATA GRANT oe.order_items_by_product
    AS SELECT
    ON oe.order_items
    WHEN SELECT GRANTED ON oe.orders
    WHERE oe.order_items.order_id = oe.orders.order_id
      AND oe.order_items.quantity > 5;

Example 17-14 Restrict the columns granted on the child object

The privilege list on the child object can include column privileges. With the following data grant, a user who holds SELECT on a parent row in oe.orders can view all columns of the corresponding order items except the discount column. 

CREATE OR REPLACE DATA GRANT oe.order_items_by_reference
    AS SELECT (ALL COLUMNS EXCEPT discount)
    ON oe.order_items
    WHEN SELECT GRANTED ON oe.orders
    WHERE oe.order_items.order_id = oe.orders.order_id;

Example 17-15 Require column privileges on the parent object

Column privileges can also be required on the parent object. With the following data grant, only users who have been granted SELECT on the customer_id column of oe.orders can view the corresponding records in oe.order_items. 

CREATE OR REPLACE DATA GRANT oe.order_items_by_customer_access
    AS SELECT
    ON oe.order_items
    WHEN SELECT (customer_id) GRANTED ON oe.orders
    WHERE oe.order_items.order_id = oe.orders.order_id;

Example 17-16 Map parent privileges to a different set of child privileges

A cross-table data grant can map one or more privileges required on the parent object to a different set of privileges on the child object. With the following data grant, a user must hold both the INSERT and UPDATE privileges on an order for the data grant to apply. Users who hold both privileges automatically receive the SELECT, INSERT, and UPDATE privileges on the corresponding order items. 

CREATE OR REPLACE DATA GRANT oe.manage_order_items
    AS SELECT, INSERT, UPDATE
    ON oe.order_items
    WHEN INSERT, UPDATE GRANTED ON oe.orders
    WHERE oe.order_items.order_id = oe.orders.order_id;

Example 17-17 Create a multi-layer cross-table data grant

Cross-table data grants can be hierarchical: the parent object can itself be the target object of another cross-table data grant. In the following example, access to oe.orders is derived from the customers that a user can access, and access to oe.order_items is in turn derived from those orders. 

CREATE DATA GRANT oe.view_orders_by_customers
    AS SELECT, INSERT, UPDATE
    ON oe.orders
    WHEN SELECT, INSERT GRANTED ON oe.customers
    WHERE oe.orders.customer_id = oe.customers.customer_id;
 
CREATE DATA GRANT oe.view_order_items
    AS SELECT, INSERT, UPDATE
    ON oe.order_items
    WHEN SELECT GRANTED ON oe.orders
    WHERE oe.orders.order_id = oe.order_items.order_id;

When a user queries oe.order_items, the user needs the SELECT privilege on the parent object oe.orders, which the view_orders_by_customers data grant in turn makes dependent on the SELECT and INSERT privileges on its own parent object, oe.customers. Deep Sec evaluates the required privileges along the entire chain at runtime.