Encrypting Data
Go Up to Using isql to Enable and Implement Encryption
Contents
As indicated at the beginning of this chapter, InterBase can be used to encrypt data at the database-level, and to encrypt specific columns in a database. Generally speaking, encrypting at the column-level offers the greatest data protection. When you encrypt at the database- or column-level, you must also encrypt the backup files of the database. For instructions on how to do so, see Encrypting Backup Files.
About the Encryption Commands
InterBase provides two encryption commands: one to encrypt a database, and the other to encrypt database columns.
To encrypt a database, use the following syntax:
ALTER DATABASE ENCRYPT [[with] key-name]
For example, the statement:
ALTER DATABASE ENCRYPT with fin_key
uses the fin_key to encrypt all the database pages in the current database (i.e. in the database to which you are connected).
To encrypt a column in an existing table, use the following syntax:
ALTER TABLE table-name ALTER COLUMN column-name ENCRYPT [[with] key-name]
For example, the following statement:
ALTER TABLE sales ALTER COLUMN total_value ENCRYPT with expenses_key
uses the expenses_key to encrypt data in the total_value column.
To encrypt a column when creating a table, use the following syntax:
CREATE TABLE table-name column-name data-type ENCRYPT [[with] key-name]
Setting a Decrypt Default Value for a Column
When encrypting a column, the database or table owner can specify a decrypt default value that displays when a user who does not have decrypt privileges for that column tries to access the column’s data. If a decrypt default value is not specified, the user will get an error message. A decrypt default value also allows existing reports and applications to run without raising permission exceptions when columns are encrypted.
To specify a decrypt default value, use the following syntax:
create table table-name (column-name data-type encrypt [[with] key_name] [decrypt default value], …)
A decrypt default can be changed or dropped from a column. Note that a decrypt default is not automatically dropped when a column is decrypted.
alter table table-name alter [column] column-name [no] decrypt default value
Encrypting Blob Columns
Blob columns can be encrypted like any other column data type. However, due to their large size, blob encryption can be time-consuming. Typically, a large blob is created before its creator knows which column it will belong to. If the final column destination is encrypted, then the unencrypted blob will need to be re-read and encrypted with the column’s encryption key.
To avoid blob re-encryption overhead, two blob parameter items have been added, and can be passed to isc_blob_create2() to indicate the column to which the blob will be assigned. The items isc_bpb_target_relation_name and isc_bpb_target_field_name denote the column to which the blob will be assigned by the developer. These items are passed via the blob parameter block in the same way that blob filter and character set blob parameter items are sent. The blob parameter byte string includes the following:
- The blob parameter;
- One “length” byte; and
- “Length” bytes for the target name.
isc_blob_gen_bpb() and isc_blob_gen_bpb2() can generate these new blob parameter items if the target blob descriptor argument has both blob_desc_relation_name and blob_desc_field_name string members.
If a blob ID is assigned between two columns with different encryptions, the blob assigned to the destination column is automatically translated between the two encryptions. This means that the source blob is decrypted internally to plaintext and the destination blob is encrypted with the new ciphertext.
The workaround described here also pertains to special cases in which one of the blobs is not encrypted. If an encrypted blob ID is assigned to a blob column with no encryption, the assignment is allowed but a warning error is returned.