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

Note the following high-level behaviors of data grants:
  • Unauthorized column values are returned as NULL. On a SELECT (or any DML statement with a SELECT clause), cells that the end user is not authorized to read are returned as NULL.
  • Unauthorized rows and columns, and predicate-violating updates or deletes are silently skipped. Rows that fail an applicable predicate are filtered out of query results. When an UPDATE targets multiple cells in a single row and any target cell is unauthorized, the entire row is left unchanged and no error is raised, provided the user has some UPDATE and SELECT privileges on the table or view through the data grant. A DELETE that violates an authorized predicate is silently skipped, provided the user has some DELETE and SELECT privileges on the table or view through the data grant.
  • Errors are raised in the following cases. An INSERT, UPDATE, or DELETE on a table or view for which the user has no applicable privilege granted through a data grant fails with ORA-41900. An INSERT that violates an authorized predicate or attempts to populate a column outside the granted scope fails with ORA-28115. An UPDATE or DELETE also fails with ORA-41900 when the statement reads a column for which the user lacks SELECT privilege: for UPDATE, this applies to columns referenced on the right-hand side of a SET expression or in the WHERE clause; for DELETE, this applies to columns referenced in the WHERE clause.
  • Data grants are the sole access path when USE DATA GRANTS ONLY is enabled. When this setting is enabled on a table or view, that object can be accessed only through data grants, and the same restrictions are enforced whether the object is queried directly or through a view. This setting applies to Deep Sec users only and does not affect standard database users with table-level privileges. For details, see Enforce Mandatory Data Privileges.

Data manipulation language (DML) privilege semantics

Data grants combine row- and column-level privileges into a fine-grained authorization model that applies to all CRUD operations. Each DML operation is evaluated against the user's applicable data grants 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.
    • If the user has no UPDATE privilege at all on the target table or view, the UPDATE fails with ORA-41900.
    • If the right-hand side of a SET expression or the WHERE clause references a column for which the user lacks SELECT privilege, the UPDATE fails with ORA-41900.
    • When multiple cells in a single row are targeted, all cells must be individually authorized; if any target cell is unauthorized (for example, the UPDATE modifies a column outside the granted scope), 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 the user has no INSERT privilege at all on the target table or view, the INSERT fails with ORA-41900.
    • If the INSERT violates an authorized predicate or attempts to populate a column outside the granted scope, the INSERT fails with ORA-28115.
  • DELETE: This is a row-level operation enforcing row-level DELETE privilege. Unlike other operations, DELETE privileges cannot be combined with column-level grants.
    • If the user has no DELETE privilege at all on the target table or view, the DELETE fails with ORA-41900.
    • If the WHERE clause references a column for which the user lacks SELECT privilege, the DELETE fails with ORA-41900.
    • If the DELETE violates an authorized predicate, the DELETE is silently skipped and no error is raised, provided the user has some DELETE and SELECT privileges on the target table or view through the data grant.

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