16.1 About Data Grants

Data grants are the central policy mechanism in Oracle Deep Data Security (Deep Sec) for controlling fine-grained access at the row, column, and cell levels.

A data grant authorizes access to specific rows and columns within a table, view, or materialized view by specifying privileges, an optional predicate to filter rows, and the grantee (end user or data role).

Through multiple data grants on the same object, a user can hold different privileges on different row sets and column values (cells); for example, SELECT on some rows, UPDATE on specific column values within a subset of those rows, and SELECT, UPDATE, DELETE on another set of rows. All data grants are additive: the effective privilege is the union of all applicable grants.

You define data grants using the CREATE DATA GRANT statement and remove them with DROP DATA GRANT. You can grant them only to Deep Sec end users or data roles; standard database users and roles cannot be grantees of a data grant.

You can query the DBA_DATA_GRANTS data dictionary view to review the existing data grants.

Data manipulation language (DML) privilege semantics

The combination of row and column grants produces a fine-grained authorization model that applies to all CRUD operations. Each DML operation is evaluated as follows:
  • SELECT: This is a row and column-level operation. It returns only rows where a row-level or authorized column-level predicate evaluates to TRUE.
    • For returned rows, unauthorized column values (cell values) are NULL.
    • DML statements with SELECT clauses (such as INSERT ... AS SELECT, CREATE TABLE AS SELECT, and UPDATE ... AS SELECT) also use NULL for unauthorized column values.
    • The SELECT privilege is also checked for UPDATE, INSERT, and DELETE statements with RETURNING INTO clauses.
    • If the sql92_security parameter is enabled, the SELECT privilege is also evaluated for UPDATE and DELETE statements.
  • UPDATE: This is a cell-level operation. It updates only cells that satisfy either a row-level or column-level predicate.
    • When multiple cells in a single row are targeted, all cells must be individually authorized; if any target cell is unauthorized, the entire row update is silently skipped.
    • Additionally, a post-update check ensures the updated data still satisfies the authorized predicates (equivalent to a WITH CHECK OPTION in views). If it doesn't, the row is not updated.
  • INSERT: This is a row or cell-level operation. It inserts records only if the user has a row-level privilege or column-level privileges for all columns being populated. Any unspecified columns are populated with their default values. If data to be inserted does not satisfy the authorized predicates, an error is raised.
  • DELETE: This is a row-level operation enforcing row-level delete privilege. Unlike other operations, DELETE privileges cannot be combined with column-level grants.

Reference table: hr.employees

All examples in this chapter use the following hr.employees table. 

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  EMAIL       MANAGER     SSN          SALARY  PHONE
-----------  ----------  ---------  ----------  ----------  -----------  ------  --------
100          Victoria    Williams   vwilliams               219-09-9999  13000   555-0100
200          Marvin      Anderson   manderson   vwilliams   457-55-5462  12030   555-0200
300          Chris       Evans      cevans      vwilliams   321-12-4567   6900   555-0300
400          Emma        Baker      ebaker      manderson   733-02-9821   8200   555-0400
500          Taylor      Mills      tmills      manderson   558-76-1243   9000   555-0500