User Defined Functions and APIs

• 1: Teradata UDFs
• 1.1: General UDFs
• 1.2: Access Check UDFs
• 1.3: Varchar Latin UDFs
• 1.4: Varchar Unicode UDFs
• 1.5: Float UDFs
• 1.6: Small Integer UDFs
• 1.7: Integer UDFs
• 1.8: Big Integer UDFs
• 1.9: Date UDFs
• 1.10: 8-Byte and 16-Byte Decimal UDFs
• 1.11: JSON UDFs
• 1.12: XML UDFs
• 1.13: Float UDFs for No Encryption
• 1.14: Date UDFs for No Encryption
• 1.15: 8-Byte AND 16-Byte Decimal UDFs for No Encryption
• 2: Trino User Defined Functions and Procedures
• 2.1: General UDFs
• 2.2: VarChar UDFs
• 2.3: BigInt UDFs
• 2.4: SmallInt UDFs
• 2.5: Integer UDFs
• 2.6: Date UDFs
• 2.7: DateTime UDFs
• 2.8: VarChar Encryption UDFs
• 2.9: Unicode UDFs
• 2.10: Decimal UDFs
• 2.11: Double UDFs
• 2.12: VarBinary Encryption UDFs

1 - Teradata UDFs

This page provides a detailed list of User Defined Functions (UDFs) for general information, protection, and unprotection of data with different data types.

It is recommended to run the sample queries in BTEQ (Basic Teradata Query). For more information, refer to Sample Scripts provided in the Teradata Data Warehouse Protector package at the default location, /opt/protegrity/databaseprotector/teradata/sqlscripts/.

Protegrity UDFs can support the JSON format for protection and unprotection. It is not possible to mask data stored in XML or JSON (JavaScript Object Notation) formats. While executing the Unprotect UDFs for these formats, clear data is returned with an error message. Masking is supported only with the Varchar UDFs.

Teradata UDFs for Protection

This section provides a detailed list of User Defined Functions (UDFs) for general information, and protection, unprotection, and tokenization of data with different data types.

Teradata UDFs - Deterministic and Non-deterministic clauses

Teradata supports the following two optional clauses to categorize if the UDF returns identical results for identical inputs or not.

• DETERMINISTIC - specifies that the UDF function returns the same results for identical inputs. The de-tokenization and decryption UDFs are defined with the DETERMINISTIC clause.
• NOT DETERMINISTIC - specifies that the UDF function returns non-identical results for identical inputs. This is the default option. The tokenization and encryption UDFs are defined with the NOT DETERMINISTIC clause.

Risk

In case of a query with constant arguments to the DETERMINISTIC UDF call, Teradata may cache the result of the evaluated UDF, as designed. During subsequent query execution, the results may be fetched from the Teradata internal cache without evaluating the UDF.

This is a risk because it can cause unauthorized access to the protected data due to lack of authorization check during the UDF execution. In addition, altering the clause to NOT DETERMINISTIC may cause performance issues as the UDFs defined with the DETERMINISTIC clause execute faster in comparison to the UDFs defined with the NOT DETERMINISTIC clause.

As per usage, if you are not using any constants in the UDF call, then you can recreate the UDF with the DETERMINISTIC clause to ensure faster performance.

Important: For all the Teradata UDFs, the communicationid and scid parameters are no longer used and are retained for compatibility purposes only. It is recommended to set the values for these parameters as zero.

• General UDFs
• Access Check UDFs
• Varchar Latin UDFs
• Varchar Unicode UDF
• Float UDFs
• Small Integer UDFs
• Integer UDFs
• Big Integer UDFs
• Date UDFs
• 8-Byte AND 16-Byte Decimal UDFs
• JSON UDFs
• XML UDFs

Teradata UDFs for No Encryption

This section provides a list of User Defined Functions (UDFs) that can be used with No Encryption data elements.

• Float UDFs for No Encryption
• Date UDFs for No Encryption
• 8-Byte and 16-Byte Decimal UDFs for No Encryption

1.1 - General UDFs

This section includes the general UDFs that can be used to retrieve the Teradata Protector version and the current user.

pty_whoami

This UDF returns the name of the user who is currently logged in.

Signature:

pty_whoami()

Parameters:
None

Returns:
The function returns the name of user logged in to the database.

Example:

select pty_whoami();

pty_getversion

This UDF returns the version of the installed Teradata Data Warehouse Protector.

Signature:

pty_getversion()

Parameters:
None

Returns:
The function returns the version of the product as a string

Example:

select pty_getversion();

pty_getdbsinfo

This UDF returns the Teradata session, statement, and request numbers. These parameters are captured in audit logs and can be cross-referenced in the ESA Forensics View.

Signature:

pty_getdbsinfo

Parameters:
None

Returns:
The function returns the following parameters in a string.

Example:

select pty_getdbsinfo();

1.2 - Access Check UDFs

This section includes list of UDFs that can be used to check select access-related information.

pty_checkselaccess

This UDF checks whether a database user has unprotect access for a set of data elements. To run this UDF, the database user should be granted access rights for protection.

Signature:

pty_checkselaccess(dataelement<n> VARCHAR, resultlen INTEGER, communicationid INTEGER)

Parameters:

Returns:

The function returns a 3-CHARACTER string.

• Position 1: Value 1 indicates select permissions on dataelement1, value 0 indicates no select permissions
• Position 2: Value 1 indicates select permissions on dataelement2, value 0 indicates no select permissions
• Position 3: Value 1 indicates select permissions on dataelement3, value 0 indicates no select permissions

Exception:
None

Example:

select pty_checkselaccess('AES256', 'AES128', 'AES128_IV_CRC_KID', 3, 0);

1.3 - Varchar Latin UDFs

The Varchar Latin UDFs accept the string data encoded in the Latin character set.

Important: Do not exceed the maximum output buffer length when using the result length parameter (resultlen) in the Varchar Latin UDFs.
For more information about the maximum output buffer length, for each Varchar Latin UDF, refer to Installing the Teradata Objects.

pty_varcharlatinenc

This UDF protects the string data using an Encryption data element.

Signature:

pty_varcharlatinenc(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_varcharlatinenc ('Any character value! ', 'AES256',500,0,0);

pty_varcharlatindec

This UDF unprotects the protected string data.

Signature:

pty_varcharlatindec(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_varcharlatindec(pty_varcharlatinenc('Any character value! ', 'dataelement',500,0,0 ), 'dataelement',500,0,0 );

pty_varcharlatindecex

This UDF unprotects the protected string data.

Signature:

pty_varcharlatindecex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns an error instead of NULL, if the user does not have access rights.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_varcharlatindecex(PTY_VARCHARLATINENC('ProtegrityProt', 'AES256',100,0,0 ), 'AES256',100,0,0 );

pty_varcharlatinins

This UDF protects the string data using type-preserving data elements, such as, tokens, and No Encryption for access control.

Signature:

pty_varcharlatinins(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the protected VARCHAR value.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharlatinins('Any character value! ', 'dataelement',500,0,0 );

• Email Tokenization:
  This UDF can be used to tokenize email input type.
  In the following example, email is a token element created in the ESA of email type.

  pty_varcharlatinins('email@protegrity.com','email',32,0,0);
  

• Timestamp Tokenization:
  This UDF can be used to tokenize timestamp data.
  The following example displays a sample of timestamp tokenization:

  select pty_varcharlatinins(cast('22-09-1990' as varchar(32)),'alphanum',64,0,0);
  

pty_varcharlatinsel

This UDF unprotects the protected string data.

Signature:

pty_varcharlatinsel(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to data.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Note: If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small 

Example:

SELECT pty_varcharlatinsel(pty_varcharlatinins('Any character value! ', 'dataelement',500,0,0 ), 'dataelement',500,0,0 );

• Email De-tokenization:
  This UDF can be used to de-tokenize email input type tokenized using the PTY_VARCHARLATININS UDF.
  In the following example, email is a token element created in the ESA of email type.

  pty_varcharlatinsel('F00CJ@protegrity.com','email',32,0,0);
  

• Timestamp Data De-tokenization:
  This UDF can be used to de-tokenize timestamp data tokenized using the PTY_VARCHARLATININS UDF. The following example displays a sample of timestamp data de-tokenization.

  sel cast(pty_varcharlatinsel(pty_varcharlatinins(cast('2019-04-14 08:30:41-04:00' as varchar(64)),'TE_N_S16_L3R1_ASTYES',64,0,0),'TE_N_S16_L3R1_ASTYES',64,0,0) AS TIMESTAMP(0));
  

pty_varcharlatinselex

This UDF unprotects the protected string data.

Signature:

pty_varcharlatinselex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Note: If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

 Input or output buffer is too small 

.

Example:

SELECT pty_varcharlatinselex(pty_varcharlatinins('Any character value! ', 'dataelement',500,0,0 ), 'dataelement',500,0,0 );

pty_varcharlatinhash

This UDF calculates the hash value of a string data.

Attention: This is a one-way function and you cannot unprotect the data.

Signature:

pty_varcharlatinhash(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the hash value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Caution: Starting from the version 10.0.x, the HMAC-SHA1 protection method is deprecated.
It is recommended to use the HMAC-SHA256 protection method instead of the HMAC-SHA1 protection method.
For assistance in switching to a different protection method, contact Protegrity Support.

Example:

SELECT pty_varcharlatinhash ('ProtegrityProt', 'HMAC_SHA256', 100,0,0);

1.4 - Varchar Unicode UDFs

The Varchar Unicode UDFs accept the string data encoded in the UNICODE character set.

Important: Do not exceed the maximum output buffer length when using the result length parameter (resultlen) in the Varchar Unicode UDFs.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

pty_varcharunicodeenc

This UDF protects the Unicode string using an Encryption data element for encryption.

Signature:

pty_varcharunicodeenc(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodeenc (TRANSLATE(CAST('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'AES_128',100,0,0 );

pty_varcharunicodedec

This UDF unprotects the protected Unicode string data.

Signature:

pty_varcharunicodedec(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected Unicode character value.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodedec(pty_varcharunicodeenc(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE, 'AES256',100,0,0), 'AES256',100,0,0 ));

pty_varcharunicodedecex

This UDF unprotects the protected Unicode string data.

Signature:

pty_varcharunicodedecex(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodedecex(pty_varcharunicodeenc(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'AES256', 100, 0,0), 'AES256', 100, 0,0);

pty_varcharunicodeins

This UDF protects Unicode string data using type-preserving data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Signature:

pty_varcharunicodeins(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Note: For pty_varcharunicodeins, set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:
The function returns the protected VARCHAR value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example for Unicode Gen2:

The Unicode Gen2 data elements supports the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer.
For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

SELECT pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0);

SELECT pty_varcharunicodeins(TRANSLATE(CAST ('ϠϡϢϣϥϦ' AS VARCHAR(1000)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0);

pty_varcharunicodesel

This UDF unprotects Unicode string data protected by data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Warning: This UDF does not support masking.

Signature:

pty_varcharunicodesel(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

For pty_varcharunicodesel, you must set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:

• The function returns an unprotected character value.
• The function returns a protected value if this option is configured in the policy and the user does not have access to data.
• The function returns NULL when the user has no access to data in the policy.

Exception:

• If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small

Example for Unicode Gen2:

The Unicode Gen2 data elements support the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer.
For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

select pty_varcharunicodesel(pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE),'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0),'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0);

select pty_varcharunicodesel(pty_varcharunicodeins(TRANSLATE(CAST ('ϠϡϢϣϥϦ' AS VARCHAR(1000)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0);

pty_varcharunicodeselex

This UDF unprotects Unicode string data protected by data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Warning: This UDF does not support masking.

Signature:

pty_varcharunicodeselex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

For pty_varcharunicodeselex, set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small

.

Example:

select pty_varcharunicodeselex(pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'NoEncryption', 100, 0,0), 'NoEncryption', 100, 0,0);

1.5 - Float UDFs

pty_floatenc

This UDF protects the float value using an Encryption data element.

Signature:

pty_floatenc(col FLOAT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatenc(26656.0, 'AES256', 100, 0,0); 

pty_floatdec

This UDF unprotects the protected float value.

Signature:

pty_floatdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected FLOAT value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatdec(pty_floatenc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_floatdecex

This UDF unprotects the protected float value.

Signature:

pty_floatdecex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected FLOAT value.
• The function returns an error instead of NULL if the user does not have access

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatdecex(pty_floatenc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_floathash

This UDF calculates the hash value for a float value.

Attention: This is a one-way function and you cannot unprotect the data.

Signature:

pty_floathash(col FLOAT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the hash value.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Caution: Starting from the version 10.0.x, the HMAC-SHA1 protection method is deprecated.
It is recommended to use the HMAC-SHA256 protection method instead of the HMAC-SHA1 protection method.
For assistance in switching to a different protection method, contact Protegrity Support.

Example:

select pty_floathash(26656.0, 'HMAC_SHA256', 100, 0,0);

1.6 - Small Integer UDFs

pty_smallintenc

This UDF protects the small integer value using an Encryption data element.

Signature:

pty_smallintenc(col SMALLINT, dataelement VARCHAR, resultlen INTEGER, communicationidINTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintenc(12345,'AES256',100,0,0);

pty_smallintdec

This UDF unprotects the small integer value.

Signature:

pty_smallintdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected SMALLINT value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintdec(pty_smallintenc(12345,'AES256',100,0,0),'AES256',0,0);

pty_smallintdecex

This UDF unprotects the protected small integer value.

Signature:

pty_smallintdecex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected SMALLINT value.
• The function returns an error instead of NULL if the user does not have access

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintdecex(pty_smallintenc(12345,'AES256',100,0,0),'AES256',0,0);

pty_smallintins

This UDF protects the small integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_smallintins(col SMALLINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected SMALLINT value.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintins(12345, 'TE_INT_2', 100, 0,0);

pty_smallintsel

This UDF unprotects the small integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_smallintsel(col SMALLINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected SMALLINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintsel(pty_smallintins(12345, 'TE_INT_2', 100, 0,0), 'TE_INT_2',0,0);

pty_smallintselex

This UDF unprotects the protected small integer value.

Signature:

pty_smallintselex(col SMALLINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the SMALLINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_smallintselex(pty_smallintins(12345, 'TE_INT_2', 100, 0,0), 'TE_INT_2',0,0);

pty_smallinthash

This UDF calculates the hash value for a SMALLINT value. This is a one-way function and you cannot unprotect the data.

Signature:

pty_smallinthash(col SMALLINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the hash value.

Exception:
If you configure an exception in the policy and the user does not have access rights, then the UDF terminates with an error message explaining what went wrong.

Example:

select PTY_SMALLINTHASH(1234, 'HMAC_SHA256', 100, 0,0);

1.7 - Integer UDFs

pty_integerenc

This UDF protects integer value using an Encryption data element.

Signature:

pty_integerenc(col INTEGER, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerenc(1234, 'AES256', 100, 0,0);

pty_integerdec

This UDF unprotects the protected integer value.

Signature:

pty_integerdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected INTEGER value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerdec(pty_integerenc(1234, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_integerdecex

This UDF unprotects the protected integer value.

Signature:

pty_integerdecex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected INTEGER value.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_integerdecex(pty_integerenc(1234, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_integerins

This UDF protects the integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_integerins(col INTEGER, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected INTEGER value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerins(1234, 'TE_INT_4', 100, 0,0);

pty_integersel

This UDF unprotects the protected integer value.

Signature:

pty_integersel(col INTEGER, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected INTEGER value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integersel(pty_integerins(1234, 'TE_INT_4', 100, 0,0), 'TE_INT_4', 0,0);

pty_integerselex

This UDF unprotects the protected integer value.

Signature:

pty_integerselex(col INTEGER, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected INTEGER value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If you configure an exception in the policy and the user does not have the access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_integerselex(pty_integerins(1234, 'TE_INT_4', 100, 0,0), 'TE_INT_4', 0,0);

pty_integerhash

This UDF calculates the hash value for integer value.

Attention: This is a one-way function and you cannot unprotect the data.

Signature:

pty_integerhash(col INTEGER, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the hash value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_integerhash(1234, 'HMAC_SHA256', 100, 0,0);

Caution: Starting from the version 10.0.x, the HMAC-SHA1 protection method is deprecated.
It is recommended to use the HMAC-SHA256 protection method instead of the HMAC-SHA1 protection method.
For assistance in switching to a different protection method, contact Protegrity Support.

1.8 - Big Integer UDFs

pty_bigintenc

This UDF protects the Big Integer value using a data element for encryption.

Signature:

pty_bigintenc(col BIGINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

The function returns the protected VARBYTE value.

Exception:

If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintenc(12345678,'AES256',100,0,0);

pty_bigintdec

This UDF unprotects the Big Integer value.

Signature:

select pty_bigintdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected BIGINT value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintdec(pty_bigintenc(12345678,'AES256',100,0,0),'AES256',0,0);

pty_bigintdecex

This UDF unprotects the protected Big Integer value.

Signature:

pty_bigintdec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected BIGINT value.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_bigintdec(pty_bigintenc(12345678,'AES256',100,0,0),'AES256',0,0);

pty_bigintins

This UDF protects the Big Integer value using type-preserving data elements, such as, tokens and No Encryption for access control.

Signature:

pty_bigintins(col BIGINT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected BIGINT value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintins(12345678, 'TE_INT_8', 100, 0,0);

pty_bigintsel

This UDF unprotects the Big Integer value.

Signature:

pty_bigintsel(col BIGINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected BIGINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintsel(pty_bigintins(12345678, 'TE_INT_8', 100, 0,0), 'TE_INT_8',0,0);

pty_bigintselex

This UDF unprotects the protected Big Integer value and returns an error instead of NULL if user does not have access.

Signature:

pty_bigintselex(col BIGINT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected BIGINT value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_bigintselex(PTY_BIGINTINS(12345678, 'TE_INT_8', 100, 0,0), 'TE_INT_8',0,0);

1.9 - Date UDFs

The dates can be protected using encryption and tokenization as the data protection method. The native UDFs, such as, pty_dateenc and pty_datedec, can be used for encryption and decryption respectively. To tokenize the date formats using the date data element, the data must be cast to VARCHAR type and then protected/unprotected with PTY_VARCHARLATININS/PTY_VARCHARLATINSEL UDFs.

To avoid any performance issues resulting due to casting of the data, a general best practice is to protect the data and present the decryption-related UDFs in the tables as views to authorized users only. This eliminates the unauthorized user’s access to the decryption UDFs and has the protected data only. The decryption process is limited to authorized users and thus, doesn’t cause any performance impact as the UDFs are executed restrictively.

pty_dateenc

This UDF protects the date value using an Encrytion data element.

Signature:

pty_dateenc(col DATE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_dateenc('1990-11-22', 'AES256', 100, 0,0);

pty_datedec

This UDF unprotects the protected date value.

Signature:

pty_datedec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected DATE value.

  The function returns the output as per the system date format.

• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF will terminate with an error message explaining what went wrong.

Example:

select pty_datedec(pty_dateenc('1990-10-22', 'AES256', 100, 0,0), 'AES256', 0,0);

pty_datedecex

This UDF unprotects the protected date value and returns an error instead of NULL if the user does not have access.

Signature:

pty_datedecexex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected DATE value.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_datedecex(pty_dateenc(CAST ('22 Sep 90' AS DATE FORMAT 'DD-MMM-YY'), 'AES256', 100, 0,0), 'AES256', 0,0);

1.10 - 8-Byte and 16-Byte Decimal UDFs

These UDFs work on the Decimal data types that are either 8 or 16 bytes in size. The 8-byte Decimal data types have a precision between 10 and 18 digits, while the 16-byte Decimals have a precision between 19 and 38 digits.

Note: Only one set of Decimal UDFs can be created for each range. The user must provide the UDF name. It is recommended that you replace with, for example, 10_2 if the target data type is Decimal(10,2) to get a function pty_decimal_10_2enc, or 22_3 if the target data type is Decimal(22,3) to get pty_decimal_22_3enc.

pty_decimalenc

This UDF protects the decimal value with a data element for encryption.

Signature:

pty_decimal<n>enc(col DECIMAL<n>, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1enc(26656.0, 'AES256', 100, 0,0);

pty_decimaldec

This UDF unprotects the protected decimal value.

Signature:

pty_decimal<n>dec(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected DECIMAL value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1dec(pty_decimal37_1enc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

pty_decimaldecex

This UDF unprotects the protected decimal value and returns an error instead of NULL if the user does not have access.

Signature:

pty_decimal<n>decex(col VARBYTE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected DECIMAL value.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1decex(pty_decimal37_1enc(26656.0, 'AES256', 100, 0,0), 'AES256', 0,0);

1.11 - JSON UDFs

These UDFs are used to protect and unprotect data for JSON data type. These UDFs have been introduced to support LOB or Large Objects that can be loaded to or extracted from the Teradata Database tables. Depending on the data element chosen, the data is tokenized or encrypted. The data in JSON are protected as CLOBs.

The examples provided for protection and unprotection are for single queries.

pty_jsonins

This UDF protects the JSON value using the type-preserving data elements, such as, token and No Encryption data element for access control.

Signature:

pty_jsonins(col JSON, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected JSON CLOB (Character Large Objects) value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Note: Tokenizing a JSON format data with a Printable tokenization data element will not return a valid JSON format output.

Example:

SELECT pty_jsonins(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'TE_A_N_S23_L2R2_Y', 500, 0, 0);

pty_jsonsel

This UDF unprotects the protected JSON CLOBs.

Signature:

pty_jsonsel(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected JSON values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_jsonsel(pty_jsonins(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'TE_A_N_S23_L2R2_Y', 500, 0, 0), 'TE_A_N_S23_L2R2_Y', 500, 0, 0);

pty_jsonselex

This UDF unprotects the JSON CLOBs that are protected using a tokenization data element.

Signature:

pty_jsonselex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected JSON values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error explaining what went wrong.

Example:

SELECT pty_jsonselex(pty_jsonins(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'TE_A_N_S23_L2R2_Y', 500, 0, 0), 'TE_A_N_S23_L2R2_Y', 500, 0, 0);

pty_jsonenc

This UDF protects the JSON value using an encrytion data element.

Signature:

pty_jsonenc(col JSON, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected JSON CLOB (Character Large Objects) value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_jsonenc(pty_jsonenc(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'AES256', 500, 0, 0), 'AES256', 500, 0, 0);

pty_jsondec

This UDF unprotects the CLOB value that are protected using an encryption data element.

Signature:

pty_jsondec(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected JSON values.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_jsondec(pty_jsonenc(NEW JSON('{"emp_name" : "John Doe", "emp_address" : "Stamford 1"}'), 'AES256', 500, 0, 0), 'AES256', 500, 0, 0);

1.12 - XML UDFs

These UDFs support the XML data type. The XML content is stored in compact binary form or CLOBs that preserve the information set of the XML document. These UDFs have been introduced to support the XML files that can be loaded to or extracted from the Teradata Database tables. Depending on the data element chosen, the data is either tokenized or encrypted.

pty_xmlins

This UDF protects the XML value using type-preserving data elements, such as, token and No Encryption for access control.

Signature:

pty_xmlins(col XML, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected CLOB value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Caution: Tokenizing XML data with Printable tokenization does not return a valid XML format output.

Example:

select pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0) "Protected Data";

pty_xmlsel

This UDF unprotects the protected CLOB value.

Signature:

pty_xmlsel(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected XML values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

sel
pty_xmlsel( 
pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0),'TE_A_N_S23_L2R2_Y',1500,0,0) "UnProtected Data";

pty_xmlselex

This UDF unprotects the protected CLOB value with strong encryption.

Signature:

pty_xmlselex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected XML values.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

sel
pty_xmlselex( 
pty_xmlins(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'TE_A_N_S23_L2R2_Y',1500,0,0),'TE_A_N_S23_L2R2_Y',1500,0,0) "UnProtected Data";

pty_xmlenc

This UDF protects the XML data using an Encryption data element.

Signature:

pty_xmlenc(col XML, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected CLOB value.

Exception:
If the user does not have protect access rights in the policy, UDF terminates with an error message explaining what went wrong.

Example:

sel 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0) "Protected Data";

pty_xmldec

This UDF unprotects the protected CLOB values.

Signature:

pty_xmldec(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the unprotected XML value.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select
pty_xmldec( 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0),'AES256',1500,0,0) "UnProtected Data";

pty_xmldecex

This UDF unprotects the protected CLOB value with strong encryption.

Signature:

pty_xmldecex(col CLOB, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the unprotected XML value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select
pty_xmldecex( 
pty_xmlenc(CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA - 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'),'AES256',1500,0,0),'AES256',1500,0,0) "UnProtected Data";

1.13 - Float UDFs for No Encryption

pty_floatins

This UDF can be used only with the No Encryption data element.

Signature:

pty_floatins(col FLOAT, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the input value as it is.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatins(26656.0, 'NoEncryption', 100, 0,0);

pty_floatsel

This UDF unprotects the float value for a No Encryption data element.

Signature:

pty_floatsel(col FLOAT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as it is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatsel(pty_floatins(26656.0, 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

pty_floatselex

This UDF unprotects the float value protected with a No Encryption data element.

Signature:

pty_floatselex(col FLOAT, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as it is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If the user does not have access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_floatselex(pty_floatins(26656.0, 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

1.14 - Date UDFs for No Encryption

This section provides DATE UDFs that are applicable for No Encryption data elements.

pty_dateins

This UDF protects a date value with a No Encryption data element to impose access control.

Signature:

pty_dateins(col DATE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the input value as is.

The function returns the output as per the system date format.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_dateins(CAST ('22-09-1990' AS DATE FORMAT 'DD-MM-YYYY'), 'NoEncryption', 100, 0,0);

pty_datesel

This UDF unprotects the date value that is protected using a No Encryption data element.

Signature:

pty_datesel(col DATE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_datesel(pty_dateins(CAST ('22-09-1990' AS DATE FORMAT 'DD-MM-YYYY'), 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

pty_dateselex

This UDF unprotects the date value that is protected with a No Encryption data element and returns an error instead of NULL if the user does not have access.

Signature:

pty_dateselex(col DATE, dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message.

Example:

select pty_dateselex(pty_dateins(CAST ('22-09-1990' AS DATE FORMAT 'DD-MM-YYYY'), 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

1.15 - 8-Byte AND 16-Byte Decimal UDFs for No Encryption

These UDFs work on the Decimal data types that are either 8 or 16 bytes in size. The 8-byte Decimals have a precision between 10 and 18 digits, while the 16-byte Decimals have a precision between 19 and 38 digits. These UDFs apply to the No Encryption data elements only.

pty_decimalins

This UDF protects the decimal value using a No Encryption data element.

Signature:

pty_decimal<n>ins(col DECIMAL<M,N>, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the input value as is.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1ins(26656.0, 'NoEncryption', 100, 0,0);

pty_decimalsel

This UDF unprotects the decimal value that is protected using a No Encryption data element.

Signature:

pty_decimal<n>sel(col DECIMAL<M,N>, dataelement VARCHAR, communicationid INTEGER, SCID INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns NULL when the user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1sel(pty_decimal37_1ins(26656.0, 'NoEncryption', 100, 0,0), 'NoEncryption', 0,0);

pty_decimalselex

This UDF unprotects the decimal value that is protected using a No Encryption data element.

Signature:

pty_decimal<n>selex(col DECIMAL(m,n), dataelement VARCHAR, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns the input value as is.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

select pty_decimal37_1selex(pty_decimal37_1ins(26656.0, 'NoEncryption', 100, 0,0),'NoEncryption', 0,0);

2 - Trino User Defined Functions and Procedures

This section provides a detailed list of User Defined Functions (UDFs) for general information, and protection and unprotection of different data types.

2.1 - General UDFs

This section includes list of general UDFs that can be used to retrieve the Trino Protector version and the current user.

ptyWhoAmI()

This function returns the name of the user.

Signature:

ptyWhoAmI()

Parameters:
None

Returns:
This UDF returns the name of the user logged in to the database as VARCHAR.

Example:

SELECT ptyWhoAmI();

ptyGetVersion()

This UDF returns the JpepLite version used in Trino UDFs.

Signature:

ptyGetVersion()

Parameters:
None

Returns:
This UDF returns the JpepLite version used in Trino UDFs.

Example:

select ptyGetVersion();

ptyGetVersionExtended()

The UDF returns the extended version information.

Signature:

pty_getversionextended();

Parameters:

• None

Returns:
The UDF returns a string in the following format:

JpepLite: <1>; CORE: <2>;

where,

• 1. Is the JpepLite version
• 2. Is the Core library version

Example:

select pty_getversionextended();

2.2 - VarChar UDFs

This section provides a list of Varchar UDFs for the protect, unprotect, and reprotect operations.

Consider a Trino session where you impersonate a user using the –user parameter as shown in the following example.

./TrinoCLI --server localhost:8080 --catalog hive --schema default --user=<sample_user>

If you execute any UDF after impersonating a user, then the query execution happens for the impersonated user <sample_user>. This is a limitation of Trino.

ptyProtectStr()

This UDF protects the varchar values.

Signature:

ptyProtectStr(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected varchar value.

Example:

select ptyProtectStr('ProtegrityProt','Varchar_DE');

Supported Protection Methods:

ptyUnprotectStr()

This UDF unprotects the existing protected varchar value.

Signature:

ptyUnprotectStr(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected varchar value.

Example:

select ptyUnProtectStr(PtyProtectStr('ProtegrityProt','Varchar_DE'),'Varchar_DE');

Supported Protection Methods:

ptyReprotect() - Str

This UDF reprotects the varchar protected data, which was earlier protected using the ptyProtectStr UDF, with a different data element.

Signature:

ptyReprotect(varchar input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected varchar value.

Example:

select ptyReprotect(PtyProtectStr('ProtegrityProt','Varchar_DE'),'Varchar_DE','new_Varchar_DE');

Supported Protection Methods:

2.3 - BigInt UDFs

This section provides a list of the BigInt UDFs for the protect, unprotect, and reprotect operations.

ptyProtectBigInt()

This UDF protects the BigInt value.

Signature:

ptyProtectBigInt(bigint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected BigInt value.

Example:

select PtyProtectBigInt(1234567, 'BigInt_DE');

Supported Protection Methods:

ptyUnprotectBigInt()

This UDF unprotects the protected BigInt value.

Signature:

ptyUnProtectBigInt(bigint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected BigInt value.

Example:

select PtyUnProtectBigInt(PtyProtectBigInt(1234567, 'BigInt_DE'), 'BigInt_DE');

Supported Protection Methods:

ptyReprotect() - BigInt

This UDF reprotects the BigInt format protected data with a different data element.

If you are using numeric data with the ptyReprotect() UDF for protection, then ensure to cast the data to BigInt before using the UDF.

Signature:

ptyReprotect(bigint input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected BigInt value.

Example:

select ptyReprotect(PtyProtectBigInt(123456, 'BigInt_DE'),'BigInt_DE','new_BigInt_DE');

Supported Protection Methods:

2.4 - SmallInt UDFs

This section provides a list of the SmallInt UDFs for the protect, unprotect, and reprotect operations.

ptyProtectSmallInt()

This UDF protects the SmallInt value.

Signature:

ptyProtectSmallInt(smallint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected SmallInt value.

Example:

select ptyProtectSmallInt(cast(12 as smallint), 'SmallInt_DE');

Supported Protection Methods:

ptyUnprotectSmallInt()

This UDF unprotects the protected SmallInt value.

Signature:

ptyUnprotectSmallInt(smallint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected SmallInt value.

Example:

select PtyUnprotectSmallInt(PtyProtectSmallInt(cast(12 as smallint), 'SmallInt_DE'), 'SmallInt_DE');

Supported Protection Methods:

ptyReprotect() - SmallInt

This UDF reprotects the SmallInt format protected data, which was earlier protected using the ptyProtectSmallInt UDF, with a different data element.

Signature:

ptyReprotect (SmallInt input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected SmallInt value.

Example:

select ptyReprotect(PtyProtectSmallInt(cast(12 as smallint), 'SmallInt_DE'),'SmallInt_DE','new_SmallInt_DE');

Supported Protection Methods:

2.5 - Integer UDFs

This section provides a list of the integer UDFs for the protect, unprotect, and reprotect operations.

ptyProtectInt()

This UDF protects the Int value.

Signature:

ptyProtectInt(Int input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Int value.

Example:

select ptyProtectInt(1234567, 'Int_DE');

Supported Protection Methods:

ptyUnprotectInt()

This UDF unprotects the protected Int value.

Signature:

ptyUnprotectInt(int input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Int value.

Example:

select ptyUnprotectInt(ptyProtectInt(1234567, 'Int_DE'), 'Int_DE');

Supported Protection Methods:

ptyReprotect() - Int

This UDF reprotects the Int format protected data, which was earlier protected using the ptyProtectInt UDF, with a different data element.

Signature:

ptyReprotect(int input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected Int value.

Example:

select ptyReprotect(ptyProtectInt(1234567, 'Token_Integer'), 'Token_Integer','new_Token_Integer');

Supported Protection Methods:

2.6 - Date UDFs

This section provides a list of the Date UDFs for the protect, unprotect, and reprotect operations.

Caution: There are inconsistencies observed when Trino is used to fetch and store date values from HDFS, where data was stored using Hive. It is recommended to verify if the correct date and datetime values are retrieved when the data is fetched from or stored in HDFS without using the Trino UDFs. If the data consistency is maintained, only then proceed to the Trino Date or DateTime UDFs.

ptyProtectDate()

This UDF protects the Date value.

Signature:

ptyProtectDate(date input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Date value.

Example:

select PtyProtectDate(cast('2018-10-10' as date), 'Date_DE');

Supported Protection Methods:

ptyUnprotectDate()

This UDF unprotects the protected Date value.

Signature:

ptyUnprotectDate(date input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Date value.

Example:

select ptyUnprotectDate(PtyProtectDate(cast('2018-10-10' as date), 'Date_DE'), 'Date_DE');

Supported Protection Methods:

ptyReprotect() - Date

This UDF reprotects the Date format protected data, which was earlier protected using the ptyProtectDate UDF, with a different data element.

Signature:

ptyReprotect(date input, varchar oldDataElement, varchar newDataElement)

Parameters:

Warning: Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Returns:
This UDF returns the protected Date value.

Example:

select PtyReprotect(cast('2018-10-10' as date), 'Date_DE', 'new_Date_DE');

Supported Protection Methods:

2.7 - DateTime UDFs

This section provides a list of DateTime UDFs for the protect, unprotect, and reprotect operations.

Caution: There are inconsistencies observed when Trino is used to fetch and store date values from HDFS, where data was stored using Hive. It is recommended to verify if the correct date and datetime values are retrieved when the data is fetched from or stored in HDFS without using the Trino UDFs. If the data consistency is maintained, only then proceed to the Trino Date or DateTime UDFs.

ptyProtectDateTime()

This UDF protects the TIMESTAMP value. The DateTime UDFs in Trino support an input precision of 12 digits or picoseconds.

Signature:

ptyProtectDateTime(timestamp(p) input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Timestamp(p) value.

Example:

select ptyProtectDateTime(cast('2018-10-10 20:35:17.123' as TIMESTAMP(3)), 'DateTime_DE');

Supported Protection Methods:

ptyUnprotectDateTime()

This UDF unprotects the protected Timestamp value.

Signature:

ptyUnprotectDateTime(timestamp(p) input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Timestamp(p) value.

Example:

select ptyUnprotectDateTime(ptyProtectDateTime(cast('2018-10-10 03:04:05.123' as TIMESTAMP(3)), 'DateTime_DE'), 'DateTime_DE');

Supported Protection Methods:

ptyReprotect() - DateTime

2.8 - VarChar Encryption UDFs

This section provides a list of VarChar encryption UDFs for the protect, unprotect, and reprotect operations.

ptyStringEnc()

This UDF encrypts the Varchar value.

Signature:

ptyStringEnc(varchar input, varchar DataElement)

Parameters:

Warning:

• The string encryption UDFs are limited to accept 2 GB data size at maximum as input.
• Ensure that the field size for the protected binary data post the required encoding does not exceed the 2 GB input limit.
• The field size to store the input data is dependent on the encryption algorithm selected, such as, AES-128, AES-256, 3DES, and CUSP, and the encoding type selected, such as, No Encoding, Base64, and Hex.
• Ensure to set the input data size based on the required encryption algorithm and encoding so that the it does not exceed the 2 GB input limit.

Returns:
This UDF returns the encrypted Varbinary value.

Example:

select ptyStringEnc('ProtegrityProt','AES128_DE');

Exception:

• ptyTrinoProtectorException: INPUT-ERROR: Tokenization or Format Preserving Data Elements are not supported: An unsupported data element is provided.
• java.io.IOException: Too many bytes before newline: 2147483648: The length of the input needs to be less than the maximum limit of 2 GB.

Supported Protection Methods:

ptyStringDec()

This UDF decrypts the Varbinary value.

Signature:

ptyStringDec(varbinary input, varchar DataElement)

Parameters:

Returns:
This UDF returns the decrypted VarChar value.

Example:

select ptyStringDec(ptyStringEnc('ProtegrityProt','AES128_DE'),'AES128_DE');

Exception:

• ptyTrinoProtectorException: INPUT-ERROR: First argument (Input Data to be unprotected) is not a valid Binary Datatype: The input data, which is not in binary format is provided.
• ptyHiveProtectorException: INPUT-ERROR: Tokenization or Format Preserving Data Elements are not supported: An unsupported data element is provided.

Supported Protection Methods:

ptyStringReEnc()

This UDF re-encrypts the Varbinary format encrypted data with a different data element.

Signature:

ptyStringReEnc(varbinary input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the Varbinary format data, which is re-encrypted.

Example:

select ptyStringReEnc(ptyStringEnc('ProtegrityProt','AES128_DE'),'AES128_DE','new_AES128_DE');

Exception:

• ptyTrinoProtectorException: INPUT-ERROR: First argument (Input Data to be reprotected) is not a valid Binary Datatype: The input data, which is not in binary format is provided.
• java.io.IOException: Too many bytes before newline: 2147483648: The length of the input needs to be less than the maximum limit of 2 GB.
• com.protegrity.hive.udf.ptyTrinoProtectorException: 26, Unsupported algorithm or unsupported action for the specific data element: An unsupported data element is provided.

Supported Protection Methods:

2.9 - Unicode UDFs

This section provides a list of Unicode UDFs for the protect, unprotect, and reprotect operations. This UDF should be used only to tokenize the Unicode data in Trino, migrate the tokenized data from Trino to a Teradata database, and detokenize the data using the Protegrity Database Protector. Ensure to use this UDF with a Unicode tokenization data element only.

ptyProtectUnicode()

This UDF protects the Varchar (Unicode) values.

Signature:

ptyProtectUnicode(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Varchar value.

Example:

select ptyProtectUnicode('ProtegrityProt','Unicode_DE');

Supported Protection Methods:

ptyUnprotectUnicode()

This UDF unprotects the existing protected string value. This UDF should be used only to tokenize the Unicode data in Trino, migrate the tokenized data from Trino to a Teradata database, and detokenize the data using the Protegrity Database Protector. Ensure to use this UDF with a Unicode tokenization data element only.

Signature:

ptyUnprotectUnicode(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Varchar(Unicode) value.

Example:

select ptyUnprotectUnicode(ptyProtectUnicode('ProtegrityProt','Unicode_DE'),'Unicode_DE');

Supported Protection Methods:

ptyReprotectUnicode()

This UDF reprotects the Varchar format protected data, which was earlier protected using the ptyProtectUnicode() UDF, with a different data element. This UDF should be used only to tokenize the Unicode data in Trino, migrate the tokenized data from Trino to a Teradata database, and detokenize the data using the Protegrity Database Protector. Ensure to use this UDF with a Unicode tokenization data element only.

Signature:

ptyReprotectUnicode(varchar input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected VarChar value.

Example:

select ptyReprotectUnicode(ptyProtectUnicode('ProtegrityProt','Unicode_DE'),'Unicode_DE','new_Unicode_DE');

Supported Protection Methods:

2.10 - Decimal UDFs

This section provides a list of the Decimal UDFs for the protect, unprotect, and reprotect operations. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

ptyProtectDecimal()

This UDF protects the Decimal value.

Signature:

ptyProtectDecimal(decimal input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Decimal value. If the input value is NULL and the data element is not a part of the policy, then the output value returned is NULL.

Example:

select ptyProtectDecimal(12332212222223.033, 'NoEnc');

Supported Protection Methods:

ptyUnprotectDecimal()

This UDF unprotects the protected Decimal value. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Signature:

ptyUnprotectDecimal(decimal input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Decimal value. If the input value is NULL and the data element is not a part of the policy, then the output value returned is NULL.

Example:

select ptyUnprotectDecimal(12332212222223.033, 'NoEnc');

Supported Protection Methods:

ptyReprotect() - Decimal

This UDF reprotects the Decimal format protected data, which was earlier protected using the ptyProtectDecimal() UDF, with a different data element.

Signature:

ptyReprotect(decimal input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected Decimal value.

Example:

select ptyReprotect(12332212222223.033,'NoEnc','NoEnc');

Supported Protection Methods:

2.11 - Double UDFs

This section provides a list of Double UDFs for the protect, unprotect, and reprotect operations.

ptyProtectDouble()

This UDF protects the Double value.

Signature:

ptyProtectDouble(double input, varchar dataElement)

Parameters:

Note: It is an observed behavior with Trino that the UDF accepting double parameter also accepts decimal and integer parameter due to internal data type conversion.

Returns:
This UDF returns the protected Double value.

Example:

select ptyProtectDouble(12345, 'No_Enc');

Supported Protection Methods:

ptyUnprotectDouble()

This UDF unprotects the protected Double value. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Signature:

ptyUnprotectDouble(double input, varchar dataElement)

Parameters:

Returns:
This UDF returns the original Double value.

Example:

select ptyUnprotectDouble(12345, 'No_Enc');

Supported Protection Methods:

ptyReprotect() - Double

This UDF reprotects the Double format protected data, which was earlier protected using the ptyProtectDouble() UDF, with a different data element. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Signature:

ptyReprotect(double input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected Double value.

Example:

select ptyReprotect(09457, 'No_Enc', 'new_No_Enc');

Supported Protection Methods:

2.12 - VarBinary Encryption UDFs

ptyBinaryEnc()

This UDF protects the VarBinary values. If the varbinary data size exceeds 32 MB, then set the configuration in the config.properties file and then restart the Trino server on all the nodes (co-ordinator and worker).

node-manager.http-client.max-content-length=64MB
exchange.http-client.max-content-length=64MB

Signature:

ptyBinaryEnc(VarBinary input, Varchar DataElement)

Parameters:

Returns:
This UDF returns the encrypted Varbinary value.

Example:

select ptyBinaryEnc(X'12A23D43', 'AES256');
select ptyBinaryEnc(binary_col1, 'AES256') from table1;

Supported Protection Methods:

ptyBinaryDec()

This UDF decrypts the Varbinary value.

Signature:

ptyBinaryDec(VarBinary input, Varchar DataElement)

Parameters:

Returns:
This UDF returns the decrypted VarBinary value.

Example:

select ptyBinaryDec(X'215b807cdfbc', 'AES256');
select ptyBinaryDec(binary_col1, 'AES256') from table1;

Supported Protection Methods:

ptyBinaryReEnc()

This UDF re-encrypts the Varbinary format encrypted data with a different data element.

Signature:

ptyBinaryReEnc(VarBinary input, Varchar oldDataElement, Varchar newDataElement)

Parameters:

Returns:
This UDF returns the Varbinary format data, which is re-encrypted.

Tokenization or Format Preserving Data Elements are not supported. Only encryption data elements are supported.

Example:

select ptyBinaryReEnc(X'12A23D43', 'Old_AES256', 'New_AES256');
select ptyBinaryReEnc(binary_col1, 'Old_AES256', 'New_AES256') from table1;

Supported Protection Methods: