ALTER PASSPHRASE Examples
Example 1:
In the
unlocked database (see
ENABLE PASSPHRASE), first SET AUTOCOMMIT ON, then change the passphrase and mainkey:
SET AUTOCOMMIT ON;
ALTER PASSPHRASE 'InteractiveGraphicsRetrievalSystem' TO
'InCoddWeTrustSoBringYourData' WITH NEWKEY;
The new passphrase must be used to lock and unlock the database going forward.
The option NEWKEY generates a new main key. This also means that the database key of an encrypted database changes as it is derived from the main key. As a result, the meta data containing the table encryption keys gets decrypted with the old database key and re-encrypted with the new database key. To perform this, a full write lock on the database is needed, and depending on the size of the meta data involved it can be a computationally expensive operation.
Example 2:
In the unlocked database first SET AUTOCOMMIT ON, then change the passphrase only:
SET AUTOCOMMIT ON;
ALTER PASSPHRASE 'InteractiveGraphicsRetrievalSystem' TO
'InCoddWeTrustSoBringYourData';
The new passphrase must be used to lock and unlock the database going forward.
Without the option NEWKEY only the passphrase is changed. The existing main key is decrypted based on the old passphrase and re-encrypted based on the new passphrase. The database key does not change, therefore no further decryption and re-encryption is necessary.
Example 3:
In the unlocked database first SET AUTOCOMMIT ON, then change the passphrase and stash it:
SET AUTOCOMMIT ON;
ALTER PASSPHRASE 'InteractiveGraphicsRetrievalSystem' TO
'InCoddWeTrustSoBringYourData' WITH STASH;
It is not necessary to unlock the database as the passphrase is stashed going forward. It is not possible to lock the database.
To remove the passphrase later from the stash without changing the passphrase itself:
SET AUTOCOMMIT ON;
ALTER PASSPHRASE 'InCoddWeTrustSoBringYourData' TO
'InCoddWeTrustSoBringYourData';
Without the option STASH the passphrase is not stashed anymore. As the same phrase is specified as old and new passphrase, it is not changed. Going forward, the passphrase must be used to lock and unlock the database.
Example 4:
In a non encrypted database change the passphrase that was automatically generated and stashed at database creation:
SET AUTOCOMMIT ON;
ALTER PASSPHRASE TO 'InCoddWeTrustSoBringYourData';
Only the new passphrase is specified because the automatically generated passphrase is stashed and not known to the user. Without the option STASH, the new passphrase is not stashed, therefore it must be used to lock and unlock the database going forward.
Example 5:
In the unlocked database change the iteration count of the Password Based Key Derivation Function (PBKDF) from its default of 2048 to 500000 without changing the passphrase itself:
SET AUTOCOMMIT ON;
ALTER PASSPHRASE 'InCoddWeTrustSoBringYourData' TO
'InCoddWeTrustSoBringYourData'
WITH PBKDF=(ITERATIONS=500000);
Changing a PBKDF parameter derives a new protection key from the passphrase, even if the passphrase itself is not changed. The old protection key is used to decrypt the main key and the new protection key is used to re-encrypt the main key. Without the option STASH the passphrase is not stashed, therefore it must be used to lock and unlock the database going forward.
A higher iteration count for the PBKDF slows down the key derivation operation. This makes brute force attempts to compromise the passphrase more difficult.
Last modified date: 12/19/2024