17.00 - Example: Creating a Simple Constraint UDF to Enforce No Read Up Row-Level Security - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

This example creates a simple constraint definition and its associated UDF to implement a simple No Read Up security policy using sensitivity labels.

Note the following about this function:

  • The SQL create text for the function defines two parameters to handle the data for the constraint.
    • UserClearance
    • RowClassification
  • The input to the UserClearance parameter is data regarding the classification level for the user who wants to read the row.
  • The input to the RowClassification parameter is data regarding the classification level for the row to be read.
  • Both of the parameters the function defines have a SMALLINT data type, which confirms that they relate to a single-level hierarchical classification constraint.

    The name of this single-level hierarchical classification constraint is ReadClassification.

  • The very simple external function compares the values for UserClearance and RowClassification.

    If the value for UserClearance is greater than or equal to the value for RowClassification, SELECT access to the requested row is granted.

    If the value for UserClearance is less than the value for RowClassification, SELECT access to the requested row is not granted.

See Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100 for information about sensitivity labels and No Read Up, No Write Down security policies.

See Teradata Vantage™ - SQL External Routine Programming , B035-1147 for information about writing external function routines to support row-level security constraints.

     CREATE FUNCTION SYSLIB.ReadClassification (
       UserClearance     SMALLINT,
       RowClassification SMALLINT)
     RETURNS CHARACTER(1)
     SPECIFIC SYSLIB.ReadClassification
     LANGUAGE C
     DETERMINISTIC
     NO SQL
     EXTERNAL NAME ‘cs!ReadClassification!c:\udf_ReadClassification.c’
     PARAMETER STYLE TD_GENERAL;

The C code for ReadClassification is as follows.

     #define SQL_TEXT Latin_Text
     #include <sys/types.h>
     #include “sqltypes_td.h”
     void ReadClassification(short int *UserClearance,
                             short int *RowClassification,
                             char *AccessAllowed,
     {
         //Enforce no read up policy - user clearance must dominate row classification
          if (*UserClearance >= *RowClassification)
          // SELECT is allowed
             *AccessAllowed = ‘T’;
          else
         // SELECT is not allowed
             *AccessAllowed = ‘F’;
          return;
     }