5. Using Data at Rest Encryption : Transparent Column Encryption : Create an Encrypted Table
 
Share this page                  
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.