Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Need help DB2 password encryption

    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!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is this for users to connect to the database? Also what DB2 version and OS?

    Andy

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Quote Originally Posted by ARWinner
    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.

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    So, you are trying to secure/encrypt data once it is in the table?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jun 2009
    Posts
    3
    Quote Originally Posted by Cougar8000
    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
    Last edited by ghostlord; 06-24-09 at 15:17.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    He means bit data (it is a typo). Look at the encrypt and decrypt functions in the manual.

    Andy

  8. #8
    Join Date
    Jul 2009
    Posts
    9
    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.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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!
    Last edited by Peter.Vanroose; 07-05-09 at 11:09.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by n_i
    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.
    I completely agree.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Jul 2009
    Posts
    9
    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.

  13. #13
    Join Date
    Mar 2011
    Posts
    1
    Quote Originally Posted by Peter.Vanroose View Post
    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 IBM Redbooks | AIX 5L Version 5.3 Cryptographic Sum Command)

    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!
    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.

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •