Put Your Sensitive DB2 Data Under Lock and Key

Put Your Sensitive DB2 Data Under Lock and Key

ackers are trying to find some way to get into your database. If you think they’re not, you’re just deluding yourself. The news stories about compromised credit card numbers are proof that the best way to lose face with your customers is to let a hacker steal their sensitive data. Customers want their credit card numbers, medical histories, etc. kept under lock and key. Go figure!

You can use technologies such as Secure Sockets Layer (SSL) and Secure HTTP to securely transport confidential user information across network connections (i.e., in transit). But how do you go about storing your customers’ confidential data securely? Sadly, in many large organizations, you can’t even trust your own staff to have access to such confidential information.

IBM DB2 Universal Database offers a solution. Since version 7.2, it has provided built-in facilities for encrypting the data you persist. Using this encryption and decryption functionality is a straightforward process that you can employ to safeguard sensitive data. In particular, this tutorial demonstrates how to use DB2’s ENCRYPT, DECRYPT_BIN, DECRYPT_CHAR, and GETHINT functions.

How do you store your customers’ confidential data securely when hackers are constantly trying break into your DB2 database?

Use DB2’s built-in functions (ENCRYPT, DECRYPT_BIN, DECRYPT_CHAR, and GETHINT) to encrypt the data you persist.

Create a Sample DB2 Database
Start off by hitting the DB2 Command Line Processor and creating a database to experiment with:

db2 => connect to cryptodb user db2admin using db2admin

Next, create a table where you will store some sensitive data:

db2 => create table accounts(accountnum varchar(64) for bit data not null primary
  key , firstname varchar(30) not null, lastname varchar(30) not null)

The input to ENCRYPT can be either CHAR or VARCHAR, and it can also be FOR BIT DATA. Also, notice the use of the “for bit data” clause. You need such a column type to be able to store encrypted data values.

Insert Some Encrypted Data
In your table, you might deem that data added to the accountnum column needs to be encrypted since it is sensitive.

In the following syntax, you use the encrypt scalar function to encrypt the accountnum column:

insert into accounts values ( encrypt('1234567890123456', 'encryptpwd'), 'Kulvir', 'Bhogal')

The first argument in your encrypt clause is the value you want to encrypt. The second argument is the password that you must use if you want to pull the inserted data from the database. Encryption passwords must be between six and 127 bytes.

Figure 1 shows the results of a feeble attempt to retrieve the account number data you just inserted without specifying a password.

Click to enlarge
Figure 1: Attempt to Retrieve Encrypted Column Contents Without a Password

To retrieve the account number, you must specify the password you supplied during encryption:

select decrypt_char(accountnum, 'encryptpwd') from accounts where firstname  = 'Kulvir'

Figure 2 shows the effective retrieval of the data using this syntax.

Click to enlarge
Figure 2: Retrieval of Encrypted Column Contents with the Correct Password

Specifying the encryption password every time you need to encrypt and decrypt can be an annoyingly repetitive process. Alternatively, you can use the following syntax:


Here, you set the password for subsequent uses of the built-in functions: ENCRYPT, DECRYPT_BIN, and DECRYPT_CHAR.

After issuing the statement above, you could go about retrieving the accountnum data without having to specify the password you used when inserting the record:

select decrypt_char(accountnum) from accounts where firstname = 'Kulvir'

Darn My Amnesia! I Forgot my Password.
What happens when you insert your data in an encrypted form and then forget the password that you used for encryption? In short, you are out of luck. However, DB2 provides some built-in safeguard for retrieving your password. For example, you can provide a password hint during encryption. The password hint is a phrase that will jog the data owner’s memory. The valid length of a password hint is anywhere from 0 and 32 bytes.

If, for example, you want to associate the password hint of “alma mater mascot” for a password of “longhorn,” you would specify your hint at insertion:

insert into accounts values ( encrypt('1234567890123456', 'longhorn',
  'alma mater mascot'), 'Kulvir', 'Bhogal')

To retrieve the hint, you can issue the following statement:

select GETHINT(accountnum) from accounts where firstname='Kulvir'

The hint also might be programmatically provided so the user can deduce the correct password.

What’s Under the Hood?
DB2 uses the RC2 block cipher with padding encryption algorithm. You can learn about this algorithm at RSA Security’s site, which also offers an excellent primer for cryptography in general.

Performance Impact and Size Considerations
In terms of system performance, encryption and the subsequent decryption does not come without a price. It takes time to perform these operations. Consequently, you should benchmark your application to get an accurate measurement of just how much of an impact encryption has on your database setup.

You also need to account for the size impact of encryption when you define the size of your columns. The rules for encryption column lengths vary depending on whether the data you are encrypting has a hint or not. Table 1 presents the rules of thumb as defined by the DB2 Information Center.

Protect Sensitive Data with DB2
In this 10-Minute Solution, you learned how to use the DB2 built-in functions for encryption and decryption of data. Security hacks are a nightmarish reality with which enterprises must deal. The data encryption features offered by DB2 are weapons that you can use to protect your customers’ and your own sensitive data.


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist