(535) Data encryption in the target database

Case study
When an employee master file on the AS400 contains sensitive data, you can encrypt it in the target database.

Library - EMPLIBR
Physical file – EMPMASTER
Data fields –
   F1 – Employee ID
   F2 – Social security number (character) 
   F3 – Salary (decimal) 
Target database – MS SQL Server 

-- Create database key
USE EMPLIBR;
CREATE MASTER KEY EMPMASTERION BY PASSWORD = 'pwd123';

-- Create self signed certificate
USE EMPLIBR;
CREATE CERTIFICATE Certificate1
EMPMASTERION BY PASSWORD = 'pwd246'
WITH SUBJECT = 'Protection';

-- Define two extended fields in the target database, refer to (530) Define extended fields in the target database
USE QMIRROR;
INSERT INTO ZEXFLD (EXLIB,EXFILE,EXFLDE,EXFLDT,EXFUNC,EXFTXT) VALUES('EMPLIBR','EMPMASTER','F2_ENCRYPTED','VARBINARY(MAX)','','Encrypted F2');
INSERT INTO ZEXFLD (EXLIB,EXFILE,EXFLDE,EXFLDT,EXFUNC,EXFTXT) VALUES('EMPLIBR','EMPMASTER','F3_ENCRYPTED','VARBINARY(MAX)','','Encrypted F3'); 

-- Create a trigger by inserting records into ZTBSQL, refer to (520) Create extra indexes in the target database
USE QMIRROR;
INSERT INTO ZTBSQL (ZQNDX,ZQBOA,ZQKEY,ZQSEQ,ZQSQL) VALUES(1,'B','EMPLIBR..EMPMASTER;',100,'USE EMPLIBR;');
INSERT INTO ZTBSQL (ZQNDX,ZQBOA,ZQKEY,ZQSEQ,ZQSQL) VALUES(2,'B','EMPLIBR..EMPMASTER;',100,'DROP TRIGGER IF EXISTS ENCRYPT_TRIGGER;'); INSERT INTO ZTBSQL (ZQNDX,ZQBOA,ZQKEY,ZQSEQ,ZQSQL) VALUES(3,'B','EMPLIBR..EMPMASTER;',100,
CREATE TRIGGER ENCRYPT_TRIGGER ON EMPMASTER
AFTER INSERT,UPDATE AS
BEGIN
     UPDATE T
     SET F2_ENCRYPTED = EncryptByCert(Cert_ID(''Certificate1''), T.F2), T.F2=NULL,
     F3_ENCRYPTED = EncryptByCert(Cert_ID(''Certificate1''), CONVERT(VARCHAR(20), T.F3)), T.F3=NULL
     FROM EMPMASTER AS T
     INNER JOIN inserted AS I
     ON i.RRN400 = T.RRN400;
END'); 

-- Select physical file EMPLIBR/EMPMASTER by taking QMirror command option 4
-- Start journal physical file by taking QMirror command option 8
-- Start QMirror and wait EMPLIBR/EMPMASTER to be downloaded

-- Reading decrypted data by certification
USE EMPLIBR;
SELECT F1,
F2,CONVERT(VARCHAR(MAX), DecryptByCert(Cert_ID('Certificate1'), F2_ENCRYPTED, N'pwd246')) AS F2_E, F3,CONVERT(VARCHAR(MAX), DecryptByCert(Cert_ID('Certificate1'), F3_ENCRYPTED, N'pwd246')) AS F3_E FROM EMPMASTER;

Previous (530) Define extended fields in the target database
Next (540) Define hidden fields in the target database