17.05 - Object-Level Privileges for Row-Level Security - Teradata Database

Teradata Vantageā„¢ - SQL Data Control Language

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1149-170K

Administrators can grant these privileges for the purpose of temporarily enabling users to bypass (override) the row-level security policy defined on database objects. Because these privileges enable users to override row-level security policy restrictions, they are referred to as override privileges.

The two basic types of override privileges are:

DML Restriction Override Privileges

These object-level privileges enable users to execute DML requests on tables that have row-level security restrictions that prohibit delete, insert, select, and update operations.

Administrators can grant these privileges to temporarily enable users to execute DML requests to perform the prohibited delete, insert, select, and update operations.

These privileges can be granted as follows:
  • To users and roles.
  • On tables or constraint columns.
The DML restriction override privileges are as follows:
  • OVERRIDE DELETE CONSTRAINT

    When granted on the target table, it enables users to bypass validation of the DELETE security policies contained within each of the DELETE constraint functions applicable to the table.

    When granted on a constraint column, it enables users to bypass validation of the DELETE security policy for a specific constraint function.

    Even if this privilege is granted on the target table, a user is not able to delete all rows of the table. This is because a DELETE on the table is also constrained by SELECT constraint functions, which filter out rows that the user is not permitted to access. To delete all rows, a user must be able to bypass all DELETE and SELECT constraint checks.
  • OVERRIDE INSERT CONSTRAINT

    Enables users to submit an INSERT request to specify a value for a column that has the INSERT row-level security constraint.

    INSERT requests must contain the value to be assigned to the constraint column. Teradata Database ensures that the value assigned to a constraint column is one specified by the name:code pairs of the constraint.

  • OVERRIDE SELECT CONSTRAINT

    When granted on a single constraint column, it enables a user to bypass the security policy in the SELECT constraint function associated with that constraint column. This application is used to permit a user to retrieve a single row.

    When granted on all constraint columns, it enables a user to retrieve all rows of the table, because the user is able to bypass the security policy in the SELECT constraint functions associated with all constraint columns of the table.

  • OVERRIDE UPDATE CONSTRAINT

    Enables users to submit an UPDATE request that specifies a value in the SET clause for a column that has the UPDATE row-level security constraint.

You can create a row-level security constraint function (UDF) for each type of DML request that can be executed against tables with row-level security constraints. If a constraint function does not exist for a type of DML request, the request can only be executed by a user who has the override privilege for that type of request.

Rules for Granting These Privileges

You can only grant these privileges as follows:
  • to users and roles.
  • on databases that contain tables with row-level security.
  • on tables with row-level security.
  • on constraint columns (columns with row-level security constraints).
Following are additional rules and restrictions for granting the DML restriction override privilege:
  • You must have the CONSTRAINT ASSIGNMENT privilege to grant these privileges.
  • You cannot grant these privileges WITH GRANT OPTION.

    If you attempt to grant one of the privileges WITH GRANT OPTION, Teradata Database aborts the request and returns an error.

  • The GRANT request must specify the name of the row-level security constraint object on which you want to grant the privilege.
  • The target object of the GRANT request must be a database, a table that has row-level security (a table with one or more row-level security constraints), or a constraint column. An error is returned you attempt to grant an OVERRIDE CONSTRAINT privilege on a table that does not have one or more row-level security constraints or a column that does not have a constraint.
Target of Request Objects the Override Privilege Must Be On
database all constraint columns of all tables within the specified database.
table all constraint columns of the specified table.
column the column.

Archive/Recovery Restriction Override Privileges

These object-level privileges enable users to execute Archive/Recovery utility commands for the purpose of archiving or restoring tables that have row-level security constraints or databases that contain tables with row-level security constraints. Administrators can grant these privileges on databases or tables.

Unlike the DML restriction override privileges, these privileges do not bypass the row-level security policies defined in constraint functions (UDFs).

These privileges are not sufficient to archive or restore tables that have row-level security constraints or databases that have tables with row-level security constraints. You must also have the DUMP or RESTORE privilege on the table or database you want to archive or restore.
The Archive/Recovery override privileges are as follows:
  • OVERRIDE DUMP CONSTRAINT

    Enables users to execute the Archive/Recovery utility ARCHIVE command to archive databases objects defined with one or more row-level security constraint columns. Required on all targets objects of the ARCHIVE command.

  • OVERRIDE RESTORE CONSTRAINT

    Enables users to execute the Archive/Recovery utility COPY and RESTORE commands on any database object defined with one or more row-level security constraint columns. Required on all targets objects of the COPY and RESTORE commands.

The rules and restrictions for granting the Archive/Recovery override privileges are as follows:
  • You must have the CONSTRAINT ASSIGNMENT privilege to grant these privileges.
  • You can only grant these privileges to users and roles and on databases and tables.
  • You cannot grant these privileges WITH GRANT OPTION.

    If you attempt to grant one of the privileges WITH GRANT OPTION, Teradata Database aborts the request and returns an error.

  • The GRANT request must specify the name of the row-level security constraint object on which you want to grant the privilege.
  • The target object of the GRANT request must be a database, table, or constraint column.