User Guide: Privacera Encryption Integration with Vertica¶

This user guide explains how to integrate Privacera Encryption with Vertica to enable encryption, decryption, and data masking. It provides step-by-step instructions to help you configure and use these features effectively. - Database: Customer_DB - Schema: Customer_Schema - Table: Customer_Data - User: Sally - Admin User: dbadmin (used to create encrypted tables)

Prerequisites¶

Running Vertica Cluster Single Node or Multi Node.
Privacera Encryption must be enabled and Integrated in the Vertica cluster.
Vertica version 12.0.x.
Sally belongs to group PEG_GROUP.
PEG_GROUP is assigned the role PEG_ROLE.
Group and Role creation: Vertica Group & Role Documentation

1. Stop Existing Vertica Database¶

To create a new database, stop the currently running one:

Stopping Vertica Database

2. Create a New Database¶

Use either Command Line or Admin Tool.

Command Line: vsql CLI Usage
Admin Tool: AdminTools Usage

Bash
# As dbadmin user
sudo su - dbadmin

# Single node
/opt/vertica/bin/admintools -t create_db \
  --data_path=/home/dbadmin \
  --catalog_path=/home/dbadmin \
  --database=Customer_DB \
  --password=MyStrongPassword \
  --hosts=localhost

# Multi-node (example)
/opt/vertica/bin/admintools -t create_db \
  --data_path=/home/dbadmin \
  --catalog_path=/home/dbadmin \
  --database=Customer_DB \
  --password=MyStrongPassword \
  --hosts=<NODE_1>,<NODE_2>...<NODE_N> \

Bash
/opt/vertica/bin/vsql -U dbadmin -w MyStrongPassword -d Customer_DB

4. Create Schema and Table¶

SQL
CREATE SCHEMA Customer_Schema;

CREATE TABLE Customer_Schema.Customer_Data (
  Name varchar(512), 
  SSN varchar(128), 
  US_PHONE_NUMBER varchar(128), 
  Address varchar(512), 
  Age int, 
  CreditCard varchar(128), 
  DOB date, 
  Createtime timestamp, 
  Createtime_with_tz timestamptz, 
  Weight double precision, 
  Height float, 
  Region_Code varchar(3), 
  Email varchar(1024), 
  Multilingual varchar(2898)
);

5. Insert Sample Data¶

Warning

This data is fictional and used for demonstration purposes only.
Any resemblance to real individuals is purely coincidental.

SQL
#Insert Data to the Table
insert into Customer_Schema.Customer_Data values ('Alexander','504-41-3444','(555) 987-6543','728 Durward Causeway Suite 304 - Anchorage',27,'3456552560100243',DATE'1996-12-31',TIMESTAMP'2021-07-01 08:43:28.124',TIMESTAMPTZ'July 1 08:43:28.12 2021 PST',56.4,5.4,'010','Alexander@example.com','española');
insert into Customer_Schema.Customer_Data values ('Emma','545-72-7432','555-123-4567','180 McGlynn Union Suite 039 - Blaine',54,'378282246310005',DATE'January 8, 1969',TIMESTAMP'2022-12-31 23:59:59',TIMESTAMPTZ'2022-12-31 23:59:59 -07:00',100.1,6.2,'011','Emma@ymail.com','francés(masculine)');
insert into Customer_Schema.Customer_Data values ('Mark','545-72-7432','1-800-555-1234','9472 Iliana Brooks Apt. 072 - Bristol',20,'5523094379294556',DATE'02-01-2003',TIMESTAMP'2022-08-01 08:43:28.345',TIMESTAMPTZ'2022-08-01 08:43:28.345 +05:00',89.12,5.91,'012','mark.jones@outlook.com','flou(ə)r');
insert into  Customer_Schema.Customer_Data values ('Severiano','159-24-8715','212-555-7890','065 Agnes Rapid Suite 194 - Freeport',32,'4539294022785332',DATE'1991-08-20',TIMESTAMP'1918-03-15 10:01:17',TIMESTAMPTZ'1918-03-15 10:01:17 -09:00',40.10,4.91,'013','Severiano.wilson@example.net','kräkəˌdīl');
insert into Customer_Schema.Customer_Data values ('Apolo','009-92-6449','(123) 456-7890','180 McGlynn Union Suite 039 - Blaine',50,'4485338578433757',DATE'12-08-1973',TIMESTAMP'2010-09-10 08:43:28.564',TIMESTAMPTZ'2010-09-10 08:43:28.56 -02:00',145.12,5.2,'014','Apolosmith@gmail.com','über');
insert into Customer_Schema.Customer_Data values ('Logan Taylor','479-06-8654','(890) 123-4567','9603 Aniya Key - Levittown, PA / 83298',45,'5337656590548758',DATE'1978-03-20',TIMESTAMP'2023-07-01 06:30:15',TIMESTAMPTZ'2023-07-01 06:30:15 -07:00',145.12,5.2,'015','Logan_Taylor@gmail.com','Düsseldorf');
insert into Customer_Schema.Customer_Data values ('Liam Davis','519-85-7167','(678) 901-2345','0505 Krista Hill - East Hartford, OH / 73101-7558',100,'6771897077528720',DATE'1923-10-10',TIMESTAMP'1995-09-20 15:45:30',TIMESTAMPTZ'1995-09-20 15:45:30+05:30',78.12,5.5,'016','Liam.Davis@gmail.com','Köln Москва');
insert into Customer_Schema.Customer_Data values ('Ava Wilson','517-64-8464','(210) 345-6789','935 Maymie Lake - Naperville, KS / 40903',23,'5422684603436809',DATE'2000-11-06',TIMESTAMP'2022-12-25 20:45:15',TIMESTAMPTZ'2022-12-25 20:45:15+08:00',35.12,5.10,'017','Ava12Wilson@ymail.com','北京市');
insert into Customer_Schema.Customer_Data values ('Noah Anderson','269605093','(123) 456-7890','115 Richie View - Bell Gardens, NY / 41433',34,'30556598204118',DATE'1989-09-10',TIMESTAMP'2003-07-10 08:15:00',TIMESTAMPTZ'2003-07-10 08:15:00-04:00',78.12,5.5,'018','Noah_anderson@hotmail.com','إسرائيل');
insert into Customer_Schema.Customer_Data values ('Ethan Thompson','238-36-0497','(987) 654-3210','855 Neal Light - Elizabeth, FL / 55482',18,'6706518060404121',DATE'2005-12-05',TIMESTAMP'2020-12-31 23:59:59',TIMESTAMPTZ'2020-12-31 23:59:59-07:00',67.123,6.4,'019','Ethan_3hompson@hotmail.com','!@#$');
insert into Customer_Schema.Customer_Data values ('Sophia Williams','527-37-9033','(210) 345-6789','188 Bailey Plain - Middletown, AL / 61943-0089',78,'347117313098521',DATE'1945-02-14',TIMESTAMP'2022-12-25 20:45:15',TIMESTAMPTZ'2022-12-25 20:45:15+08:00',65.123,5.87,'020','SophiaWilliams42@yahoo.com','añ');
insert into Customer_Schema.Customer_Data values ('Mason Johnson','408-51-9388','(456) 789-0123','3549 Schamberger Courts - Fresno, ID / 23577-9607',89,'5469453507080681',DATE'1934-09-23',TIMESTAMP'2023-08-15 18:45:00',TIMESTAMPTZ'2023-08-15 18:45:00+07:00',60.12,4.7,'021','massoncapowlowski.wunsch@hotmail.com','Estoy perdido/a');
insert into Customer_Schema.Customer_Data values ('Olivia','519600943','(456) 789-0123','8211 Krystina Fort - Aurora, MA / 53718',78,'6771890426118780',DATE'1945-12-09',TIMESTAMP'2011-11-11 18:00:00',TIMESTAMPTZ'2011-11-11 18:00:00-06:00',68.12,4.5,'022','olivia_kilback65@yahoo.com','Después');
insert into Customer_Schema.Customer_Data values ('Isabella','136089082','(678) 901-2345','4897 Rau Pass - Bellevue, WI / 71480-9199',90,'6771899888271649',DATE'1933-01-13',TIMESTAMP'2018-09-01 16:15:00',TIMESTAMPTZ'2018-09-01 16:15:00+02:00',88.12,6.5,'023','Isabellajohnson10@hotmail.com','사람');
insert into Customer_Schema.Customer_Data values ('Martinez','319368323','(987) 654-3210','108 Eliane Springs - Oakland, IN / 91208-6042',61,'5402977139754495',DATE'1962-03-03',TIMESTAMP'2014-04-22 09:45:30',TIMESTAMPTZ'2014-04-22 09:45:30+09:00',58.12,4.51,'024','gideonMartinez_deckow@gmail.com','대하다');

6. Create System and Custom Schemes¶

6.1. Create System Schemes (Encryption, Presentation)¶

Log in to the Privacera Portal
Navigate to Encryption & Masking > Encryption & Masking
Click Generate System Scheme
Confirm the creation by clicking Yes
System schemes will be created for:
- Encryption
- Presentation
Review the list of default system schemes

6.2. Create Custom Schemes (Encryption, Presentation, Masking)¶

Navigate to Encryption & Masking > Encryption & Masking
Click Add Scheme
Select the scheme type: Encryption, Presentation, or Masking
Enter the required details and click Save

11. Create Encrypted Table (as dbadmin)¶

Bash
1 2	`/opt/vertica/bin/vsql # Enter dbadmin password`

11.1. Encrypt the Data¶

SQL
CREATE TABLE Customer_Schema.Customer_Enc_Data AS 
SELECT 
  PRIVACERA.protect(ssn, 'SYSTEM_SSN') as enc_ssn, 
  PRIVACERA.protect(Address, 'system_address') as enc_address, 
  PRIVACERA.protect(CreditCard, 'SYSTEM_CREDITCARD') as enc_cc, 
  PRIVACERA.protect(email, 'System_email') as enc_email 
FROM 
  Customer_Schema.Customer_Data;

Expected Error (Before Policy Applied):

Bash
ERROR 3399: dbadmin does not have permission on protect permission on scheme

Note: There are 2 audits per scheme on a single node. For multi-node setups, audits equal 2 × n (where n = number of nodes).

In Self-Managed environments, Access Audits can be viewed by navigating to: Access Management > Audits > Access

11.2. Create Scheme Policy for dbadmin¶

Navigate to Access Management > Scheme Policies
Click Add Scheme Policy
Enter Protect Access for dbadmin as the Policy Name.
Under Schemes, select the following:
SYSTEM_SSN
SYSTEM_EMAIL
SYSTEM_CREDITCARD
SYSTEM_ADDRESS
In the Users section, select dbadmin.
Set Permissions to Protect.
Click Save to create the policy.

11.3. Run the Encryption Query Again¶

11.4 Verify:¶

SQL
SELECT * FROM Customer_Schema.Customer_Enc_Data;

12. Decrypt the Data (as Sally)¶

12.1 Grant SELECT to Sally¶

SQL
GRANT SELECT ON TABLE {DATABASE_NAME}.Customer_Schema.Customer_Enc_Data TO Sally;

12.2 Create UnProtect Scheme Policy for Sally:¶

Navigate to Access Management > Scheme Policies
Click on Add Scheme Policy
Policy Name: Unprotect Access
Schemes: SYSTEM_SSN, SYSTEM_EMAIL, SYSTEM_CREDITCARD, SYSTEM_ADDRESS
Select PEG_ROLE in the Role section
Permissions:
Unprotect
Click Save

12.3 Run the following query to decrypt the data:¶

SQL
/opt/vertica/bin/vsql {DATABASE_NAME} Sally
# Enter password

SQL
SELECT 
  PRIVACERA.unprotect(enc_ssn, 'SYSTEM_SSN') as dec_name, 
  PRIVACERA.unprotect(enc_Address, 'system_address') as dec_address, 
  PRIVACERA.unprotect(enc_cc, 'SYSTEM_CREDITCARD') as dec_cc, 
  PRIVACERA.unprotect(enc_email, 'System_email') as dec_email 
FROM 
  Customer_Schema.Customer_Enc_Data;

13 Obfuscate the Data¶

13.1 Policy: Presentation Access¶

Policy Name: Presentation Access
Encryption Schemes:
System_SSN, SYSTEM_EMAIL, SYSTEM_CREDITCARD, SYSTEM_ADDRESS
Presentation Schemes:
SYSTEM_PRESENTATION_SSN, SYSTEM_PRESENTATION_EMAIL, SYSTEM_PRESENTATION_CREDITCARD, SYSTEM_PRESENTATION_ADDRESS
Allow:
Role: PEG_ROLE
Permission: Unprotect

13.2 SQL to Obfuscate Data¶

SQL
SELECT 
  PRIVACERA.unprotect(enc_ssn, 'SYSTEM_SSN','SYSTEM_PRESENTATION_SSN') as dec_name, 
  PRIVACERA.unprotect(enc_Address, 'system_address','SYSTEM_PRESENTATION_ADDRESS') as dec_address, 
  PRIVACERA.unprotect(enc_cc, 'SYSTEM_CREDITCARD','SYSTEM_PRESENTATION_CREDITCARD') as dec_cc, 
  PRIVACERA.unprotect(enc_email, 'System_email','SYSTEM_PRESENTATION_EMAIL') as dec_email 
FROM 
  Customer_Schema.Customer_Enc_Data;

14. Mask the Data¶

14.1 Policy: Mask Access¶

Policy Name: Mask Access
Masking Schemes: MASK_SSN, MASK_EMAIL, MASK_ADDRESS
Allow:
Role: PEG_ROLE
Permission: MASK

14.2 SQL to Mask Data¶

SQL
SELECT 
  PRIVACERA.mask(ssn, 'MASK_SSN') as mask_ssn, 
  PRIVACERA.mask(Address, 'mask_address') as mask_address, 
  PRIVACERA.mask(email, 'Mask_email') as mask_email 
FROM 
  Customer_Schema.Customer_Data;