17.00 - Example: Creating a Row-Level Security Function - 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 several row-level security functions to update table constraint column values. Note the following about these functions.

  • The parameter name CURRENT_SESSION defines the source data for the parameter as the current session value for the constraint that is associated with the UDF.
  • The parameter name INPUT_ROW defines the source data for the parameter as the value in the corresponding constraint column of the row that is the object of the invoking request.
  • The data types assigned to these constraints for the various example functions enable you to determine whether the DML operation being performed applies to a classification constraint or a compartment constraint.

The insert_level function inserts a row using the level value for the session (as determined from the parameter name CURRENT_SESSION) as input and returns the level to be assigned to the new row. Because the CURRENT_SESSION parameter is defined with a SMALLINT data type, you know that the level value to be inserted concerns a single value of a hierarchical classification constraint.

     CREATE FUNCTION SYSLIB.insert_level (
       CURRENT_SESSION SMALLINT)
     RETURNS SMALLINT
     LANGUAGE C
     NO SQL
     PARAMETER STYLE TD_GENERAL
     EXTERNAL NAME 'CS!insertlevel!c:\cctests\insertlevel.c';

The update_level function updates a row using the level value for the session (as determined from the parameter name CURRENT_SESSION) as input and the level value from the target row (as determined from the INPUT_ROW parameter). It returns the level to be assigned to the updated row. Because both the CURRENT_SESSION and INPUT_ROW parameters are defined with a SMALLINT data type, the level value to be updated concerns a single value of a hierarchical classification constraint.

     CREATE FUNCTION SYSLIB.update_level (
       CURRENT_SESSION SMALLINT, 
       INPUT_ROW       SMALLINT)
     RETURNS SMALLINT
     LANGUAGE C
     NO SQL
     PARAMETER STYLE TD_GENERAL
     EXTERNAL NAME 'CS!updatelevel!c:\cctests\updatelevel.c';

The delete_level function deletes a row using the level value from the target row (as determined from the parameter name INPUT_ROW) as input. Because the INPUT_ROW parameter is defined with a SMALLINT data type, the row to be deleted concerns a single value of a hierarchical classification constraint.

The associated external function for this UDF returns either the character T to indicate that the specified deletion can be done or the character F to indicate that the specified deletion cannot be done.

     CREATE FUNCTION SYSLIB.delete_level (
       INPUT_ROW SMALLINT)
     RETURNS CHARACTER
     LANGUAGE C
     NO SQL
     PARAMETER STYLE TD_GENERAL
     EXTERNAL NAME 'CS!deletelevel!c:\cctests\deletelevel.c';

The read_level function selects a row using the level value from the session (as determined from the parameter name CURRENT_SESSION) and the level value from the target row (as determined from the INPUT_ROW parameter) as input. Because both the CURRENT_SESSION parameter and the INPUT_ROW parameter are defined with a SMALLINT data type, the row to be read concerns a single value of a hierarchical classification constraint.

The associated external function for this UDF returns either the character T to indicate that the select operation can be done or the character F to indicate that the select operation cannot be done.

     CREATE FUNCTION SYSLIB.read_level (
       CURRENT_SESSION SMALLINT, 
       INPUT_ROW       SMALLINT)
     RETURNS CHARACTER
     LANGUAGE C
     NO SQL
     PARAMETER STYLE TD_GENERAL
     EXTERNAL NAME 'CS!readlevel!c:\cctests\readlevel.c';

The function inserts a row using the category value for the session (as determined from the CURRENT_SESSION parameter) as input. Because the CURRENT_SESSION parameter is defined with a BYTE(8) data type, you know that the row to be inserted concerns a non-hierarchical compartment constraint.

The associated external function for this UDF returns the category to be assigned to the new row.

     CREATE FUNCTION SYSLIB.insert_category (
       CURRENT_SESSION BYTE(8))
     RETURNS BYTE(8)
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     EXTERNAL NAME 'CS!insert_category!c:\cctests\insert_category.c';

The update_category function updates a row using the category value for the session (as determined from the CURRENT_SESSION parameter) and the category value from the target row (as determined from the INPUT_ROW parameter) as input. Because both the CURRENT_SESSION parameter and the INPUT_ROW parameter are defined with the BYTE(8) data type, the row to be updated concerns a non-hierarchical compartment constraint.

The associated external function for this UDF returns the category to be assigned to the updated row.

     CREATE FUNCTION SYSLIB.UpdateCategory (
       CURRENT_SESSION BYTE(8), 
       INPUT_ROW       BYTE(8))
     RETURNS BYTE(8)
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     EXTERNAL NAME 'CS!updatecategory!c:\cctests\updatecategory.c';

The function to delete a row has as input the category value from the target row (as determined from the INPUT_ROW parameter) and returns either the character T to indicate that the specified deletion can be done or the letter F to indicate that the specified deletion cannot be done.

     CREATE FUNCTION SYSLIB.DeleteCategory (
       INPUT_ROW BYTE(8))
     RETURNS CHARACTER
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     EXTERNAL NAME 'CS!deletecategory!c:\cctests\deletecategory.c';

The function to select a row has as input the category value from the session (as determined from the CURRENT_SESSION parameter) and the category value from the target row (as determined from the INPUT_ROW parameter) and has as output the character T to indicate that the select can be done or the letter F to indicate it cannot be done.

     CREATE FUNCTION SYSLIB.ReadCategory (
       CURRENT_SESSION BYTE(8), 
       INPUT_ROW       BYTE(8))
     RETURNS CHARACTER
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     EXTERNAL NAME 'CS!readcategory!c:\cctests\readcategory.c';