The following sections describe the considerations for functional encryption.

With the session setting STRING_TRUNCATION IGNORE, a string value that does not fit into the column is silently truncated. While this may be acceptable for normal strings, it causes a problem for encrypted values. The truncated encrypted value cannot be decrypted anymore, and with that the data value effectively is lost. Even with the setting of STRING_TRUNCATION WARN, it is possible that an application or user does not heed the warning, and the data is lost due to the truncation of the encrypted string. Only the setting STRING_TRUNCATION FAIL will make sure that an encrypted value does not get truncated without being noticed. Correct size calculation for columns that store functionally encrypted data therefore is very important.

An index is used to quickly find a specific data row in a large table, e.g. when a where clause in the SQL statement contains a predicate for the table column. If an index contains a column that stores encrypted values, then the encrypted values are also in the index, and likely will not match the predicate value in the where clause. At least not unless the predicate value also would be encrypted in exactly the same way as the column value. In general it probably does not make much sense to include in an index a column that contains encrypted values.

Table keys likely are not functioning if they contain encrypted column values

Similar to indexes, a table key is used to match values from columns of different tables, e.g. for table joins. If the key in one table contains encrypted values, then the matching would work only when also the corresponding column in the other table would contain the values encrypted in exactly the same way. Therefore, it is not advised to include columns with encrypted values in a table key.

As functional encryption is controlled purely by the application, it is necessary that the secret passphrase is supplied in every SQL statement that is to encrypt or decrypt a column value. The handling of such SQL statements therefore must be done very carefully to avoid leaking the secret passphrase, e.g. in application source code, SQL statement trace capabilities in the application and the database server. For example, the SESSION_TRACE session setting includes passphrases for functional encryption in the trace output. This use of this session setting must be considered carefully, and the output of the trace setting should be kept sufficiently secure. Or external postprocessing of the trace output should be applied to obscure the passphrases.

Due to the inherent uniqueness of encrypted data, it cannot be compressed with lossless compression methods. (Lossy compression cannot be applied, because it would render the encrypted data to be no longer decryptable.) This not only affects the data stored on disk in table files, but also the backups of the data, including output generated by performing a checkpoint with the ckpdb utility. The increased backup data size not only requires more permanent storage space, but also increases the transfer time of the data to that storage during the backup creation as well as during a restore. This can cause a substantial slowdown, e.g. when backing up to (or restoring from) cloud storage.