Encryption and Tables that Hold Audit Trails
Encrypted columns are in their encrypted format in the journals and thus in the audit trails created with auditdb. This means that the encrypted byte format must be specified for the table into which an audit trail (.trl file) is copied.
The table that holds the audit trail data must match the .trl file that is processed with the COPY statement, so for encrypted columns, use the length as displayed in the encrypted section of HELP TABLE.
Consider the auditdb example (in the Command Reference Guide) in which a table to hold audit trail data is created for the emp table. We decide to encrypt the salary column in the emp table:
CREATE TABLE emp
(eno I2,
ename CHAR(10),
age I1,
job I2,
salary MONEY ENCRYPT,
dept I2)
WITH ENCRYPTION=AES192, PASSPHRASE='encrypted audit example';
The HELP TABLE output for emp shows that the salary column will be 32 bytes long in the audit record:
Column encryption: AES192
Alter table totwidth: 54
Encrypted width: 54
Encrypted Column Name Type Width Salt
salary money 32 yes
So when we create an empaudit table to hold the audit trail for emp, we need to define the salary column as a non-nullable byte(32):
CREATE TABLE empaudit
(date INGRESDATE NOT NULL WITH DEFAULT,
usrname CHAR(32) NOT NULL WITH DEFAULT,
operation CHAR(8) NOT NULL WITH DEFAULT,
tranid1 INTEGER NOT NULL WITH DEFAULT,
tranid2 INTEGER NOT NULL WITH DEFAULT,
table_id1 INTEGER NOT NULL WITH DEFAULT,
table_id2 INTEGER NOT NULL WITH DEFAULT,
eno I2,
ename CHAR(10),
age I1,
job I2,
salary BYTE(32) NOT NULL,
dept I2);
Although the terminal output format for auditdb for encrypted tables is in hex and therefore hard for us to read, the imported audit trail is readable. The encrypted column is in AES encrypted format and thus readable only at this level. The plaintext value is protected.
SELECT date, operation, eno, hex(salary) AS salary FROM empaudit
Executing . . .
--------------------+------+---+----------------------------------------------------------------+
date |operat|eno|salary |
--------------------+------+---+----------------------------------------------------------------+
01-oct-2010 18:46:05|append| 1|CC438DD9ECEE3E406D52C4F7FDCA85FBD5BD11374E77E6819E024818E0C646B0|
01-oct-2010 18:46:05|append| 2|C3A5D3012B3EAAD877F2E2AFD02725225FE987370C7A12A97BAE32F6C7AF0B6B|
--------------------+------+---+----------------------------------------------------------------+
Last modified date: 01/30/2023