17.05 - Example of Granting Row-Level Security OVERRIDE Privileges to Users and Roles - 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

Assume that all users are granted the INSERT, UPDATE, DELETE and SELECT discretionary access control (DAC) privileges on the inventory table, access to which is controlled by the row-level security constraints classification_level and classification_category.

The following GRANT request grants those privileges to PUBLIC:

     GRANT INSERT, UPDATE, DELETE, SELECT
     ON inventory
     TO PUBLIC;

Assume that user top_gun is granted the necessary privileges to update the classification_level and the classification_category columns of the inventory table.

User top_gun is defined as follows:

     CREATE USER top_gun AS
     PERM=1E6,
     PASSWORD=Top1111GUN;

To be able to update the classification_level and classification_category columns of inventory, top_gun must have the OVERRIDE UPDATE privilege on those constraints.

The following GRANT requests grant the OVERRIDE UPDATE CONSTRAINT privilege to user top_gun on the classification_level and classification_category constraints.

     GRANT OVERRIDE UPDATE CONSTRAINT (classification_level)
     ON inventory
     TO top_gun;

     GRANT OVERRIDE UPDATE CONSTRAINT (classification_category)
     ON inventory
     TO top_gun;

Assume you have also created a constraint named group_membership. When you initially created this constraint, there was no row-level security policy function specified for the UPDATE and DELETE statement actions in the definition of the group_membership constraint.

To enable the updating and deletion of rows in the emp_record table, you must grant the appropriate privileges to a user or role, so you decide to grant those privileges to the role personnel_clerk, which was earlier granted to user sally_jones. The role personnel_clerk already has all discretionary access control privileges on the table.

The OVERRIDE UPDATE CONSTRAINT and OVERRIDE DELETE CONSTRAINT privileges are granted to personnel_clerk by the security administrator, who has the CONSTRAINT ASSIGNMENT privilege.

The following GRANT request grants the OVERRIDE UPDATE CONSTRAINT and OVERRIDE DELETE CONSTRAINT privileges for the group_membership constraint column to the personnel_clerk role on the emp_record table.

     GRANT OVERRIDE UPDATE, DELETE CONSTRAINT (group_membership)
     ON emp_record TO personnel_clerk;