General Rules for Creating a Row-Level Security UDF - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

You use CREATE FUNCTION requests to generate the functions that Vantage executes to enforce the security policy for a data row. You can create a function for each of the four statement-action types that can be executed on a table defined with the full set of security constraints. See Teradata Vantage™ - SQL External Routine Programming, B035-1147 for information about how to code row-level security UDFs.

The following rules apply to creating UDFs to enforce row-level security:
  • You can only code the external routines for row-level security functions using C and C++.

    Java is not supported for external routines written to enforce row-level security.

  • Only scalar type UDFs can be used as row-level security functions.
  • Name overloading is not supported for row-level security functions.

    Because of the parameter types required by the server for input to the UDF function, the same number of parameters and their data types are required for each UDF that executes the security policy for a specific statement-action.

    The only difference there can be between the parameters for UDFs that execute a security policy is that the parameters of different UDFs can include or omit null indicator variables, depending on whether the constraint allows nulls.

    No other values are acceptable for an indicator variable parameter.

    A parameter style of TD_GENERAL for a row-level security UDF specifies that there are no indicator variable parameters for the UDF parameters.

  • Unlike the case for other UDFs, input parameter names for a row-level security UDF are mandatory because they define the source for those parameters.

    A security constraint policy UDF cannot be called by a user from an SQL request. Instead, Vantage automatically calls row-level security functions whenever the corresponding statement-action type is executed for a table on which the constraint UDF has been defined. Because Vantage automatically generates the input parameter values for the UDF, it must know the source the user desires.

    Two system-defined parameter names exist for row-level security functions.

  • The valid system-defined input parameters for each UDF depend on the statement-action for the UDF as defined in the constraint object.
    This parameter name … Defines the source of the parameter as …
    CURRENT_SESSION the value that is currently set for the session for the constraint to which the UDF applies.
    INPUT_ROW being in the corresponding constraint column of the row that is the object of the request.

    In these cases, the system-defined parameter names indicate that the input provided by Vantage to a security policy UDF are the constraint values from the source defined in the following table:

    Statement Required Input Parameters Statement-Action Result
    DELETE INPUT_ROW An indication of whether the session passed the security policy test.
    INSERT CURRENT_SESSION A value that is placed in the target row.
    An indication of whether the session passed the security policy test.

    If the result is that the policy test did not pass, Vantage discontinues processing of the action, moves to the next row, and does not generate an audit row.

    A value that is placed in the target row.
  • If you do not create a constraint function for a statement-action type, that statement-action can only be executed by a user who has the OVERRIDE privilege required to execute the request.

    Either the request must also include the values to be assigned to the constraint columns of the target rows or you must select the rows to be assigned to the target table from the source table.

    See UDF Parameter Styles for more information about function parameter styles.