Security Implementation and Usage | Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows

This section describes how to use new functions available in IBM DB2 Universal Database (for UNIX and Windows) to integrate data encryption easily into database applications.

For years , databases have been able to keep unauthorized persons from being able to see the data. This is generally covered by privileges and authorities within the database manager. In today's environments, there is an increasing need for privacy of stored data. This means that, even though a DBA may have complete access to the data in a table, there is information that the owner of the data would not want anyone else to see. This has surfaced in particular with Web-based applications where the user has entered data (such as credit card numbers ) that is to be kept for subsequent uses of the application by the same user. People want assurance that nobody else can access this data.

This functionality allows the application to encrypt and decrypt data. When data is inserted into the database, it can be encrypted using an encryption password supplied by the user. When the data is retrieved, the same password must be supplied to decrypt the data. For situations where the same password is going to be used several times, the ENCRYPTION PASSWORD value can be set using an assignment statement and is valid for the length of a connection.

The following describes the SQL functions and gives some examples of how the encryption functions could be used. We also discuss the design and performance implications of having encrypted data in a relational database.

To ensure that you are using correct data types and lengths for encrypted data, be sure to read the "Table Column Definition" section under the ENCRYPT function in the SQL Reference.

Encrypt (data-string-expression, password-string-expression, and hint-string-expression).
Decrypt_Bin (encrypted-data, password-string-expression).
Decrypt_Char (encrypted-data, password-string-expression).

The algorithm used to encrypt the data is an RC2 block cipher with padding. The 128-bit secret key is derived from the password using an MD2 message digest. The encryption password is not tied to DB2 authentication and is used for data encryption and decryption only.

An optional parameter, hint-password-expression, can be provided and is a string that would help a user remember the password-string-expression that is used to encrypt the data (for example, "Dwaine and Tom" as a hint to remember "DNTTEAM").

Column-Level Encryption (CLE)

CLE means that all values in a given column are encrypted with the same password. This type of encryption can be used in views and when one common password is used. When the same key is used for all of the rows in a table or tables, the ENCRYPTION PASSWORD special register can be quite useful.

Example 1

This example uses the ENCRYPTION PASSWORD value to hold the encryption password. An employee social security number is encrypted and stored in the EMP table in encrypted form.

  -- Create emp table, set encryption password, and insert three rows:   create table emp (ssn varchar(124) for bit data);   set encryption password ='DNTTEAM';   insert into emp (ssn) values(encrypt('111-11-1111'));   insert into emp (ssn) values(encrypt('222-22-2222'));   insert into emp (ssn) values(encrypt('333-33-3333'));   -- Select data from emp table:   select ssn from emp   SSN   -----------------------------------------------------------------   x'0010F0FF0333D5A034E989260E4F99ED59070DD69B6E3C1B'   x'005EA2FF0333D5A05F167D1BC1E9EAD33D7D4987B57D5670'   x'00EE12FF0333D5A06174133D8E3A22756382F84B48F4DD05'   -- Set encryption password:   set encryption password ='DNTTEAM';   select decrypt_char(ssn) as ssn from emp;   SSN   -----------   111-11-1111   222-22-2222   333-33-3333

Example 2

This example uses the ENCRYPTION PASSWORD value to hold the encryption password in combination with views. The following statement declares a view on the EMP table:

  create view clear_ssn (ssn) as   select decrypt_char(ssn) from emp;

In the application code, we set the ENCRYPTION PASSWORD to DNTTEAM and can now use the CLEAR_SSN view.

  select ssn from clear_ssn;   When there is no encryption password, the result encrypted   x'0010F0FFE404A0D534E989260E4F99ED59070DD69B6E3C1B   set encryption password ='DNTTEAM';   select ssn from clear_ssn;   When encryption password is set, the result should be:  111-11-1111

Row-Column ( Cell ) or Set-Column Level Encryption (SCLE)

SCLE means that, within a column of encrypted data, many different passwords are used. For example, a Web site may need to keep customer credit card numbers (CCN). In this database, each customer could use his or her own password or phrase used to encrypt the CCN.

Example 1

The Web application collects user information about a customer. This information includes the customer name , which is stored in host variable V_NAME, the credit card number, which is stored in a host variable V_CCN, and the password, which is stored in a host variable V_USERPSWD. The application performs the insert of this customer information as follows :

  create table customer (name char(20), ccn varchar(124) for bit data);   Application...   CREATE PROCEDURE insertCcn (IN v_cnn char(124), IN v_userpswd char(128), v_name char(20))   RESULT SETS 1   LANGUAGE SQL   Insert_Ccn:   BEGIN NOT ATOMIC   Declare SQLCODE Integer Default 0;   Declare retCode Integer Default 0;   BEGIN   Insert into customer (ccn, name) values(encrypt(v_ccn, v_userpswd), v_name);   If ( retCode <> 0 ) Then   Return -200;   END IF;   END;   END Insert_Ccn   /   call v8inst.inCcn('123456789','DNTTEAM','TPHAN');   or   insert into customer (ccn, name)   values(encrypt('123456789','DNTTEAM'),'TPHAN');

When the application needs to re-display the credit card information for a customer, the password is entered by the customer and again stored in host variable V_USERPSWD. The CCN can then be retrieved as follows:

  Application...   CREATE PROCEDURE getCcn (IN v_userpswd char(128), v_name char(20))   RESULT SETS 1   LANGUAGE SQL   Get_Ccn:   BEGIN NOT ATOMIC   Declare SQLCODE Integer Default 0;   Declare retCode Integer Default 0;   BEGIN   DECLARE Cgetccn CURSOR WITH RETURN FOR   Select decrypt_char(ccn,  v_userpswd) from customer where name = v_name;   Declare Continue Handler For SQLException, SQLWarning   Begin   Set retCode = SQLCODE;   End;   OPEN Cgetccn;   If ( retCode <> 0 ) Then   Return -200;   END IF;   Return 0;   END;   END Get_Ccn   /   ...Application   call v8inst.getCcn ('DNTTEAM','TPHAN');   or   If select without password decrypted:   select * from customer;   NAME          CCN   -------------------------------------------------------------   TPHAN         x'00DD23FF0333D5A2DCDE9395FDA51E4087C244BE694CABEE'   Result from select with decrypted password:   select decrypt_char(ccn,'DNTTEAM') as ccn from customer where name ='TPHAN';   CCN   -------------   123456789   If using the decrypt_char without the encrypted password, the query failed:   select decrypt_char(ccn) from cust where name='DSNOW';   SQL20145N  The decryption function failed. The password used for decryption   does not match the password used to encrypt the data.  SQLSTATE=428FD   To correct the above, using the set encryption password before select statement:   set encryption password='SANFRAN';   select decrypt_char(ccn) as ccn from customer where name ='DSNOW';   CCN   -------------   987654321

Example 2

This example uses the hint to help customers remember their passwords. Using the same application as example 3, the application stores the hint into the host variable V_PSWDHINT. Assume the values 'SANFRAN' for V_USERPSWD and 'MY_O' for V_PSWDHINT. The hint 'MY_O' is stored to help the user DSNOW remember the encryption password of SANFRAN.

  Application...   insert into customer (ccn, name) values(encrypt(v_ccn, v_userpswd,   v_pswdhint), v_name);   ...Application   call v8inst.insertCcn ('987654321','SANFRAN','MY_O','DSNOW');   or   insert into customer (ccn, name)   values(encrypt('987654321','SANFRAN','MY_O'),'DSNOW');

If the customer requests a hint about the password used, the following query is used.

[View full width]

  [View full width] 
  Application...   select gethint(ccn) from customer where name = v_name;   select decrypt_char(ccn, v_userpswd) from customer where name =   v_name;   ...Application   call v8inst.getpwHint ('DSNOW');   or   select gethint(ccn) as ccnhint from customer where name ='DSNOW';   CCNHINT   --------   MY_O   The value for pswdhint is set to 'MY_O' to help the user about the encryption of password  SANFRAN:   select decrypt_char(ccn,'SANFRAN') from customer where name   ='DSNOW';   CCN   -------------   987654321

Encrypting Non-Character Values (ENCV)

ENCV or the encryption of numeric and date/time data types is indirectly supported via casting. By casting non-character SQL types to varchar or char, they can be encrypted.

Example 1

Casting functions used when encrypting and decrypting TIMESTAMP data.

  -- Create a table to store our encrypted value   create table etemp (c1 varchar(124) for bit data);   set encryption password='DNTTEAM';   -- Store encrypted timestamp   insert into etemp values encrypt(char(CURRENT TIMESTAMP));   -- Select and decrypt timestamp   select * from etemp;   1   --------------------------   x'00F30DFF0333D5B1751BD041CD67DE921D711536B4A5E22FB36215BE9B05635   499305'   select timestamp(decrypt_char(c1)) from etemp;   1   --------------------------   2003-01-10-12.34.51.933954

Example 2

Encrypt/decrypt double data.

  set encryption password='DNTTEAM';   insert into etemp values encrypt(char(1.11111002E5));   select double(decrypt_char(c1)) from etemp;   1   ----------------------------------   +1.11111002000000E+005

Performance Considerations

Encryption can slow down SQL statements. Also, encrypted data will have a significant impact on your database design. In general, you want to encrypt a few very sensitive data elements in a schema, such as Social Security numbers, credit card numbers, patient names , account numbers, etc. Some data values are not very good candidates for encryption, for example, Booleans (true and false) or other small sets, such as the integers 1 through 10. These values, along with a column name, may be easy to guess, so you want to decide whether encryption is really useful. It is best to create indexes on encrypted data.

The following scenario illustrates our discussion. Consider a common master-detail schema where one programmer can work on many projects. We will implement column-level encryption on the employee's Social Security number (SSN). In the master table EMP and the detail table EMPPROJECT, the SSN will be stored in encrypted form.

  -- Define Tables   create table emp (ssn varchar(48) for bit data, name varchar(48));   create table empProject( ssn varchar(48) for bit data, projectName   varchar(48));   -- Create indexes   create unique index idxEmp on emp (ssn) include (name);   create index idxEmpPrj on empProject (ssn);   -- Add some data   set encryption password='DNTTEAM';   insert into emp values (encrypt('111-11-1111'),'Advanced Programmer');   insert into emp values (encrypt('222-22-2222'),'Programmer');   insert into empProject values (encrypt('111-11-1111'),'CLS Project');   insert into empProject values (encrypt('222-22-2222'),'CLS Project');   insert into empProject values (encrypt('111-11-1111'),'DB2 UDB Version   8');   -- A. Find the programmers working on CLS Project   select a.name, decrypt_char(a.ssn)   from emp a, empProject b   where a.ssn=b.ssn and b.projectname='CLS Project';   NAME                     2   ----------------------------------------   Advanced Programmer      111-11-1111   Programmer               222-22-2222   -- B.  Build a list of the projects that the programmer with ssn='111-11-1111' is working on   select projectName from empProject where ssn=encrypt('111-11-1111');   PROJECTNAME   ---------------------   DB2 UDB Version 8   CLS Project

The following is an example of how not to write the two queries on the EMP and EMPPROJECT table. Although these queries return the same answers as the above A and B, they also decrypted the SSN for all rows. When the table gets very large the problem can be significant .

  select a.name, decrypt_char(a.ssn)   from emp a, empProject b   where decrypt_char(a.ssn)=decrypt_char(b.ssn) and b.project='CLS   Project';   NAME                             2   ------------------------------------------   Programmer                       222-22-2222   Advanced Programmer              111-11-1111

This would require decryption of every row of the EMP table and each 'CLS Project' row of the EMPPROJECT table to perform the join.

  select projectName from empProject where decrypt_char(ssn)='111-11-   1111';   PROJECTNAME   -----------------   DB2 UDB Version 8   CLS Project

This would require decryption of every row of the EMPPROJECT table.

In summary, using encryption functions in DB2 provides a simple way to encrypt sensitive data. These functions can be used to implement column- and row-column-level encryption. There are some important performance implications that developers should review during design and implementation. Data encryption adds a new tool to be used to hide private data, even from administrative staff.

NOTE

For additional information, refer to Appendix H ”LDAP Integration in DB2 UDB Using Microsoft Active Directory ”and Appendix I ”Tuning DB2 UDB in the IBM LDAP Environment.