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

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