Transparent Column Encryption (DBMS Server-level Encryption)
Column encryption is transparent and is done at the DBMS Server level. The Vector server handles encryption for the application and assures that data at rest is encrypted.
To use transparent 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.
The passphrase applies to all encrypted rows and columns in the table.
Access 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 can insert data into the new table without having to issue the MODIFY to access it.
If you are not going to allow access to the new table immediately, you must issue the MODIFY tablename ENCRYPT WITH PASSPHRASE = ' ' to disable access to the table.
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.
Enable Access to Encrypted Data
Access to encrypted data in a table that contains encrypted columns is possible only when it is enabled after server startup with the MODIFY statement:
MODIFY socsec1 ENCRYPT
WITH PASSPHRASE='transparent encryption example';
Disable Access to Encrypted Data
Perhaps a sensitive table is needed only during regular work hours, even though the DBMS runs continually. Disabling encryption and decryption at the end of the work day will prevent unauthorized access during the night shift.
To make the encrypted data inaccessible, disable the passphrase.
To disable the passphrase
Use the MODIFY statement, specifying an empty string for the passphrase:
MODIFY tablename ENCRYPT WITH PASSPHRASE='';
An attempt to access the data will result in the following message:
E_US24BF A query has been issued against a table for which encryption has not been unlocked with the MODIFY statement. This unlock statement must be issued every time the installation is restarted. Please contact your data administrator.
Change the Passphrase
The circle of trust for the table may change, or the law may require that the passphrase change periodically.
To change the passphrase for encrypted data, use the NEW_PASSPHRASE option on the MODIFY statement.
To change the passphrase
1. Issue the MODIFY statement with the following options:
MODIFY tablename ENCRYPT
WITH PASSPHRASE='encryption passphrase',
NEW_PASSPHRASE='new encryption passphrase';
2. Issue another MODIFY statement to enable the table with the new phrase:
MODIFY tablename ENCRYPT WITH PASSPHRASE = 'new encryption passphrase';
Issuing the second MODIFY re-enables access to encrypted data and ensures that the changed passphrase was typed correctly. We recommend doing this immediately, and before a COMMIT is issued. If a problem occurs, ROLLBACK to the old passphrase and try again.