Implications of Data Encryption for Database Design and Operations
When an encrypted table is created, an AES key is created (either generated randomly or according to the key bit pattern specified on AESKEY=). The AES key--not the passphrase--controls the encrypted binary representation of data, and is stored in the catalog at the table level.
The passphrase is used to derive another AES key (not stored), which secures the catalog-stored AES key. The passphrase is essentially a lockbox for the encrypted AES key.
The fact that there is one AES key that encrypts the user data and another passphrase-derived key that protects the first AES key has the following implications for database design and operations:
• When the passphrase is changed, database backups before this point in time must be accessed using the old passphrase. Subsequent backups must be accessed using the new passphrase. When changing the passphrase, it is not necessary to suspend use of the table by rebuilding it (though the encryption access will be momentarily suspended until the new passphrase is confirmed with a second MODIFY command).
When the passphrase is changed, the catalog-stored key is decrypted with the old passphrase, re-encrypted with the new passphrase, and then replaced in the catalog. The user data is not re-encrypted because only the passphrase--not the underlying user key--has changed.
• If a backup or replication scheme works at the binary level by transferring and applying journal records, the original table and the copy table must use the same catalog-stored AES key. This will be the case if (1) the catalogs were copied at the binary level or (2) the AESKEY= option was used when the table was created.
Note: Tables that share an underlying AES key for encrypting user data can be protected by different passphrases.
• Encrypted data is usually unique at the binary level for each stored instance or at least in each stored table. It is therefore problematic to protect it outside of the table in the same manner that it is protected in the Ingres table files. When data is copied to a flat file, it is unprotected. Such files should use a different protection scheme, such as password encrypting the entire file, or writing it to an encrypted device or file system.