security

MARIADB PASSWORDS

PROBLEM
Did you know that MariaDB (which has its roots in MySQL) stores unsalted password hashes for the database users? You can copy those unsalted hashes and paste them into many internet rainbow tables, and get plain text passwords back? One must wonder how any PCI DSS compliant enterprise can stay complaint using such a database engine :/ Here’s the problem.

On any MariaDB system (current version is 10.1.19), issue this SQL statement:

SELECT * FROM mysql.user;

You’ll see a ‘Password’ column that contains the unsalted hash. It will contain data similar to this.
*8CE1739ADDF8A89483EC7199E6B7E8ACCD4EC3C8

Then, issue the following Google search: mariadb OR mysql password cracker

Find your favorite site, copy/paste the hash. Some of the sites may require you to remove the “*” in column 1 of the hash as it’s really not part of the hash. Try the above unsalted hash. Did you get the password back?

RAINBOW TABLE

The sites are using what is called a rainbow table. If you want to build your own rainbow table, go out on the internet and gather all the passwords that have been discovered in all the major breaches over the years. Put them into a database, say table ‘my_table’, column ‘plain_text’, and massage them… create new entries with ‘123’ appended, change some ‘ohs’ to ‘zeros’, ‘ells’ to ‘ones’, etc, etc. Alter your table to include a ‘Password’ column. Then issue the command…

UPDATE my_table, SET Password=UPPER(SHA1(UNHEX(SHA1(plain_text))))

Now, you too can search millions if not billions of MariaDB passwords by searching for matching ‘Passwords’ and returning the ‘plain_text’ column.

Now, if each of the hashes in the system were salted, rainbow tables would be impossible. Salted hashes means that each given plain text password, rather than being just a single hash for all occurrences, there would be most likely 4 billion… assuming the hash is an unsigned integer. That makes the creation of rainbow tables impractical as you’d need to multiply your database for the unsalted hash by 4 billion… both the time to create, and the disk space used.

SOLUTION
Funny thing is, MariaDB’s documentation indicates their password process is ‘old’ implying that it may not be sure. They are referencing specifically the use of the SHA1 hashing function.

Since they will be replacing SHA1 anyway (because it’s “old”), I’d recommend that they also salt and pepper the hash. Salt has a one to one relationship with the user. Pepper acts just like the salt, but is a one to one relationship with the database instance or the enterprise (or some other grouping).
*Note added 20-Mar-2017: MariaDB will be moving from SHA1 to SHA2 in releases 10.1.22 and 10.2.5 as MDEV-12160.

Here’s what I’d suggest to MariaDB.

At it’s basis, change the existing hash generator
from: UPPER(SHA1(UNHEX(SHA1(@plaintextpassword))))
to: UPPER(SHA2(UNHEX(SHA2(
             CONCAT(CONCAT(@plaintextpassword, salt), pepper_value),256)),256))

To accommodate a transition, the existing ‘Password’ column in the mysql.user table would need to remain. I’d suggest these changes though:
ALTER TABLE mysql.user ADD (password2 CHAR(64) DEFAULT NULL);
ALTER TABLE mysql.user ADD (salt CHAR(16) DEFAULT NULL);
UPDATE mysql.user SET salt= CAST(ROUND(RAND()* 10000000000000000) AS CHAR(16)) WHERE salt IS NULL;
ALTER TABLE mysql.user ADD (pepper ENUM(‘N’,’Y’) DEFAULT ‘N’);

The ‘password2’ column would hold the new hash.
The ‘salt’ column would hold 16 digits of random salt
The ‘pepper’ column would allow instance specific pepper to be used or not. If not, pepper would be the empty string: ”

PEPPER
There would need to be insert/update triggers on the mysql.user table to insure pepper is available if ever set to ‘Y’. Note, pepper is not well defined as it may be something in the my.cnf file, or it may simply be using a global variable that should never change, like server_id. The ramifications for using pepper is that the mysql.user table could not be copied to another system and expect users to be able to login.

NOT SO SIMPLE, MORE TO THINK ABOUT
Where else is the password hash used? There’s the SET PASSWORD command. The SET PASSWORD command works differently for a user changing their own password than an administrator changing the password on behalf of the user. For the administrator, they need to specify a user. For the user changing their own password, they cannot specify a user.

All new passwords set with the SET PASSWORD command should be set using the new method, by adding the hash to ‘password2’.

When logging in (or any time the password is checked), if ‘password2’ is not null, the new method should be used. Otherwise the old method should be used.

One last recommendation to add to the security of MariaDB… when the user sets their own password interactively with the SET PASSWORD command, they should be queried to verify their existing password.

Any time pepper is changed, the administrator would need to reset that user’s password for them.

ETC, ETC, ETC 🙁
To allow continued compliance with PCI DSS, some additional care should be taken while making changes to the LOGIN process & the SET PASSWORD command. Some columns should be added to the mysql.user table… a last_password_changed date/time, last_login date/time, last_failed_login date/time, login_failed_count counter, and an enforce_password_expiration flag.

There should also be some instance level items, probably GLOBAL.variables: password_lifetime_days, password_failed_lockout_count value (lock account upon this number of failures), password_failed_reset_minutes (to re-enable the ability to login once last_failed_login ages past the set number of minutes), password_min_length, password_complexity_lower/password_complexity_upper/password_complexity_digits/password_complexity_special (number of upper case letters, lower case letters, digits, special characters required in the password)

Of course, code would need to be added to use these values.

Do you see issues with my assistance to MariaDB? Please comment below.

Keywords: security, passwords, encryption, hash, salt, pepper, sha1, sha2, sha256, sha512

WHAT IS THE RIGHT WAY TO STORE A PASSWORD IN A DATABASE

This blog was created for those that wish to store passwords in the database. Those willing to take a few minutes to think about and understand the proper way, will be pleasantly surprised that it’s really just as quick and easy as doing it the wrong way. The difference is that you’re here, spending a few minutes understanding the right way.

First, the wrong way is to store the password in clear-text. If you can SELECT * from table_name; and you can see your user’s passwords… you’ve discovered the wrong way. Same with trying to scramble them up with some ad-hoc code.

BACKGROUND/RATIONAL
Many people use the same username/password for all their web activity. If they create an account on your web site, there is a chance that you (as the web site administrator) could use their
username/password (specified to log into your web site) to login to their gmail.com, amazon.com, microsoft.com, twitter.com, facebook.com, etc, account.

By the same token, you want to protect your user’s personally identifiable information from hackers that may have access to your site.

DISCUSSION
1) Hash the password
Hash the password. All will be good, right? (‘Hash’ is kind of like encrypting, see below.)

One benefit of hashing is that you can store the hash in, say a CHAR(64) column. If you have a 1 character password, it will hash to 64 byte hexadecimal string. Same goes for a password of 128 characters. It does not matter. There should be no need at all for a password maximum length limit, any password will hash to the same 64 bytes. That’s also the reason for CHAR and not VARCHAR… there is no varying, it’s always 64 bytes.

Hashing is an important step, but the password your user provided hashes to exactly one hash-value.
Because hashes of the same password always hash to the same hash-value, someone could create a table of common passwords and their corresponding hash-values. They then could see if a password’s hash-value is listed in the table. If so, they then have your user’s password in clear-text. That table is called a ‘rainbow table’.

2) Salt
Adding salt involves hashing again with the user username after hashing the password (ie, first hash concatenated with the userid). This makes the hash-value different for someone with the same password. This can also be called a ‘salted hash’ and is an effective way of protecting against a rainbow table. But, if someone steals a database backup, they can also obtain the salt along with the salted hash.

If they wanted to create a ‘rainbow table’, it would be for each user. For example, they could build a rainbow table for ‘john’. John’s rainbow table would hash all possible passwords, and would be valid for only john’s users. Maybe, rather than using ‘john’ for the salt, we generate a random number as salt for each user as they are added to the database.

Because hashing algorithms have been standardized (ie, SHA256), hashing just a password (without salt), where each password generates a 1 to one mapping with the hash, building a rainbow table of, say, passwords discovered in breaches from the last 10 years, might be feasible with large computing power, and months of time. It would contain many billion entries. By adding salt, there would have to be one of these rainbow tables for each salt entry. Hence, rainbow tables become impractical.

3) Pepper
Pepper is like salt. It’s a third hash with something else appended to the second hash. The difference is it’s something that is not stored in the database. It’s something unique to the database server, the application server, or the company. For example, maybe you hash again with your company’s phone number after hashing with the password and then hashing with the salt.

With that, a simple attack like SQL Injection, or stealing a database backup, will not be enough (they won’t have the pepper). Your user’s passwords will be kept out of the hands of the bad guy.

FURTHER EXPLANATION
The above is a description of the rational for hashing a password and mixing in salt and pepper. It’s all about making it hard and compute intensive to reverse the process.

Quick definition: a ‘hash’ is a one-way cryptographic function. It’s not encryption, it’s a cryptographic function. A hash function will quickly convert a clear-text string to a hash-value (commonly referred to as a ‘hash’, just like the type of function). It is then nearly impossible to reverse the process, ie, to go from the hash-value back to the clear-text string. Hashing implies one way, or not reversible. Encryption is twoway, you can generate something unreadable, but with the key, that unreadable stuff can be reversed to the original clear-text string.

Because the goal is to make reversing the process slow (see Update below), rather than
   hash(password+salt+pepper) // one call to hash
we opt for
   hash(hash(hash(password)+salt)+pepper) // three calls to hash, three times slower

Use a modern hash for above algorithm. Something like SHA256. Many old hashes have been deprecated and should not be used. Note, many programming languages have libraries containing hash algorithms, ie, java: MessageDigest md = MessageDigest.getInstance(“SHA-256”);

Salt & pepper should each be a 32 byte integer. They need to be large enough to be impractical to manipulate, ie to discover the pepper if the password and salt is known.

Salt should be a random value stored in the database in the same row as the password’s hash-value, and should be generated/created when the user row is created.

Pepper should be a random value stored outside the database, preferably on the application server. It should be created at the time the web-site is created. Pepper can be hard coded, although a secure config parameter is preferred. As the specific pepper is required for anyone to use the web site, it should be backed up by a special process.

Ensure the password is transmitted securely from the user to the app server using transport level encryption (up to date TLS (ie, V1.2 or greater at the time this blog was created).

Password validation is accomplished by hashing the password the user entered, and comparing the hash-value to the hash-value stored in the database.

EXTRA SECURITY
Consider hashing like this to slow the process down and make generating rainbow tables more time consuming…
i=500 // or some value stored with the user row
for (j=1, j<=i, i++)  // many repetitions of hash
{
    hash(hash(hash(password)+salt)+pepper)
}

Choose salt and pepper as 64 byte integers, and choose a large hash, something like SHA512.

[Updated 18-Oct-2017]
The ‘Extra Security’ may not scale in huge environments. In environments with just thousands of users, it’ll be fine. For the huge environments, consider adding the complexity of using a keyed-Hash-based Message Authentication Code (HMAC) rather than Salt+Pepper. The HMAC key serves the same function as the pepper. There is no need for the “Extra Security” looping, and no need to slow the process. HMAC, because it uses a key, may require additional complexities, for example, it may require the use of a Hardware Security Module (HSM) and isolate the use of the key from the application (ie, via a web service).

The above suggests the use of SHA256 hashing function. Also consider use of PBKDF2 or Scrypt.

REFERENCES

• Hash
• Deprecated Hashes
• Same Password used on all web sites
• Rainbow Table
• Google password+salt+pepper

BONUS

• Maybe passwords are a thing of the past

Keywords: password storage, hash, salt, pepper