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