CDP for Teradata VCL UDFs
protect_string
protect_string returns the protected data when a plaintext string and protection policy name is provided.
Prerequisites
CDP for Teradata VCL UDFs must be installed in the target database.
CDP for Teradata VCL container must be up and running.
CipherTrust Manager must be up and running.
Protection policy must be created.
Key used in the protection policy must be added to the Application Data Protection Clients Group with Use, Read, Encrypt, Decrypt, and Export permissions.
UDF Signature
function protect_string (data varchar(8192) CHARACTER SET UNICODE, protection_policy_name varchar(256)) returns varchar(8192) CHARACTER SET UNICODE
Input Parameters
data: Data to be protected in unicode character set.protection_policy_name: Protection policy to be used during the protect operation.
Return Value
The exact format of the return value depends on the protection policy versioning type.
Internal Version
If the protection policy versioning type is internal, the return value is protected_data (the ciphertext in unicode string format).
External Version
If the protection policy versioning type is external, the return value is a JSON-encoded unicode string of the following format:
{"status": "success", "protected_data": "<protected_data>", "external_version": "<external_version"}
Examples
Protection Policy Version Type is Internal
The following example encrypts the first and last name of a customer. It is assumed that the UDFs have been installed into the Thales database, a protection policy has been set up in the CipherTrust Manager with the name tokenize_name, and the character set ascii has been selected.
select thales.protect_string('John Doe','tokenize_name');
Query completed. One row found. One column returned.
Total elapsed time was 1 second.
thales.protect_string('John Doe','tokenize_name')
OHBU cCj
Protection Policy Version Type is External
The following example encrypts the first and last name of a customer. It is assumed that the UDFs have been installed into the Thales database, a protection policy has been set up in the CipherTrust Manager with the name tokenize_name, and the character set ascii has been selected.
select thales.protect_string('John Doe','tokenize_name');
Query completed. One row found. One column returned.
Total elapsed time was 1 second.
thales.protect_strng('John Doe','tokenize_name')
{"status":"success","protected_data":"OHBU cCj","external_version":"1001000"}
Tip
Use JSONExtractValue to extract the individual fields from the JSON-enoded string.
WITH json_data AS (SELECT NEW JSON (protect_string('123456','cdp-vcl-pp-ex-1')) AS json_val)
SELECT
json_val.JSONExtractValue('$.protected_data') AS protected_data,
json_val.JSONExtractValue('$.external_version') AS external_version
FROM json_data;
Query completed. One row found. 2 columns returned.
Total elapsed time was 1 second.
protected_data
OHBU cCj
external_version
1001000
Note
To accept ascii characters as input, the
protect_stringcall must refer to a protection policy that uses a predefined ascii character set.The input string must adhere to the BTEQ requirements, such as, escaping special characters. For example, if the input string contains an apostrophe, use a double apostrophe (It''s all about CDP UDFs).
reveal_string
reveal_string returns the plaintext data when a protected string, protection policy name, and protection policy version is provided.
Prerequisites
CDP for Teradata VCL UDFs must be installed in the target database.
CDP for Teradata VCL container must be up and running.
CipherTrust Manager must be up and running.
Protection policy must be created.
Key used in the protection policy must be added to the Application Data Protection Clients Group with Use, Read, Encrypt, Decrypt, and Export permissions.
UDF Signature
function reveal_string (protected_data varchar(8192) CHARACTER SET UNICODE, protection_policy_name varchar(256), protection_policy_version varchar(16)) returns varchar(8192) CHARACTER SET UNICODE
Input Parameters
protected_data: Protected data in unicode character set.protection_policy_name: Protection policy to be used for the reveal operation.protection_policy_version: If protection policy version type is external, specify a nonempty string to reveal the protected data. But if protection policy version type is internal, specify an empty string to reveal the protected data.
Return Value
The original data in unicode character set.
Examples
Protection Policy Version Type is Internal
The following example reveals the name of a customer, previously protected with protect_string. It is assumed that the UDFs have been installed into the Thales database, a protection policy has been set up in the CipherTrust Manager with the name tokenize_name, and the character set ascii has been selected.
select thales.reveal_string( 'OHBU cCj', 'tokenize_name', '' );
Query completed. One row found. One column returned.
Total elapsed time was 1 second.
thales.reveal_strng( 'OHBU cCj', 'tokenize_name', '' )
'John Doe'
Protection Policy Version Type is External
The following example reveals the name of a customer, previously protected with protect_string. It is assumed that the UDFs have been installed into the Thales database, a protection policy has been set up in the CipherTrust Manager with the name tokenize_name, and the character set ascii has been selected.
select thales.reveal_string( 'OHBU cCj','tokenize_name', '1001000');
Query completed. One row found. One column returned.
Total elapsed time was 1 second.
thales.reveal_strng( 'OHBU cCj','tokenize_name', '1001000')
John Doe
