Example of Granting Row-Level Security OVERRIDE Privileges to Users and Roles - Advanced SQL Engine - Teradata Database

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;