Implementing column-level encryption

The following steps create a table from which a user who knows the password can retrieve rows that include one column of encrypted data.

1. Create a database table containing at least one column of type BLOB, CLOB, or a character data type of sufficient length to store the encrypted values. For example, the following statement creates a table called customer in which the column creditcard can store encrypted credit card numbers:

   CREATE TABLE customer (id CHAR(20), creditcard CHAR(107));

2. Specify a password (and optional hint) and insert encrypted data:

   SET ENCRYPTION PASSWORD 'credit card number is encrypted' 
      WITH HINT 'Why is this difficult to read?';
   INSERT INTO customer VALUES ('Alice', 
      encrypt_tdes('1234567890123456'));
   INSERT INTO customer VALUES ('Bob',
      encrypt_tdes('2345678901234567'));

3. Query the encrypted data, using a decryption function:

   SELECT id, DECRYPT_CHAR(creditcard, 
      'credit card number is encrypted') FROM customer;

   The following query calls a decryption function in the WHERE clause, using the session password default, rather than an explicit password argument:

   SELECT id FROM customer
      WHERE DECRYPT_CHAR(creditcard) = '2345678901234567';

Column level encryption offers the coding convenience of passing the implicit session password for all rows with encrypted columns, and in multiple encryption and decryption function calls in the same SQL statement. Confidentiality of the data, however, requires users who know the password on encrypted columns to avoid compromising its secrecy. Triggers and UDRs, for example, should always use the session password, rather than explicit password arguments if they invoke the encryption or decryption functions.

The DBSA can manage highly confidential data with column level encryption. Informix® does not, however, prevent users with sufficient privileges from entering data encrypted by some other password into a table whose other rows use the designated column level encryption password.