Advanced Topics
Environment Variables
Listed below are the environment variables currently required for the Cloud Function.
| Key | Value | Desc |
|---|---|---|
| BATCHSIZE | 200 | Nbr of rows to chunk when using batch mode |
| CMPWD | Yourpwd! | CMPWD if using CADP |
| CMUSER | apiuser | CM USERID if using CADP |
| CRDPIP | 20.221.216.666 | CRDP Container IP if using CRDP |
| datatype | charint | datatype of column (char or charint) |
| keymetadata | 1001000 | policy and key version if using CRDP |
| keymetadatalocation | external | location of metadata if using CRDP (internal,external) |
| mode | revealbulk | mode of operation(protect,reveal,protectbulk,revealbulk) CRDP |
| protection_profile | plain-nbr-ext | protection profile in CM for CRDP |
| returnciphertextforuserwithnokeyaccess | yes | if user in CM not exist should UDF error out or retur ciphertext |
| usersetidincm | 716f01a6-5cab-4799-925a-6dc2d8712fc1 | userset in cm if user lookup is done |
| usersetlookup | no | should uselookup be done (yes,no) |
| usersetlookupip | 20.241.70.666 | userset lookup |
| showrevealinternalkey | yes | show keymetadata when issuing a protect call (CRDP) |
Application Data Protection UserSets
Application Data Protection UserSets are currently used for DPG to control how the data will be revealed to the users. These UserSets can also be independent of any Access Policy. Most cloud databases have some way to capture who is running the query and this information can be passed to CipherTrust Manager to be verified in a UserSet to ensure the person running the query has been granted proper access.
In GitHub, there is a sample class file called CMUserSetHelper that can be used to load a userset with values from an external identity provider such as LDAP. The name of this method is addAUserToUserSetFromFile. Once, users have been loaded into this userset, the usersetid must be captured and used as an environment variable for the Function.
The function has a number of environment variables that must be provided for the function to work. Please review the section on Environment Variables for more details.
Options for Handling Null Values
Since it is not possible to encrypt a column that contains null values or any column that has 1 byte, it is necessary to skip those to avoid getting an error when running the query. There are a couple of ways to handle this use case.
Option 1. Modify the queries.
Many times, you can avoid the query errors by simply adding a where clause to exclude values that have nulls or less than 2 bytes. For example: select * from FROM mw_demo_dataset_US.plaintext50cadpemailprotected_nulltest where email is not null and length(email) > 1; For those scenarios where that is not suffice some other examples are listed below.
Here is an example of a select statement that can be modified to handle null values:
SELECT
name,
CASE
WHEN email IS NULL THEN 'null'
WHEN length(email) < 2 then email
WHEN email = 'null' then email
ELSE ‘cpl-sales-l-app-us-01.mw_demo_dataset_US.thales_cadp_encrypt_char‘(email)
END AS email
FROM
mw_demo_dataset_US.plaintext50Cadpemailprotected_nulltest;
The above use case was for situations where the column contained both null and the word ‘null’.
Here is an example of a select statement that can be modified to handle null values in the where clause.
SELECT name, email, email_enc
FROM
mw_demo_dataset_US.plaintext50cadpemailprotected_nulltest
where CASE
WHEN email_enc IS NULL THEN 'null'
WHEN length(email_enc) < 2 then email_enc
WHEN email_enc = 'null' then email_enc
ELSE ‘cpl-sales-l-app-us-
01.mw_demo_dataset_US.thales_eadp_decrypt_char‘(email_enc)
END like "%gmail%"
| Row | name | email_enc | |
|---|---|---|---|
| 1 | Dr. Lemmie Zboncak | ikris@gmail.com | 1|OEk@RPDqC.GHd |
| 2 | Zillah Leuschke | scronin@gmail.com | 53mWY7Y@4bzb6.204 |
| 3 | Troy Gaylord | devon49@gmail.com | EsTMziF@lSZgZ.yN6 |
| 4 | Dr. Spurgeon Wintheiser | hilah17@gmail.com | PlM4vAd@qAIV9.oJC |
| 5 | Tatyana Bernhard | denny56@gmail.com | yVQ7ITA@3idYW.GqV |
| 6 | Renata Hilpert | tdickens@gmail.com | plzg3sz@oetcj.Opi |
| 7 | Deliah Douglas | sconnelly@gmail.com | ipXAUZWPX@koaH2.y88 |
| 8 | Amare Feeney | batz.geary@gmail-com | GO4W.CRN |
| 9 | Dr. Cristy Schinner | schulist.garfield@gmaiLoom | HIl90wCU.LPtU3p6o@F9ND716... |
| 10 | Vena Douglas | hustonchristiansen@gmail.com | 70prz.PXIOPoJJaWIQ@AvTP... |
Option 2. Modify the Cloud Function to skip encrypting these values.
Include an if statement checking for null values the word ‘null’ or any value less than 2.
if (redshiftrow != null && redshiftrow.size() > 0) {
JsonElement element = redshiftrow.get(0);
if (element != null && !element.isJsonNull()) {
sensitive = element.getAsString();
if (sensitive.isEmpty() || sensitive.length() < 2) {
if (sensitive.isEmpty()) {
JsonElement elementforNull = new JsonPrimitive("null");
sensitive = elementforNull.getAsJsonPrimitive().toString();
} else {
sensitive = element.getAsJsonPrimitive().toString();
}
encdata = sensitive;
} else {
if (sensitive.equalsIgnoreCase("null")) {
sensitive = element.getAsJsonPrimitive().toString();
encdata = sensitive;
} else {
sensitive = element.getAsJsonPrimitive().toString();
byte[] outbuf = encryptCipher.doFinal(sensitive.getBytes());
}
}
} else {
JsonElement elementforNull = new JsonPrimitive("null");
sensitive = elementforNull.getAsJsonPrimitive().toString();
encdata = sensitive;
}
} else {
JsonElement elementforNull = new JsonPrimitive("null");
sensitive = elementforNull.getAsJsonPrimitive().toString();
encdata = sensitive;
}
Note
When using this logic, it is important to know that any null column will return ‘null’. This should be fine for use cases where the query is not updating the column. For use cases where the source system is expecting null vs the word ‘null’ additional testing should be conducted on the systems that rely on this data type as being null vs the word ‘null’.
Options for keyname for CADP
Currently, the sample code uses a hard-coded key name. Other options to be considered are:
keyname as an environment variable.
keyname as an AWS secret.
keyname passed in an attribute to the function.
A database view example.
create view employee as select first_name, last_name, thales_cadp_aws_decrypt_char('testfaas', email) as email from emp_basic
Sample CRDP Docker Commands
As noted in the link above there are number of ways to deploy the CRDP container. For demo purposes here are the steps that were performed for a standalone use case.
sudo apt update
sudo apt install apt-transport-https ca-certificates curl software-properties-common
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu bionic stable"
sudo apt update
apt-cache policy docker-ce
sudo apt install docker-ce
sudo systemctl status docker
sudo usermod -aG docker crdpuser
su – crdpuser
docker ps
docker pull thalesciphertrust/ciphertrust-restful-data-protection:latest
exit
//Back on as root
docker run -e KEY_MANAGER_HOST=192.168.159.134 -e REGISTRATION_TOKEN=vw886rxzMolgtL1Gz950Ta9kVbDDvo0qgawGHthOc7iKnm8b1VP6R2ps3qifVDTN -p 8090:8090 -e SERVER_MODE=no-tls thalesciphertrust/ciphertrust-restful-data-protection
{"level":"info","time":"Thu, 20 Jun 2024 14:56:26 +0000","msg":"going to initialize shield"}
{"level":"info","time":"Thu, 20 Jun 2024 14:56:26 +0000","msg":"registerClient: Going to register the client"}
{"level":"info","time":"Thu, 20 Jun 2024 14:56:26 +0000","msg":"registerClient: Register the client successfully"}
{"level":"info","time":"Thu, 20 Jun 2024 14:56:32 +0000","msg":"","app_name":"crdpapp1","audit":true,"client_id":"be458360-e656-4faf-b7ee-0d210626b214","endpoint":"/v1/protect","jwt_username":"","key_name":"test","key_version":"1","method":"POST","protection_policy_name":"plain-alpha-internal","protection_policy_version":"4","source_ip":"192.168.159.1","status":"Success"}
{"level":"info","time":"Thu, 20 Jun 2024 14:56:45 +0000","msg":"","app_name":"crdpapp1","audit":true,"client_id":"be458360-e656-4faf-b7ee-0d210626b214","endpoint":"/v1/protect","jwt_username":"","key_name":"test","key_version":"1","method":"POST","protection_policy_name":"plain-alpha-internal","protection_policy_version":"4","source_ip":"192.168.159.1","status":"Success"}
{"level":"info","time":"Thu, 20 Jun 2024 14:56:51 +0000","msg":"","app_name":"crdpapp1","audit":true,"client_id":"be458360-e656-4faf-b7ee-0d210626b214","endpoint":"/v1/protect","jwt_username":"","key_name":"test","key_version":"1","method":"POST","protection_policy_name":"plain-alpha-internal","protection_policy_version":"4","source_ip":"192.168.159.1","status":"Success"}
{"level":"error","time":"Thu, 20 Jun 2024 14:56:57 +0000","msg":"error while encrypting the data, err: Input buffer is too short (len=1), it has to be at least 2 characters long."}
{"level":"error","time":"Thu, 20 Jun 2024 14:56:57 +0000","msg":"Input buffer is too short (len=1), it has to be at least 2 characters long.","app_name":"crdpapp1","audit":true,"client_id":"be458360-e656-4faf-b7ee-0d210626b214","endpoint":"/v1/protect","jwt_username":"","key_name":"test","key_version":"1","method":"POST","protection_policy_name":"plain-alpha-internal","protection_policy_version":"4","source_ip":"192.168.159.1","status":"Error"}
