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
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.
|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.
|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:
SET ENCRYPTION PASSWORD = 'encryptpwd'
Here, you set the password for subsequent uses of the built-in functions:
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'