Question DB user-level password policy implementation OE 11.73

[kasundha]

I'm using _user table to manage user accounts in the system but I'm using application-level password policy to manage user's passwords. I need to implement a password policy in the database. scenarios as follows.

1. for password complexity.
2. password renewal timeframes.
3. password history (cannot use the same password as the previous “n” passwords).
4. account lockout after “n” failed login attempts.
5. one-time use passwords. Etc

 

[Rob Fitzpatrick]

I used to work on a federated application (multiple databases) which, in the past, stored user credentials and attributes in _User in each of the databases, as well as in an application table.

It had all of the authentication rules you described, implemented at the application level. Password changes would be written simultaneously to the application table and to each _User. Similarly, user authentication at logon happened in the application and in each database.

I need to implement a password policy in the database.

The OpenEdge RDBMS has no such functionality. None. But you don't need it to in order to accomplish what you've described.

Note that I said the application I worked on had this design in the past. It is an outdated design. The _User password hashing is old and not fit for purpose today. It uses the old, proprietary encode function.

In the long term, you should look at externalizing authentication, either in an external third-party layer (e.g. AD/AAD/LDAP) or in OpenEdge Authentication Gateway. The application I worked on went the latter route.

One last note: I assume when you say "11.73" you mean 11.7.3. The current update to OpenEdge release 11.7 is 11.7.18, which shipped in August 2023. 11.7.3 (service pack 3 of 11.7) shipped in May 2018. If you are on 11.7.3, you should update to 11.7.18; you are missing hundreds of bug fixes.

Also note that OpenEdge 11.7 will enter the Retired phase of its life cycle in April 2025. The clock is ticking.

 

[TomBascom]

Aside from being ancient and supposedly easy to crack, the other reason that the ENCODE function is inadequate is that it only considers the first 16 characters - so your ability to require long passwords is compromised. (strike out for posterity...)

Also "requirements" #1 & #2 are junk. "Complexity" just forces people to write them on a sticky note and reuse the same password pattern everywhere. Usually using some horrible, easy to guess substitution like leet-speak. Or appending a couple of digits to a simple string - such as "Pa$$word2023". Time limits have the same impact. Both approaches have been thoroughly deprecated by thoughtful security organizations - including Microsoft and the US government. Nobody should be requiring any such things.

Correct-Horse-Battery-Staple is a valiant attempt to address some of that but also ultimately not helpful: Correct Horse Battery Staple Review | Fractional CISO

If you must use passwords, your best protections from password cracking are length and true randomness (true randomness is NEVER a result of users making it up on their own). Nothing else is even remotely useful when it comes to passwords.

 

[Stefan]

... the other reason that the ENCODE function is inadequate is that it only considers the first 16 characters - so your ability to require long passwords is compromised.

I've seen this oddity repeated a few times in the knowledge base - it is not true.

Someone seems to have thought that since the format for the _user._password field is x(16), you can only enter a 16 character password. The plain text password is not stored in _password, the encoded password is, and encode will reduce anything to 16 characters.

ProgressAblDojo

abldojo.services.progress.com

 

[TomBascom]

I wasn't thinking that the plaintext password would be stored, that's a grave sin, but in my mind I was certain that ENCODE() just ignored everything after the 16th character.

I should have tested that

 

[TomBascom]

There are some "interesting" patterns with ENCODE(), for instance:

define variable rr as character no-undo.
define variable ii as integer   no-undo.
define variable jj as integer   no-undo.

do ii = 1 to 10:
  rr = "".
  do jj = 1 to 16:
    rr = rr + chr( random( 32, 126 )).
  end.
  display encode ( rr + rr /* + rr + rr */ ) format "x(30)" with down.
  down.
end.

 

[kasundha]

There are some "interesting" patterns with ENCODE(), for instance:

define variable rr as character no-undo.
define variable ii as integer   no-undo.
define variable jj as integer   no-undo.

do ii = 1 to 10:
  rr = "".
  do jj = 1 to 16:
    rr = rr + chr( random( 32, 126 )).
  end.
  display encode ( rr + rr /* + rr + rr */ ) format "x(30)" with down.
  down.
end.

Encoded charaters are same for different charater sets...

 

[Stefan]

You are encoding an unlimited bit length string to a limited bit length space. Why are you surprised? This is a property of all hashes - see Hash collision - Wikipedia - the 16 character length of _password does not help in reducing the chance of hash collision.

 

[Rob Fitzpatrick]

The takeaway here is:

• The old OpenEdge RDBMS authentication functionality stores hashed passwords in _User.
• Passwords are hashed, once, with the encode function, as opposed to a modern, high-quality cryptographic hash function that is suitable for this purpose.
• There is no possibility to salt the hashes or use multiple hash iterations.
• This scheme is susceptible to various attacks, including rainbow tables.
• The encode algorithm is undocumented but could be known to attackers.

Therefore, no one should rely on this as an attack-resistant authentication scheme. Adding window dressing like password history and complexity policies won't change that.

 

[kasundha]

So do we have any mechanism to apply some conditions to DBA users like a password expiration period?

 

[Cecil]

Going a bit off topic. But I wrote the DECODE() function as a static class object in the ABL: Comment - My gimmicky version of the ENCODE() function

 

[peterjudge]

• The old OpenEdge RDBMS authentication functionality stores hashed passwords in _User.
• Passwords are hashed, once, with the encode function, as opposed to a modern, high-quality cryptographic hash function that is suitable for this purpose.
• There is no possibility to salt the hashes or use multiple hash iterations.
• This scheme is susceptible to various attacks, including rainbow tables.
• The encode algorithm is undocumented but could be known to attackers.

I am not advocating for using _User, but you can mitigate matters. You can use the OE Authentication Gateway (since 11.6.something) to add better authentication - using Active Directory or LDAP or something - and not have to manage credentials, their storage and their policies.

That said, if you are stuck using _User ...

You can write password hashes directly to the _User table, and apply whatever algorithm, salt, number of hash iterations, etc, etc, you want when creating that password hash.

You could look at using Authentication callbacks (see https://docs.progress.com/bundle/op...re-OpenEdge-performs-user-authentication.html) . You can use the AuthenticateUser callback to check the input password using the same approach you used to store the value. Note that you cannot use the shipped _oeusertable authentication system for this, you will ahve to add your own, but that's not a big deal.