User db passwords



  • A few months ago, everyone had to convert all db password mechanisms to use encrypted passwords. The work was done.

    Now that we're SDLC compliant, apparently, user password changes need to be audited as well.

    There was just an email - across all development, QA and mangement teams, describing the layout and use of the new password-change audit table:

    DB Table DbLoginPwdChangeAudit
       Column Name        Column Type  Nullable  Column Comment
       -----------        -----------  --------  --------------
       skey               NUMBER(38)   not null  unique PK
       loginName          VARCHAR2(32) not null  db user's login name
       loginPwdChangeDate TIMESTAMP    not null  the last time the user changed their password
       encryptedPassword  VARCHAR2(64) not null  the encrypted password they changed it TO
       decryptedPassword  VARCHAR2(32) not null  the unencrypted password they changed it TO
                                                 (for human-readable auditing)
       ...
    

    As long as we have encrypted passwords.



  • @snoofle said:

    A few months ago, everyone had to convert all db password mechanisms to use encrypted passwords. The work was done.

    Now that we're SDLC compliant, apparently, user password changes need to be audited as well.

    There was just an email - across all development, QA and mangement teams, describing the layout and use of the new password-change audit table:

    DB Table DbLoginPwdChangeAudit
       Column Name        Column Type  Nullable  Column Comment
       -----------        -----------  --------  --------------
       skey               NUMBER(38)   not null  unique PK
       loginName          VARCHAR2(32) not null  db user's login name
       loginPwdChangeDate TIMESTAMP    not null  the last time the user changed their password
       encryptedPassword  VARCHAR2(64) not null  the encrypted password they changed it TO
       decryptedPassword  VARCHAR2(32) not null  the unencrypted password they changed it TO
                                                 (for human-readable auditing)
       ...
    

    As long as we have encrypted passwords.



    This one is below industry standards. The audit was effective, if only temporarily.

     



  • You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.



  • @locallunatic said:

    You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.

    Easy. Call them securePassword and insecurePassword... and then wait for management to complain about the name because it sounds like an invitation to hackers.

     



  • Great example of a locked iron clad door right next to an open window (with a giant sign pointing to it).



  • @locallunatic said:

    You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.

    Column description: For hacker's convience

    21 characters.

    As for my password:

    IH@x0redYou!!11

    15 characters


  • BINNED

    @Anketam said:

    Great example of a locked iron clad door right next to an open window (with a giant sign pointing to it).

    I'm not sure about the locked iron clad part. That would depend on how they encrypted the passwords.



  • @PedanticCurmudgeon said:

    @Anketam said:

    Great example of a locked iron clad door right next to an open window (with a giant sign pointing to it).

    I'm not sure about the locked iron clad part. That would depend on how they encrypted the passwords.
    Aye, I was making a poor assumption there.  It could be a paper thin wood door.  I will have to defer to Snoofle if their encryption is actually real encryption.

  • Trolleybus Mechanic

    @locallunatic said:

    You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.
     

    Fly’s open. Junk visible. FIXIT


  • @Lorne Kates said:

    Fly’s open. Junk visible. FIXIT
     

    Can't reproduce.


  • Trolleybus Mechanic

    @TehFreek said:

    @locallunatic said:

    You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.

    Easy. Call them securePassword and insecurePassword... and then wait for management to complain about the name because it sounds like an invitation to hackers.

     

     

    Damn fucking straight. That column should be more secure. It should be called  notPassword or drowssaPerucesni

     



  • @dhromed said:

    @Lorne Kates said:
    Fly’s open. Junk visible. FIXIT
     Can't reproduce.
    That is what happens when you are on birth control



  • @locallunatic said:

    You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.

    "WTF?!"


  •  @Anketam said:

    @PedanticCurmudgeon said:

    @Anketam said:

    Great example of a locked iron clad door right next to an open window (with a giant sign pointing to it).

    I'm not sure about the locked iron clad part. That would depend on how they encrypted the passwords.
    Aye, I was making a poor assumption there.  It could be a paper thin wood door.  I will have to defer to Snoofle if their encryption is actually real encryption.
    The encryption is some standard encryption library, so it's probably reasonably done. Of course, everyone uses the decrypt routine to figure out the passwords so they can connect to the db at the command line, and the decrypt routine is right there is source control, so...


  • @snoofle said:

    The encryption is some standard encryption library, so it's probably reasonably done. Of course, everyone uses the decrypt routine to figure out the passwords so they can connect to the db at the command line, and the decrypt routine is right there is source control, so...
    No, it's not reasonably done. Since, as it seems, the encryption is reversible, as is to be expected from any encryption. That is not how passwords are supposed to be treated. They are supposed to be (one-way-) hashed (with salt, please) instead. Encryption is intended to be reversible, via decryption, but in case of passwords, that is totally undesired. At least if you want to remain on the safe side of the fence and don't want to assume the responsibility for the passwords of your users.



  • @snoofle said:

    The encryption is some standard encryption library, so it's probably reasonably done. Of course, everyone uses the decrypt routine to figure out the passwords so they can connect to the db at the command line, and the decrypt routine is right there is source control, so...
     

    So at best it's a sturdy iron door with a torch-proof deadbolt, but the key is hidden under the doormat.



  • @TheRider said:

    @snoofle said:
    The encryption is some standard encryption library, so it's probably reasonably done. Of course, everyone uses the decrypt routine to figure out the passwords so they can connect to the db at the command line, and the decrypt routine is right there is source control, so...
    No, it's not reasonably done. Since, as it seems, the encryption is reversible, as is to be expected from any encryption. That is not how passwords are supposed to be treated. They are supposed to be (one-way-) hashed (with salt, please) instead. Encryption is intended to be reversible, via decryption, but in case of passwords, that is totally undesired. At least if you want to remain on the safe side of the fence and don't want to assume the responsibility for the passwords of your users.
    +1

    Very unreasonably done. Very.

    TRWTF, is NOT the cleartext password column. 

    However, without the cleartext column, it does meet industry standards. :-)



  • @TheRider said:

    wisdom.
    Fair enough.Then the fact that we now have encrypted passwords, which passed yet another (prior) audit means that this is a WTF compounding an existing WTF.

    Maybe it IS time to go looking for another job...

     



  • @snoofle said:

    Then the fact that we now have encrypted passwords,
     

    Is the requirement "password must be encrypted" or "you have encrypted passwords"...?

    Seems having encrypted passwords is of no value if the decrypted versions are readily available. Kinda "the door has a lock, just that we didn't turn the key on the door" situation.


  • BINNED

    Just when we thought the door analogy hadn't quite been done to death...



  • Honestly I think it takes a genius to think of that audit table. What are they doing with the decrypted passwords? Are they manually checking if it meets password requirements? At least: one lower case, one uppercase, one number, and one special character?



  • @snoofle said:

    Maybe it IS time to go looking for another job...

     


    Please don't.

    I have few constants in my life. One of those precious few is that snoofle will post something about his work environment to The Daily WTF that makes mine look less miserable. Finding such a target rich environment again would be exceptionally unlikely.

    Yes, I work for the government.

    MArk B.



  • @SteamBoat said:

    Yes, I work for the Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:"";mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin:0in;mso-para-margin-bottom:.0001pt;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:"Times New Roman";mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;}government.

    That's hilarious.



  • @snoofle said:

    A few months ago, everyone had to convert all db password mechanisms to use encrypted passwords. The work was done.

    Now that we're SDLC compliant, apparently, user password changes need to be audited as well.

    There was just an email - across all development, QA and mangement teams, describing the layout and use of the new password-change audit table:

    DB Table DbLoginPwdChangeAudit
       Column Name        Column Type  Nullable  Column Comment
       -----------        -----------  --------  --------------
       skey               NUMBER(38)   not null  unique PK
       loginName          VARCHAR2(32) not null  db user's login name
       loginPwdChangeDate TIMESTAMP    not null  the last time the user changed their password
       encryptedPassword  VARCHAR2(64) not null  the encrypted password they changed it TO
       decryptedPassword  VARCHAR2(32) not null  the unencrypted password they changed it TO
                                                 (for human-readable auditing)
       ...
    

    As long as we have encrypted passwords.

     

    Provided the column is only used for auditing, this may not be a WTF. For example, the procedures that drive this table might only be present in the dev environment, or they might check their current environment and null out decryptedPassword if running in prod.

    Given that we're talking about snoofle, this level of sophistication seems unlikely.

     



  • @snoofle said:

    @locallunatic said:

    You should record a note about how this is such a bad idea in the unencrypted column, though I'm not sure how to properly explain it in 32 characters.

    "WTF?!"
     

    Not bad! "WTF?" explains it in 32 BITS!

     

     



  •  So let's see if I understand this right. The password is encrypted and stored in encryptedPassword, the key it's encrypted with is stored in skey (as a NUMBER, wtf?), the password in plaintext is stored in decryptedPassword and you limit passwords to 32 chars.



  • @OhNoDevelopment said:

    Honestly I think it takes a genius to think of that audit table. What are they doing with the decrypted passwords? Are they manually checking if it meets password requirements? At least: one lower case, one uppercase, one number, and one special character?

    They're probably preparing for the future with the goal of checking whether people are changing their password in a simple sequence (mypass1, mypass2, mypass3, ...). Because the idea of enforcing strong passwords via an intelligent entropy checker and then letting people go for more than a month without changing them is probably heresy in Snoofle's workplace.



  • @PedanticCurmudgeon said:

    Just when we thought the door analogy hadn't quite been done to death...
     

    I could have specified a car door. And found a stick-figure cartoon to illustrate my point.

    But I didn't.

    @snoofle said:

    the fact that we now have encrypted passwords, which passed yet another (prior) audit means that this is a WTF compounding an existing WTF.
     

    (I'd typed another response, but the WTF wireless in this resturant gobbled it:)

    What would happen if the passwords didn't synch up?

    Just thinking of manually tweaking your plain-text password to something like "I/love/GoatSex!" then ringing the service desk (on speakperphone) and complaining you couldn't login...



  • @Cassidy said:

    @PedanticCurmudgeon said:
    Just when we thought the door analogy hadn't quite been done to death...
     

    I could have specified a car door. And found a stick-figure cartoon to illustrate my point.

    But I didn't.

    And I can make a door analogy about people making door analogies.



  •  I can make a door analogy that shuts the door on all door metaphors.


  • BINNED

    Sure, but at this point it's basically locking the barn door after the horses have gone.



  • Snoofle: your post really made my friday evening (Yes, it's already friday for me).

     

    Continue with the good posts :D

     



  • @Cassidy said:

    @PedanticCurmudgeon said:

    Just when we thought the door analogy hadn't quite been done to death...
     

    I could have specified a car door. And found a stick-figure cartoon to illustrate my point.

    But I didn't.

    "Car analogies are the Rolls Royces of analogies." (source lost)



  • Basically, this.



  • @pjt33 said:

    They're probably preparing for the future with the goal of checking whether people are changing their password in a simple sequence (mypass1, mypass2, mypass3, ...).
     

    If one had to implement this as a business requirement (putting aside for the moment the question of WTFness in the requirement itself), what would be the least brainless / WTF-y way to accomplish it?



  • @sprained said:

    @pjt33 said:

    They're probably preparing for the future with the goal of checking whether people are changing their password in a simple sequence (mypass1, mypass2, mypass3, ...).
     

    If one had to implement this as a business requirement (putting aside for the moment the question of WTFness in the requirement itself), what would be the least brainless / WTF-y way to accomplish it?

     

    Integration with an established authentication mechanism with such features inbuilt  (eg: Active Directory)...?

    I'm guessing there's already libraries/modules/frameworks out there that do this stuff for you. I don't know of them - anyone care to comment?

     



  • Hash password with random salt, store hash and salt side-by-side.

    For future password changes, Hash new password with old salt, see if it matches old hash.



  • @Circuitsoft said:

    @sprained said:

    @pjt33 said:

    They're probably preparing for the future with the goal of checking whether people are changing their password in a simple sequence (mypass1, mypass2, mypass3, ...).
     

    If one had to implement this as a business requirement (putting aside for the moment the question of WTFness in the requirement itself), what would be the least brainless / WTF-y way to accomplish it?

    Hash password with random salt, store hash and salt side-by-side. For future password changes, Hash new password with old salt, see if it matches old hash.

    The hashes would only match if they were doing mypass1 over and over again, not mypass1 -> mypass2 and so on.



  • @sprained said:

    @pjt33 said:

    They're probably preparing for the future with the goal of checking whether people are changing their password in a simple sequence (mypass1, mypass2, mypass3, ...).

    If one had to implement this as a business requirement (putting aside for the moment the question of WTFness in the requirement itself), what would be the least brainless / WTF-y way to accomplish it?


    I'd want to clarify the requirement a bit, but one approach which might fit would be that when the user changes the password you store either the length of the longest common subsequence or the Levenshtein distance between them.


Log in to reply