dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

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


advertisement

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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:

SET ENCRYPTION PASSWORD = 'encryptpwd'

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.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date