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