Using column-level data encryption to secure credit card data

About this task

The following example uses column-level encryption to secure credit card data.

To use column-level data encryption to secure credit card data:

Procedure

  1. Create the table: create table customer (id char(30), creditcard lvarchar(67));
  2. Insert the encryption data:
    1. Set session password: SET ENCRYPTION PASSWORD "credit card number is encrypted";
    2. Encrypt data.
          INSERT INTO customer VALUES
("Alice",  encrypt_aes("1234567890123456"));
    INSERT INTO customer VALUES
("Bob", encrypt_aes("2345678901234567"));
  3. Query encryption data with decryption function.
        SET ENCRYPTION PASSWORD "credit card number is encrypted";
    SELECT id FROM customer 
       WHERE DECRYPT_CHAR(creditcard) = "2345678901234567";

Results

Important: Encrypted data values occupy more storage space than the corresponding unencrypted data. A column whose width is sufficient to store plain text might need to be increased before it can support column-level encryption or cell-level encryption. If you attempt to insert an encrypted value into a column whose declared width is shorter than the encrypted string, the column stores a truncated value that cannot be decrypted.

For more information on encryption security, see HCL OneDB™ Administrator's Guide.

For more information on the syntax and storage requirements of built-in encryption and decryption functions, see HCL OneDB Guide to SQL: Syntax.