(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 (int)
Target database – MS SQL Server

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

-- Create self signed certificate
USE EMPLIBR;
CREATE CERTIFICATE Certificate1
ENCRYPTION 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=’’’’,
	F3_ENCRYPTED = EncryptByCert(Cert_ID(''Certificate1''), CONVERT(VARCHAR(20), T.F3)), T.F3=0
	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
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd123';
USE EMPLIBR;
SELECT F1,
F2,CONVERT(VARCHAR(MAX), DecryptByCert(Cert_ID('Certificate1'), F2_ENCRYPTED, N'pwd246')) AS F2_E,
F3,CONVERT(int, 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