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);