Create an Encrypted Table
The following example creates an encrypted table, enables access to it, inserts rows, and then selects them:
CREATE TABLE socsec1
(fname char(10),
lname char(20),
socsec char(11) encrypt nosalt)
WITH ENCRYPTION=AES256,
PASSPHRASE='transparent encryption example';
MODIFY socsec1 ENCRYPT
WITH PASSPHRASE='transparent encryption example';
SET TRACE POINT DM805;
INSERT INTO socsec1 VALUES ('John', 'Smith', '012-33-4567');
INSERT INTO socsec1 VALUES ('Lois', 'Lane', '010-40-1234');
INSERT INTO socsec1 VALUES ('Charlie', 'Brown', '012-44-9876');
SELECT * FROM socsec1;
The following results are returned:
+----------+--------------------+-----------+
|fname |lname |socsec |
+----------+--------------------+-----------+
|John |Smith |012-33-4567|
|Lois |Lane |010-40-1234|
|Charlie |Brown |012-44-9876|
+----------+--------------------+-----------+
(3 rows)
The encryption is transparent to the application (in this case, the SQL terminal monitor), as shown by the plain text values in the socsec column.
The SET TRACE POINT DM805 statement sends a dump of encrypted buffers immediately after the encryption processing to II_DBMS_LOG. Trace point DM806 sends a dump of encrypted buffers immediately before decryption processing. The log shows the values that are stored for the social security numbers:
AES 256-bit encrypt blocks:
1C90492C913D7D9195FED8507F0D1BFE >,I...}=.P.......<
AES 256-bit encrypt blocks:
FF24FB9037A156F6D4CE57921F0EFD07 >..$..V.7.W......<
AES 256-bit encrypt blocks:
94EE866C722BEA0AF096EF3D64347271 >l.....+r=...qr4d<
For transparent encryption, the value stored includes salt (see
Understanding Salt) (if any) and a verifying hash, in addition to the user data itself.