16.20 - C Function Definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)
The following shows sample C code for the UDFs that will enforce the security policies. The policies used in the example are:
  • For INSERT operations, the new row must contain the sensitivity label of the session.
  • For UPDATE operations, the session sensitivity label must dominate the target row label or the update is not allowed. If the UPDATE is allowed, then the label in the updated row must contain the level of the session and a category which is a combination of the categories from the session and the current row.
  • For DELETE operations, the row cannot be deleted unless the sensitivity level is unclassified and the sensitivity category is null.
  • For SELECT operations, the session sensitivity label must dominate the target row label.
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>

typedef unsigned char byte;

// INSERT Level UDF
void InsertLevel( short int  *sess_level,
                  short int  *new_row )
{
   // Level of new row equals that of the session
   // return level to DBS
   *new_row = *sess_level;
   return;
}

// UPDATE Level UDF

void UpdateLevel( short int  *sess_level,
                  short int  *curr_row,
                  short int  *upd_row )
{
   // Session's level must be equal to or greater than row’s to allow
   // the update. If update is allowed, then the level of the updated
   // row is changed to that of the session.

   // can the session update the row?
   if (*sess_level >= *curr_row)
      // set level in updated row to session's level
      *upd_row = *sess_level;
   else
      // update is not allowed
      *upd_row = 0;
   return;
}

//DELETE Level UDF

void DeleteLevel( short int  *curr_row,
                  char       *result )
{
   // Row can only be deleted if it has a label of unclassified
   if (*curr_row == 1)
      // delete is allowed
      *result = 'T';
   else
      *result = 'F';
   return;
}

// SELECT Level UDF

void ReadLevel( short int  *sess_level,
                short int  *curr_row,
                char       *result )
{
   // Read allowed if session's level is equal to or greater than row's
   if (*sess_level >= *curr_row)
      // select is allowed
      *result = 'T';
   else
      //select is not allowed
      *result = 'F';
   return;
}

//INSERT Category UDF

void InsertCategory( byte sess_cat[],
                     byte new_row[],
                     int *indic1,
                     int *retindic)
{
   int i;

   // policy is that category for new row equals that for the session

   if (*indic1 == -1)  // null session, then null new row
      *retindic = -1;
   else  // non-null session, then new row equals session
   {
      *retindic = 0;
      for (i  =  0; i  <  8; i++)
         new_row[i] = sess_cat[i];
   }
   return;
}

// UPDATE Category UDF

void UpdateCategory( byte sess_cat[],
                     byte curr_row[],
                     byte new_row[],
                     int *indic1,
                     int *indic2,
                     int *retindic)

{

   int i;

   // Policy is that row can be updated if user's category is a
   // superset of the row's category. If the update is allowed,
   // then the category for the new row is a combination of the
   // categories of the current row plus those of the session.

   if (*indic1 == -1)  // if session’s category is null
   {
      if (*indic2 == -1) // if row’s category is null, update is allowed
      {  // both are null
         *retindic = -1;  // new row constraint is null
         return;
      }
      else
      {   // session is null and row is not, so disallow update
         *retindic = 0;
         for (i = 0; i < 8; i++)
            new_row[i] = 0;  // set return category to null for error
         return;
      }
   }
   // session’s category is not null
   *retindic = 0;  // must return a category
   if (*indic2 == -1)  // if row’s category is null, then update
                       // is allowed
      {
         for (i = 0; i < 8; i++)
            new_row[i] = sess_cat[i];  //set return category to session
         return;
      }
   // session’s category and input row’s category are not null
   for (i = 0; i < 8; i++)  // does session dominate row?
      if ((sess_cat[i] & curr_row[i]) != curr_row[i])
         // no it does not, so update is not allowed
         {
         for (i = 0; i < 8; i++)
            new_row[i] = 0;  // set return category to all zeroes
                              //for error
         return;
         }
   // update is allowed; set return category to a combination of
   // session’s and row’s categories
   for (i = 0; i < 8; i++)
      new_row[i] = sess_cat[i] | curr_row[i];  //inclusive or
   return;
}

// DELETE Category UDF

void DeleteCategory( byte curr_row[],
                     char *result,
                     int *indic1,
                     int *retindic)

{
   // Policy is that row can be deleted only if its category is null
   *retindic = 0;  // result is always not null
   if (*indic1 == -1)  // if row's category is not null
      *result = 'T';   // delete is allowed
   else
      *result = 'F';   // delete is not allowed
   return;
}

// SELECT Category UDF

void ReadCategory( byte sess_cat[],
                   byte curr_row[],
                   char *result,
                   int *indic1,
                   int *indic2,
                   int *retindic)

{
   int i;

   // Policy is that row can be selected if user's category is a
   // superset of the row's category.
   *retindic = 0; // result is always not null
   *result = 'T';
   if ((*indic1 == *indic2) && (*indic1 == -1)) // both are null
      return;  // allow select
   if (*indic2 == -1)  // row is null and session isn't
      return;  // allow select
   // both are not null
   *result = 'F';
   for (i = 0; i < 8; i++)  // does session dominate row
      // does session dominate
      if ((curr_row[i] ^ sess_cat[i]) & curr_row[i])
         return;  // no, so select is not allowed
   *result = 'T';  // yes, so allow select
   return;
}