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';
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.