CLOB UDFs

This section provides a list of UDFs that can be used for both, encryption and decryption of the data stored in CLOB.

pty_clobenc

This function encrypts the data stored as VARCHAR(max*) using any encryption data element.

Warning: This function supports encryption of data up to 1GB. However, exceeding this limit will result in memory issues.

Signature:

pty_clobenc(data VARCHAR(max), dataelement VARCHAR(64))

Parameters:

Returns:
This UDF returns the encrypted value as the VARBINARY (max*) datatype.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

Note: The max parameter specifies the maximum length of input and output data, which depends on the specified maximum storage limitation of the VARBINARY data type supported by the MSSQL database.

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

select <database_name>.dbo.pty_clobenc('Protegrity','AES256');

In the Example, value=‘AES256’, is the name of the data element.

pty_clobdec

This function decrypts the encrypted data stored as VARBINARY(max*) using any encryption data element.

Signature:

pty_clobdec(data VARBINARY(max), dataelement VARCHAR(64))

Parameters

Returns:
This UDF returns the clear or decrypted value as the VARCHAR(max*) datatype.

Attention: If the user does not have access to the data in the policy, this UDF returns ‘NULL’.

Note: The max parameter specifies the maximum length of input and output data, which depends on the specified maximum storage limitation of the VARBINARY data type supported by the MSSQL database.

Exception:
If a data element or a security operation is configured in the policy and the user does not have access, then the function will terminate with an appropriate error message.

Example:

select <database_name>.dbo.pty_clobdec(dbo.pty_clobenc('Protegrity','AES256'),'AES256');

In the Example, value=‘AES256’, is the name of the data element.