17.05 - Example: Loading Tables without User OVERRIDE Privileges - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

When a user without the OVERRIDE privilege performs an INSERT or UPDATE on an RLS table the system converts the session constraint value(s), defined as byte(n) in the assigned user constraint, to hex code and loads them into the table.

For example, assume that:

  1. A BYTE(1) non-hierarchical constraint named Countries is defined with these values:
    • USA: 1
    • UK: 2
    • Canada: 3
  2. User U1 is assigned the constraint.
    CONSTRAINT = Countries (USA, UK, Canada)
  3. User U1 defines a table to include the Countries constraint column:
    CT rls_table (x INT, Countries CONSTRAINT);
  4. The security policy defined in the related INSERT UDF does not alter the session constraint for the user.

At logon, the session constraint value for user U1 is calculated by the system as follows:

Constraint Value Bit Position Binary Value
USA:1 1 1
UK:2 2 1
Canada:3 3 1
Not applicable 4 0
5 0
6 0
7 0
8 0

The system evaluates the assigned user constraints and calculates a binary string to represent each set of non-hierarchical values, in the example above, 11100000, which translates to the hex string ‘E0’xb.

If user U1 inserts a row into the table rls_tbl, the system automatically enters the calculated hex value ‘E0’xb in the Countries CONSTRAINT column for the table.

Resetting the Session Constraint

Users have the option to change the default constraint values available for a session using the SET SESSION CONSTRAINT statement.

Based on the previous example, U1 might reset the session default Countries constraint:

SET SESSION CONSTRAINT = Countries (UK, Canada);

The session constraint value is changed from the ‘E0’xb shown above to '60'xb (hex representation of 01100000). Subsequent inserts during the session default to '60'xb for the Countries constraint column.

You can display the hex string for the default constraint values using the HELP SESSION CONSTRAINT statement.