Using Table Column Encryption
To use table column encryption, use the CREATE TABLE statement to encrypt column values and define the encryption passphrase. To enable access to the table, use the MODIFY statement.
Access is Enabled for New Tables
When you create a table with encryption, it automatically unlocks the table as if you had issued the MODIFY...ENCRYPT WITH PASSPHRASE= statement, so you can insert data into the new table without having to issue the MODIFY to access it.
If you don’t want to allow access to the new table immediately, you must issue the MODIFY tablename ENCRYPT WITH PASSPHRASE = ' ' to disable access to the table.
Creating an Encrypted Table
The following example creates an encrypted table, enables access to it (although access is already enabled), 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<
Table column encryption by default includes in the stored value a salt (see “
Understanding Salt”) and a verifying hash, in addition to the user data itself. In the above example, there is no salt as the option NOSALT is specified in the CREATE TABLE statement.
Last modified date: 01/27/2026