Encryption Key Management
In an encrypted database, tables are encrypted on disk by randomly chosen table keys. These keys are encrypted by the database encryption key. The database encryption key is derived from the randomly chosen main key. The main key in turn is secured by the passphrase. A standard algorithm, called PBKDF (Password Based Key Derivation Function), is used to derive an encryption key, called “protection key”, from the passphrase, then this protection key is used to encrypt the main key. The “protection key” is not stored anywhere.
The ALTER PASSPHRASE statement lets you change the passphrase which protects the main key. If the WITH option NEWKEY is specified, not only the passphrase but also the main key is changed. This in turn changes the database encryption key as it is derived from the main key. But the table keys are left untouched.
The WITH option STASH for the ALTER PASSPHRASE statement allows the passphrase to be encrypted and stashed in an internal database system file. The database server can retrieve the stashed passphrase and make the database accessible without the need to first unlock the database. This convenience is traded off with slightly decreased security due to the stashing of the passphrase. A database with its passphrase stashed cannot be locked and DISABLE PASSPHRASE produces an error. If the passphrase is currently stashed, then running ALTER PASSPHRASE without the STASH option erases the passphrase from the stash.
The WITH option PBKDF=(…) for the ALTER PASSPHRASE statement lets you specify certain parameters for the PBKDF standard algorithm. The parameters are specified as a comma separated list of name=value pairs within the parenthesis of the option PBKDF=(…). For more details about these parameters see the documentation of the ALTER PASSPHRASE SQL statement in the “SQL Language Guide”.
The concept of the main key, from which the database encryption key is derived, also permits to derive different encryption keys for other purposes. Therefore, every database has a main key and consequently also a passphrase to secure the main key. By default, the passphrase for encrypted databases is required from the creator of the database and is not stashed. On the other hand, for not encrypted databases the passphrase is internally generated and stashed. Even though not encrypted databases now have a main key and passphrase, the stashing of this passphrase allows the full management of the database without having to know and handle the passphrase.
The ALTER KEYS statement lets you change the table keys without changing the passphrase and the main key. For each table in a database, the system randomly chooses a key for encryption/decryption operations.
These keys are encrypted with the database encryption key.
Note: The ALTER PASSPHRASE operation requires the AUTOCOMMIT setting to be ON; otherwise, an error will occur.
Note: After the passphrase and main key are changed, restoring from pre-existing checkpoints requires the old passphrase from the user if it was not stashed. Also, it is not possible to roll forward log records beyond the ALTER PASSPHRASE statement. It is therefore recommended to take a new checkpoint after altering the passphrase to ensure disaster recovery operations can be performed properly.
The following illustrates how key management works:
The default handling of the passphrase is different for encrypted and non encrypted databases:
• Encrypted databases:
The createdb utility requires the passphrase from the user when creating the database. The passphrase is not stashed and therefore must be remembered by the user. After the database is created, the database can be locked by issuing the statement DISABLE PASSPHRASE. A server shutdown also effectively locks an encrypted database. To access a locked database, it must be unlocked by issuing the ENABLE PASSPHRASE statement.
• Non encrypted databases:
The createdb utility does not require a passphrase from the user. Instead, a passphrase is internally and randomly generated and stashed. A database with a stashed passphrase cannot be locked and does not need unlocking.
The default handling of the passphrase can be changed by running the ALTER PASSPHRASE statement after database creation:
• Encrypted databases:
When running ALTER PASSPHRASE … WITH STASH, the database remains always accessible.With the passphrase in the stash, the database cannot be locked and DISABLE PASSPHRASE produces an error. ENABLE PASSPHRASE is superfluous and has no effect.
To have the ability to lock an encrypted database, don’t run the ALTER PASSPHRASE with the STASH option. If the passphrase is currently stashed and you want to eliminate it from the stash, then run ALTER PASSPHRASE without the STASH option.
• Non encrypted databases:
Running ALTER PASSPHRASE without the STASH option eliminates the passphrase from the stash. Subsequently it is possible to lock the database with the statement DISABLE PASSPHRASE. Also, without the passphrase being stashed, a restart of the database server requires that the database gets unlocked with the ENABLE PASSPHRASE statement.
If you do not want to take care of the passphrase for a non encrypted database, the easiest way is to never run the ALTER PASSPHRASE statement. The passphrase generated at creation time remains stashed and keeps the database always accessible without having to unlock it.
If you want to change the passphrase, but don’t want to remember it, then make sure that ALTER PASSPHRASE is always run with the STASH option.
Note: For both, encrypted and non encrypted databases, it is necessary to remember the passphrase if it is not stashed!
Last modified date: 12/19/2024