16.10 - Creating Row Level Security UDFs - Teradata Database

Teradata Database Security Administration

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Administration
Security
featnum
B035-1100-161K

Coding a set of UDFs for a security constraint must be coordinated with the purpose and structure of the corresponding CONSTRAINT object. See Working with Security Constraints.

  1. Code and file each security constraint UDF as shown in SQL External Routine Programming.
    You can create more than one UDF for an SQL operation type, but you can only specify one UDF of each type in a security CONSTRAINT object.
  2. Use the CREATE FUNCTION statement to define a FUNCTION object for each UDF, for example:
    CREATE FUNCTION SYSLIB.function_name 	
    [parameter_name   data_type  ...(, parameter_name   data_type)]
        Returns  data_type 
        LANGUAGE C
        NO SQL
        PARAMETER STYLE  style 
        EXTERNAL NAME  udf_path_name  ; 

    where:

    Syntax Element Description
    SYSLIB.function_name SYSLIB is the database that contains the UDF

    function_name identifies the UDF. For example, in the function named UpdateLevel:

    • Update defines the SQL operation that the UDF constrains.
    • Level indicates that the UDF is constructed to enforce hierarchical (level) constraints.
    parameter_name Each UDF type requires one or more parameter names to define the source of the required UDF input parameters.

    Valid parameter names are:

    • current_session: the session security label
    • input_row: the security label for the row being accessed
    The NULL option in the security CONSTRAINT object that references a UDF determines whether the input_row value can be null.

    Parameter name requirements for SQL operations:

    • An INSERT UDF requires current_session.

      The UDF uses the security label of the current session as the constraint column value for a newly inserted row.

    • A SELECT or UPDATE UDF requires both current_session and input_row.

      The UDF compares the session and row security labels to determine whether the SQL operation is allowed. If it is allowed, the UDF uses the session label as the constraint value for the row.

    • A DELETE UDF requires input_row.

      The UDF checks the row security label to determine whether to allow the row to be deleted.

    data_type The data type for an input parameter, either current_session, or input_row, which must match the data type for the CONSTRAINT object that specifies the UDF.

    Valid data types:

    • Smallint – hierarchical constraint
    • Byte(n) – non-hierarchical constraint

    For data type requirements, see Creating CONSTRAINT Objects.

    Returns data_type
    • A INSERT or UPDATE UDF returns the constraint value for the row, so the return data type must be the same as the data type for the CONSTRAINT object that specifies the UDF.
    • An SELECT or DELETE UDF returns either T or F, indicating the success or failure of the operation, so the return data type must be CHAR.
    LANGUAGE C The programming language in which the UDF is written.

    Security constraint UDFs must be C coded scalar functions.

    NO SQL The UDF cannot execute SQL statements or read/modify SQL data in the database (required).
    PARAMETER STYLE style If the CONSTRAINT object definition:
    • Does not allow nulls in constraint columns, the style must be TD_GENERAL.
    • Allows nulls in constraint columns, the style must be SQL.
    udf_path_name The UDF path name. For example:'CS!updatelevel!c:\cctests\updatelevel.c'
  3. To avoid performance problems that occur when running the UDF in protected mode, use the ALTER FUNCTION statement to set non-protected mode execution for the function:
    ALTER FUNCTION SYSLIB.function_name  EXECUTE NOT PROTECTED
If a UDF fails to operate correctly in non-protected mode, it can cause a database restart. Be sure to test each UDF thoroughly before deploying it.