Encrypted Columns and dump database

dump and load work on the ciphertext of encrypted columns, ensuring that the data for encrypted columns remains encrypted while on disk. These commands also pertain to the entire database; default keys and keys that are created in the same database are dumped and loaded along with the data to which they pertain.

If your keys are in a separate database than the columns they encrypt, you should:

  • When you dump the database containing encrypted columns, you also dump the database where the key was created. This is necessary if new keys have been added since the last dump.

  • When you dump the database containing an encryption key, dump all databases containing columns encrypted with that key. This keeps the encrypted data in sync with the available keys.

  • After loading the database containing the encryption keys and the database containing the encrypted columns, bring both databases online at the same time.

Because of metadata dependencies of encrypted columns on the key’s database, follow the steps below if you intend to load the key database into a database with a different name (if your data is stored in the same database as your keys, you need not follow these steps):
  1. Before dumping the database containing the encrypted columns, use alter table to decrypt the data.

  2. Dump the databases containing keys and encrypted columns.

  3. After loading the databases, use alter table to reencrypt the data with the keys in the newly named database.

The consistency issues between encryption keys and encrypted columns are similar to those for cross-database referential integrity. See Cross-Database Constraints and Loading Databases in the System Administration Guide.