Encrypted Data in Log Records and Auditdb Output
UPDATE statements are recorded in the log file differently for encrypted tables because:
• Encrypted changes are physically wider than the values of the decrypted column values
• When SALT is used, each re-encryption of the data yields a different physically stored value
Such characteristics of encrypted data in log and journal files can be seen in the auditdb output, which displays encrypted records in hex format and without decryption.
Consider this script, in which a table with two integer columns (only the second of which is encrypted) is created, populated, and then updated:
CREATE TABLE auditdemo (c1 INT, c2 INT ENCRYPT)
WITH ENCRYPTION=AES256, PASSPHRASE='auditdb demo';
MODIFY auditdemo ENCRYPT WITH PASSPHRASE='auditdb demo';
INSERT INTO auditdemo VALUES (1,2);
UPDATE auditdemo SET c1=3 WHERE c1=1;
UPDATE auditdemo SET c1=4 WHERE c1=3;
Even in the hex display we can see that c1 starts off as value 1 and then is updated to 3 and then to 4. But notice also that the remainder of these rows change too, because each rewrite of the row causes a re-encryption that produces a different value.
Audit for database aud
18-Aug-2010 12:50:03.59 Page 1
Begin : Transaction Id 00004c584d495a7e 18-Aug-2010 12:49:56.89
Username ingres
Create : Transaction Id 00004c584d495a7e Id (268,0) Table [auditdemo,ingres ]
Location $default
Insert/Append : Transaction Id 00004c584d495a7e Id (268,0) Table [auditdemo,ingres]
Record: 01000000006d8f35d1cff37f6e7eab7070403c13b785bc20e6e489f41731daa0230db78884
Update/Replace : Transaction Id 00004c584d495a7e Id (268,0) Table [auditdemo,ingres]
Old: 01000000006d8f35d1cff37f6e7eab7070403c13b785bc20e6e489f41731daa0230db78884
New: 03000000006e954a05f5c9f8a5b73234ec957386c23bf686d8141cb180d190a4b8c7bc17f6
Update/Replace : Transaction Id 00004c584d495a7e Id (268,0) Table [auditdemo,ingres]
Old: 03000000006e954a05f5c9f8a5b73234ec957386c23bf686d8141cb180d190a4b8c7bc17f6
New: 0400000000829e143ee15459d75aa0f8d3238a2b1ea1643a594b6dffb4792c5b59f8657bb5
End : Transaction Id 00004c584d495a7e 18-Aug-2010 12:49:56.90
The SQL statements in the script do not update column c2. So, despite the various encrypted incarnations of the record, the underlying value for column c2 is the one that was specified when the row was first inserted:
SELECT * FROM auditdemo
Executing . . .
+-------------+-------------+
|c1 |c2 |
+-------------+-------------+
| 4| 2|
+-------------+-------------+
(1 row)