Column-level encryption in IDS

Manage information security in DB2 and Informix Dynamic Server

Protect your sensitive data by using the column-level encryption (CLE) feature of IBM Informix® Dynamic Server (IDS) 10 and later.

Share:

Manoj Mohan (manojm@us.ibm.com), IDS Security Software Development Engineer, IBM, Software Group

Manoj MohanManoj Mohan has worked in the IBM-IDS Team as a software developer for five years. His experiences include designing and implementing Pluggable Authentication Module (PAM) and IP V6 support in IDS Server. Manoj was also involved in designing and implementing trusted context support in DB2 for Linux, UNIX, and Windows.



Lynette D. Adayilamuriyil (lynetta@us.ibm.com), IDS Security Software Development Engineer, IBM

Lynette AdayilamuriyilLynette Adayilamuriyil is a software engineer working on IDS for the past nine years. She has vast experience in the kernel and security areas of IDS.



01 November 2007

Also available in Russian

Introduction

Database security is of paramount importance to customers in today's e-commerce world. Enterprises around the world are facing bigger challenges to protect their sensitive data from hackers. Furthermore, a large number of state, federal, and corporate compliance mandates such as, Gramm-Leach-Bliley Act and Health Insurance Portability and Accountability Act (HIPAA), Sarbanes-Oxley, are driving enterprises toward database encryption. CLE is one such technique that enables the encryption of specific columns within a database.

CLE

The IDS 10 release implemented a SQL-based CLE solution that customers could use to improve data confidentiality. This feature provides a built-in SQL interface to the industry's best-regarded symmetric encryption algorithms, like Triple-Data Encryption Standard (TDES) and Advanced Encryption Standard (AES). New built-in SQL functions provide methods for data in columns to be stored in an encrypted format.

IDS CLE does not provide key management, and the task of key management is delegated to the applications or users. With CLE, data is encrypted using the key generated by a password, provided to the built-in SQL encryption functions. Passwords can be set at a session level using the SET ENCRYPTION statement or provided as an input to the encryption functions. Data can be retrieved or modified only when the secret password is provided in the SELECT statements. IDS provides an easy way for the users to remember the passwords by supporting hints. Users can provide a hint when password encryption is set. This hint can later be retrieved by using a built-in SQL function. Passwords and hints are not stored as plain text in any table of the system catalog. The key used in the process is a time-based random value per instance. The database server initializes the key when the server starts. The key is destroyed when the database server shuts down.

IDS uses an encrypt virtual processor (VP) to handle encryption and decryption. The VPCLASS configuration parameter with the encrypt keyword can be used to configure encrypt VPs. If VPCLASS is not configured for encrypt VPs, the database server starts one encrypt VP the first time any encryption or decryption functions defined for CLE are invoked. Encrypt VPs can also be added dynamically using the onmode utility.

Configure encryption VPs

The following is an example of using the onconfig parameter:

VPCLASS encrypt,num=5

The following is an example of using the onmode utility, to add the encryption VP dynamically:

onmode -p 5 encrypt

Built-in SQL functions and statements for CLE

The following is a list of functionalities supported by CLE:

  • ENCRYPT_TDES: The ENCRYPT_TDES function returns a value that is the result of encrypting a character string, or a BLOB or CLOB value, by applying the TDES (which is sometimes also called DES3) algorithm to its first argument.
    ENCRYPT_TDES(data-string-expression [, password-string [, hint ]])
  • ENCRYPT_AES: The ENCRYPT_AES function returns an encrypted value that it derives by applying the AES algorithm to its first argument, which must be an unencrypted character string or a smart large object (that is, a BLOB or CLOB data type).
    ENCRYPT_AES(data-string-expression [, password-string [, hint ]])
  • DECRYPT_CHAR: The DECRYPT_CHAR function accepts as its first argument an encrypted_data character string that can have any character type (CHAR, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR). You must specify a password as its second argument, unless the SET ENCRYPTION statement has specified for this session the same session password by which the first argument was encrypted.
    Decrypt_char(EncryptedData [, Password])
  • DECRYPT_BINARY: The DECRYPT_BINARY function accepts as its first argument an encrypted_data large object of type BLOB or CLOB. You must specify a password as its second argument, unless the SET ENCRYPTION statement has specified as the default for this session the same password by which the first argument was encrypted.
    Decrypt_binary(EncryptedData [, Password])
  • GETHINT: The GETHINT function returns a character string that a previously executed SET ENCRYPTION PASSWORD statement defined for the password that was used when encrypted_data was encrypted by the ENCRYPT_AES function or by the ENCRYPT_TDES function. This hint string typically provides information that helps the user to specify the password needed to return the plain text version of encrypted_data with the DECRYPT_CHAR or DECRYPT_BINARY decryption function.
    GETHINT(EncryptedData)
  • SET ENCRYPTION PASSWORD: Use the SET ENCRYPTION PASSWORD statement to define or reset a session password for encryption and decryption of character, BLOB, or CLOB values. Only IDS supports this statement, which is an extension to the ANSI/ISO standard for SQL. You can use this statement with ESQL/C.
    SET ENCRYPTION PASSWORD  "password"  [ WITH HINT "hint-string" ]

Use CLE in a dbaccess application

The following is an example that illustrates how to use the AES algorithm and decrypt_char features of CLE in a dbaccess application. In this example, the data is encrypted using the AES algorithm along with the password mypassword1 and mypassword2, which are used as the key for encryption. If any other user selects the data from the table, no meaningful data is displaced. However, if a SELECT> statement is performed using a decrypt function, like decrypt_char, along with the appropriate password, then the correct data is displayed.

Listing 1. dbaccess application using AES and decrypt_char
create database test with log;
Database created.

create table tab (custname char(32), ccard char(16), enc_ccard lvarchar(3000));
Table created.

insert into tab values ("Manoj Mohan", "1234567890",
    encrypt_aes("1234567890", "mypassword1"));
1 row(s) inserted.

insert into tab values ("Lynette Daniel", "2345678901",
    encrypt_aes("2345678901", "mypassword2"));
1 row(s) inserted.

select * from tab;

custname   Manoj Mohan
ccard      1234567890
enc_ccard  0gH//AAAAEAWnTjoJu721chtLzYumGZUshGnfI2elmV

custname   Lynette Daniel
ccard      2345678901
enc_ccard  0Sbb/AAAAEAy4rjGrXe6wqETDb3d/thwySlcjVWYttH

2 row(s) retrieved.

select custname, ccard, decrypt_char(enc_ccard, "mypassword1")
    as clear_ccard from tab where custname="Manoj Mohan";

custname     Manoj Mohan
ccard        1234567890
clear_ccard  1234567890

1 row(s) retrieved.

select custname, ccard, decrypt_char(enc_ccard, "mypassword2")
    as clear_ccard from tab where custname="Lynette Daniel";

custname     Lynette Daniel
ccard        2345678901
clear_ccard  2345678901

1 row(s) retrieved.

Database closed.

Use CLE in an ESQL/C application

CLE can also be used in an ESQL/C application. The following is an example that illustrates the use of the TDES algorithm and the decrypt_binary feature of CLE.

Listing 2. ESQL/C application using TDES and decrypt_binary
#include <stdio.h>
#include <decimal.h>
#include <datetime.h>
EXEC SQL include sqlda;
EXEC SQL include sqlca;
EXEC SQL include sqltypes;

main()
{
EXEC SQL BEGIN DECLARE SECTION;
int id;
lvarchar a[3000], b[3000];
EXEC SQL END DECLARE SECTION;

EXEC SQL drop database test;
printf("SQLCODE %d\n", SQLCODE);
EXEC SQL create database test with log;
printf("SQLCODE %d\n", SQLCODE);
EXEC SQL create table tab (id int, credit_card lvarchar(3000),
    encrypted_credit_card lvarchar(3000));
printf("SQLCODE %d\n", SQLCODE);
EXEC SQL insert into tab values (1, "1234567890",
    encrypt_tdes("1234567890", "mypassword"));
printf("SQLCODE %d\n", SQLCODE);
EXEC SQL select id, credit_card, decrypt_binary(encrypted_credit_card, "mypassword")
    into :id, :a, :b from tab;
printf("SQLCODE %d\n", SQLCODE);
printf("credit_card: %s, enc_credit_card %s\n", a, b);
}

Use CLE to set or retrieve hints

CLE also supports hints, which can be stored along with the password. Furthermore, a user can set the password of any session using the SET ENCRYPTION PASSWORD syntax. This enables the customers to issue INSERT or SELECT statements without specifying the password (in the encrypt/decrypt functions).

Listing 3. Example of using CLE to set or retrieve hints
create database test with log;
Database created.

create table tab (custname char(32), ccard char(16), enc_ccard lvarchar(3000));
Table created.

set encryption password "VJTICOLLEGEMUMBAI" with hint "what is my PG College name?";
Encryption password set.

insert into tab values ("Manoj Mohan", "1234567890", encrypt_aes("1234567890"));
1 row(s) inserted.

select * from tab;

custname   Manoj Mohan
ccard      1234567890
enc_ccard  0fIMgAAAAEAeliIJN4liR4qPCFw9//mIyq82berEiifNLgbd3vW23CVUzI3KSCy4kYQF
91PRuayfa3WpBpbTDQ=

1 row(s) retrieved.

select custname, gethint(enc_ccard) as hint from tab where custname="Manoj Mohan";

custname  Manoj Mohan
hint      what is my PG College name?

1 row(s) retrieved.

set encryption password "SOMAIYACOLLEGEMUMBAI" with hint "what is my Eng College name?";
Encryption password set.

insert into tab values ("Lynette Daniel", "2345678901", encrypt_aes("2345678901"));
1 row(s) inserted.

select custname, gethint(enc_ccard) as hint from tab where custname="Lynette Daniel";

custname  Lynette Daniel
hint      what is my Eng College name?

1 row(s) retrieved.

Storage requirements for CLE

The encrypted data are stored in character columns in IDS and need more space than unencrypted data. For instance, if the input data are N bytes long, then for AES cipher the storage requirement is as follow:

Listing 4. Example of storage requirements for CLE
AES	 = B64(NGM(N, 16) + H + 8) + 11
Where,
H = 0 with no hint; H = 40 with hint.
NGM(x,y): Next multiple of y that is greater than x = ((x + y)/ y) * y
B64(x)  : Base-64 encoding size = ((x + 2) / 3) * 4

Similarly, the storage requirement for the TDES cipher can be determined using the following formula:

TDES	 = B64(NGM(N, 8) + H + 8) + 11
Where,
H = 0 with no hint; H = 40 with hint.
NGM(x,y) : Next multiple of y that is greater than x = ((x + y) / y) * y
B64(x)   : Base-64 encoding size = ((x + 2) / 3) * 4

Storage requirements for AES and TDES

The following table illustrates the storage requirement for AES and TDES cipher using the above formulas.

Table. 1 Storage requirements for AES and TDES ciphers
Input Size (bytes)TDES (No Hint)AES (No Hint)TDES (With Hint)AES (With Hint)
1..735438799
8..1543439999
16..235567107119
24..316767119119
32..397587131139
40..478787139139
100163171215227
200299299355355
500695707747759

Note that the performance impact of encryption is significant. Therefore, it is recommended that it should be used for a business need or to adhere to a federal or legal requirement.


Supported data types

All built-in data types except BYTE and TEXT can be encrypted or decrypted using CLE. These include, CHAR, VARCHAR, NCHAR, NVARCHAR, LVARCHAR,SMALLINT, INTEGER, INT8, DECIMAL, SMALLFLOAT, FLOAT,DATE, DATETIME, INTERVAL, BOOLEAN, BLOB, and CLOB. Encrypted data is stored in CHAR, VARCHAR, or LVARCHAR columns. Encrypted BLOBs and CLOBs are stored in BLOB columns.

Complex data types like Collection and Row types can be encrypted too, but they require explicit cast. For example:

ENCRYPT_AES(LIST{1,3,2}::LVARCHAR, 'PassWord')
DECRYPT_CHAR(enc_list, 'PassWord')::LIST(INT NOT NULL)

User-defined DISTINCT types are treated same as base types. User-defined opaque require explicit cast to or from LVARCHAR

Conclusion

The CLE feature provides a built-in SQL interface to the industry's best-regarded symmetric encryption algorithms, like Triple-DES and AES. In this article, you saw an overview of how customers can use CLE to improve database security and provide data confidentiality and integrity.


Acknowledgments

The authors would like to thank their architect Jonathan Leffler for sharing his presentation on CLE that was quite helpful during the formation of this article.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=266026
ArticleTitle=Column-level encryption in IDS
publish-date=11012007