Data at Rest Encryption

Specific database table columns can be encrypted to enhance data security, ensure privacy, and protect media that contains database records holding sensitive information. This feature ensures that data in an Ingres database is stored on disk or other media in such a way that protected columns are unreadable without knowledge of the encryption passphrase. Ingres functions AES_ENCRYPT and AES_DECRYPT provide explicit encryption and decryption. Table-level encryption is not provided.

Encryption "at rest" refers to data on physical media recorded in a persistent form. This includes stored database records, the transaction log, journals, and checkpoints. Encrypted columns are stored in the database files using 128-, 192-, or 256-bit Advanced Encryption Standard (AES) encryption.

When encryption has been enabled for a table that contains encrypted columns, the encryption is transparent to the applications accessing the data. Only minimal changes, if any, are required at the application level.

Note: Data is readable in DBMS buffers when a client is communicating with the DBMS Server. Data is also not protected when copied out of the database to flat files.

You specify encryption by using options on the CREATE TABLE statement. For example, the following statement creates a table with an encrypted column:

CREATE TABLE socsectab
(
    fname CHAR(10),
    lname CHAR(20),
    socsec CHAR(11) ENCRYPT
)
    WITH ENCRYPTION=AES128,
        PASSPHRASE='this is a secret';

At the column level, you can specify the encryption option NOSALT to override the default encryption, which is done with salt (extra random bits).

To enable access to data in an encrypted table, you must know the passphrase, which is specified on the MODIFY statement:

To revoke access, specify an empty passphrase string using the MODIFY statement:

To change the passphrase, specify a new one on the MODIFY statement:

MODIFY socsectab ENCRYPT
WITH PASSPHRASE='this is a secret',
NEW_PASSPHRASE='we have a new secret';

By default, the optimizedb program skips encrypted columns, but they can be included by specifying the -ze flag.

This feature adds a new DBMS Server configuration parameter, dmf_crypt_maxkeys. For more information, see the help for Configuration-By-Forms or Configuration Manager.

Note: Because of changes to the standard catalogs, you must run upgradedb on existing installations.

Details on this feature are documented in the Security Guide, the MODIFY statement in the SQL Reference Guide, and the optimizeddb command in the Command Reference Guide.