Understanding Salt
The NOSALT column-level option overrides the default (SALT), which adds 16 bytes of random bits to data before encryption and strips them out after decryption.
To understand salt, consider this example. A medical database contains a column indicating the status for each patient: whether they have had a particular medical test and, if so, whether they tested positive or negative for the condition. A value of Y indicates a positive result, N indicates a negative result, and NULL that the test has not been done. This data must be keep confidential. If the column is encrypted, has the patient information been protected?
If SALT is used the data is indeed protected. But if NOSALT is specified, the data is essentially unprotected. Given identical input and an identical key, encryption is repeatable. If Y is encrypted again and again with the same AES key, the encrypted representation of each value is the same.
Someone working to "crack" the database can correlate the repeated patterns with the easily learned frequency of positive and negative test results in the general population. The secret is revealed without needing to crack the actual AES encryption. The problem is that hiding the values Y and N does not hide the information that Y and N represent.
If salt is added to the encryption, every row contains, in the indicator column, a different encrypted value. Because the null indicator is included in the encryption protection, it is impossible to know who has been tested or who has tested positive for the condition. In fact, no statistical information can be gleaned. Without the passphrase, which protects the AES key for the table, the medical test result column appears to contain nothing but meaningless binary noise, which is the goal of encryption.
But consider another column in the medical database that contains values that do not repeat: the U.S. Social Security number of the patient. The encrypted values in the column will be distinct, regardless of whether SALT or NOSALT is specified. (Furthermore, the values are distinct from the same Social Security number stored encrypted in another table, because the other table has a different AES key, even if the tables are protected with the same passphrase.)
In the case of Social Security numbers, NOSALT still yields apparent randomness in the encrypted values. The only information leaked is the fact that Social Security numbers are different for each patient, which is common knowledge.
Using SALT (the default):
• Provides further protection of the encrypted data.
• Hides column value repetitions (for example, Y, N, null).
• Provides a unique encrypted representation of each value (because a different, random, salt prefix is generated for each encryption operation). SALT renders the encryption non-repeatable.
NOSALT can be used:
• When you want to index a table on the encrypted value of the column
• When the column has values that are unique before encryption
• To use less disk storage. Salt adds one AES block (16 bytes) to the column.
Note: The cost is a small price to pay for better protection, considering the relatively low cost of storage.