VARCHAR UDFs

This section provides a list of VARCHAR UDFs for both, protect and unprotect operations.

pty_varcharins

This UDF protects the VARCHAR data through Tokenization or the No Encryption method.

Signature:

pty_varcharins(data VARCHAR(8000), dataelement VARCHAR(64))

Parameters:

Results:
This UDF returns the protected value as the VARCHAR(8000) datatype.

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

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_varcharins('Protegrity123','TE_AN_L0R0_Y');

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

pty_varcharsel

This UDF unprotects the protected VARCHAR data through Tokenization or the No Encryption method.

Signature:

pty_varcharsel(data VARCHAR(8000), dataelement VARCHAR(64))

Parameters:

Returns:
This UDF returns the unprotected value as the VARCHAR(8000) datatype.
This UDF returns the protected value when the user does not have the required access rights in the policy.

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

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_varcharsel('m6g2ZC6qb0xSAY','TE_AN_L0R0_Y');

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

pty_hash_varchar

This UDF protects the VARCHAR data and calculates the hash value.

Note: This function is irreversible, that is, the protected data cannot be unprotected.

Signature:

pty_hash_varchar(data VARCHAR(8000), dataelement VARCHAR(64))

Parameters:

Returns:
This UDF returns the hash value as the VARBINARY(8000) datatype.

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

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_hash_varchar('Protegrityl23','HMAC_SHA256');

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

Note: New data elements under HMAC_SHA1 cannot be created. However, existing data elements under HMAC_SHA1 remain supported and can be used. HMAC_SHA1 is deprecated. Protegrity recommends transitioning to HMAC_SHA256.

pty_varcharenc

This UDF encrypts the VARCHAR data using the encryption data element.

Signature:

pty_varcharenc(data VARCHAR(8000), dataelement VARCHAR(64))

Parameters:

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

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

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_varcharenc('Protegrityl23','AES256');

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

pty_varchardec

This UDF decrypts the encrypted data that was encrypted using the pty_varcharenc UDF.

Signature:

pty_varchardec(data VARBINARY(8000), dataelement VARCHAR(64))

Parameters:

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

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

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_varchardec(<database_name>.dbo.pty_varcharenc('Protegrityl23','AES256'),'AES256');

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