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