Creating Row Level Security UDFs - Advanced SQL Engine - Teradata Database

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 Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
   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