Browse DevX
Sign up for e-mail newsletters from DevX


Put Your Sensitive DB2 Data Under Lock and Key-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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'

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date