Hi guys,

I am a newbie to DB2.
My current project need some sort of encryption on user login password in user table to prevent from anybody viewing it even for the DB2 administrator.
Is there a password "type" in DB2 so that the persist password can be encrypted automatically?

Any suggestions are appreciated!

Is this for users to connect to the database? Also what DB2 version and OS?

Andy

Thanks for the quick response, andy.
There will be a form for user registration. After user registration, the user input password will be encrypted and stored into the DB2 table.
I am using DB2 9.5 and winXP as a test environment.
On Production server,using DB2 9.X and AIX.

So, you are trying to secure/encrypt data once it is in the table?

Create new table with that column defined for nit data

create new view that has that column defined with DECRYPT_CHAR(decrypt_phraze)

Create instead of triggers. 1 for insert and 1 for update.

revoke all access from syscat.views to make sure no one finds your decrypt_phraze

What is nit data? Please advise
Sorry, would you mind to elaborate it, I am a newbie to DB2.
Thanks again

He means bit data (it is a typo). Look at the encrypt and decrypt functions in the manual.

Andy

Expect this way of implementation would slow down your database quite a bit. Non persistent database view is very slow on queries. If you have 1,000,000 records in the table, the decrypt function will have to be invoked 1,000,000 times. Persistent view, yes it solves performance but as the 'encrypted' columns are actually stored decrypted, it's insecure. Adding triggers would complicate your application making it hard to debug/trace issues. Check out storage/file encryption products in the market.

If I understand your use case well, all you need is a one-way function.
Typically, for this purpose, you want to "hash" the entered password, store the hashed form in DB2, then on validation compare the two hashed forms (stored versus newly entered and hashed) to find out whether the given password matches the one stored in the database.

See, e.g., One-way encryption then click on Cryptographic hash function and read the paragraph "Applications" --> "password verification".

An often used one-way function for this purpose is MD5: see wiki/MD5

See e.g. DB2 Issue on how to create a User-Defined Function (UDF) which implements MD5 to be used in DB2.
Alternatively, you may use an MD5 conversion command of the OS (that is, AIX in your case): most likely it is called "crypt". Otherwise look for commands "csum" or "sum". (See http://www.redbooks.ibm.com/abstracts/tips0472.html)

Don't use the DB2 encrypt() function for this purpose, since this is not a one-way function: decrypt() will make the encrypted form of the password readable again!

I think the idea is to encrypt the password on the application side, so by the time it reaches the database it's already encrypted. In that case a UDF won't be of much help. However, if Mr. ghostlord is OK with relying on DB2 for encryption, here's a Java UDF too: datori Top 5 SQL statements

I completely agree.

If you just need to authenticate a user, agree with Peter that a simple one-way hash would be good enough. That means when user submits a pin to sign on, your application calculates a digest value and match it with the database record. But remember as hash is irreversible, if ever user forgets his/her pin, you have no way to tell from the application. Instead you have to setup pin reset functions to save the new hashed pin to db and inform your user of the newly reset pin.

while inserting a password the bytearray looks like [91, 77, 48, 6, -120, -15, -100, -113, -42, 91, -115, 108, -49, -104, -32, -82] and while authenticating the same user the password in the form of bytearray looks like [91, 77, 48, 6, -120, -15, -100, -113, -42, 91, -115, 108, -49, -104, -32, -82, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32, 32] and the authentication is failing.Can anybody let me know where i'm going wrong.

In SQL terms, these two are identical, since decimal 32 is a blank.
Since only the first 16 bytes are relevant, using
SUBSTR(..., 1, 16) = SUBSTR(..., 1, 16)
in your comparison instead of ... = ...
should solve this problem.