BLOB UDFs

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

pty_blobenc

This function encrypts the data stored as VARBINARY(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_blobenc(data VARBINARY(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 security operation is configured in the policy, this UDF terminates with an appropriate error message when the user does not have the required access rights.

Example:

select <database_name>.dbo.pty_blobenc(cast('Protegrity' as varbinary(max)),'AES256');

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

pty_blobdec

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

Signature:

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

Parameters:

Returns:
This UDF returns the clear or decrypted 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_blobdec(<database_name>.dbo.pty_blobenc(cast('Protegrity' as varbinary(max)),'AES256'),'AES256')

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