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.

                
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.

                
#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).

                
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:

                
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.

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.

Back to top

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.

Back to top

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

• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Get more details from the Informix library .
  
  
• IBM Informix Dynamic Server v10.00 Information Center : Learn more about IDS 10.
  
  
• Stay current with developerWorks technical events and Webcasts.
  
  

Get products and technologies

• Download a free trial version of Informix Dynamic Server.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Manoj 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 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.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• CLE
• Configure encryption VPs
• Built-in SQL functions and statements for CLE
• Use CLE in a dbaccess application
• Use CLE in an ESQL/C application
• Use CLE to set or retrieve hints
• Storage requirements for CLE
• Storage requirements for AES and TDES
• Supported data types
• Conclusion
• Acknowledgments
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers