Bug 19223

Assigning to maintainer

CC: (none) => marja11
Assignee: bugsquad => alien

*** Bug 19222 has been marked as a duplicate of this bug. ***

After many hours of digging I have found:

MariaDB [mysql]> SHOW VARIABLES LIKE '%pass%';
+------------------------------------+-----------------------------+
| Variable_name                      | Value                       |
+------------------------------------+-----------------------------+
| cracklib_password_check_dictionary | /usr/share/cracklib/pw_dict |
| old_passwords                      | OFF                         |
| report_password                    |                             |
| strict_password_validation         | ON                          |
+------------------------------------+-----------------------------+

So why has strict_password_validation been set ON for default installations?

It looks like an upstream decision as there is no reference to the variable outside the upstream tarball.

It seems buggy in that it checks for dictionary words for a regular mysql user but ignores the same word for the mysql root user. Also it appears to get the character count wrong depending on the text included, however testing gets very frustrating without any written policy to test against.

Anyhow, do we really need to have this feature enabled by default?
If not, can it be changed in our my.cnf or somewhere?

Also if a system is upgraded from Mga5 where this was not implemented, will passwords fail after upgrade? (maybe 'old_passwords' variable affects this?).

For the next version of zoneminder I have enhanced the setup wizard to check passwords before they are passed to mariadb, and also to update the weak default built-in password in the configuration.

I have tested an upgrade from Mageia5 to Cauldron with the new version of zoneminder and the upgrade does go smoothly with the old mysql root password.

This will fix my main reason for submitting this bug report, however it could affect other packages.

> assuming I can find the details of it.

https://dev.mysql.com/doc/refman/5.7/en/validate-password-plugin.html
bottom of page.

> It seems buggy in that it checks for dictionary words for a regular mysql user
> but ignores the same word for the mysql root user. 

My experience indicated it does check new root user pw.

> Also if a system is upgraded from Mga5 where this was not implemented, will
> passwords fail after upgrade? (maybe 'old_passwords' variable affects this?).

Going to depend on what is being done.  :(

I saved my mythtv database. Booted Release 6. Found out about new pw requirements, finally managed to change mysql root password, modified passwords to match new pw policy, loaded my mythtv database and was no longer able to login as root. :(

Solution was to change/set passwords in old database to pass new checks in the new mysql app and modify all configuration files to have default password which passes check.
See bug 19127

CC: (none) => bittwister2

(In reply to Bit Twister from comment #5)
> > assuming I can find the details of it.
> 
> https://dev.mysql.com/doc/refman/5.7/en/validate-password-plugin.html
> bottom of page.
> 
Yes I read that mysql page but I don't think it applies to new mariadb.

> > It seems buggy in that it checks for dictionary words for a regular mysql user
> > but ignores the same word for the mysql root user. 
> 
> My experience indicated it does check new root user pw.

I have found that if a (now) invalid root password exists prior to upgrade it will continue to work. Also if logged in as (mysql) root another now invalid root password may be set. It's all very confusing.   

> 
> > Also if a system is upgraded from Mga5 where this was not implemented, will
> > passwords fail after upgrade? (maybe 'old_passwords' variable affects this?).
> 
> Going to depend on what is being done.  :(

I did test this and it was not an issue.
> 
> I saved my mythtv database. Booted Release 6. Found out about new pw
> requirements, finally managed to change mysql root password, modified
> passwords to match new pw policy, loaded my mythtv database and was no
> longer able to login as root. :(
> 
> Solution was to change/set passwords in old database to pass new checks in
> the new mysql app and modify all configuration files to have default
> password which passes check.
> See bug 19127

"mythtv_Password" has no numerics and contains a dictionary word which should fail in new mariadb - but I have not tested it.

zoneminder users are requested to always run the zmsetup script after updates which now checks user password and any new root password.

If it's of any use to you I have a bash function to check passwords in zmsetup:
chkpass(). It checks character types, length and uses cracklib-dicts for dictionary check (as IIANM mariadb does). It is quiet by default returning 0 or 1 but creates an error message variable $messg if it fails which may be used by the calling script later if needed.

Sorry that should have been chkpasswd() at line 65
http://svnweb.mageia.org/packages/cauldron/zoneminder/current/SOURCES/zmsetup?revision=1047873&view=markup

Seems that the relevance of the dictionary word to cracklib-dicts is also dependant on password length - using my checker I get:

[baz@jackodesktop bin]$ chkpass mythtv_Password
Error: No numbers
[baz@jackodesktop bin]$ chkpass mythtv1_Password
OK
[baz@jackodesktop bin]$ chkpass mytht1_Password
OK
[baz@jackodesktop bin]$ chkpass myth1_Password
OK
[baz@jackodesktop bin]$ chkpass myt1_Password
OK
[baz@jackodesktop bin]$ chkpass my1_Password
OK
[baz@jackodesktop bin]$ chkpass m1_Password
Error: Contains dictionary word

Interesting ;)

Looking like either the Release Notes or Errata needs to have a mariadb section about this new "feature" requirement.

This just gets more confusing:

Running a series of passwords past mariadb gives really strange results.

It seems that length is checked to be min of 8

Numbers, upper case and special characters seem to not be tested for.

Dictionary words seem to be checked sometimes and possibly for a limited selection of words and then only when at the start of the password?

[baz@jackodesktop SOURCES]$ for p in mythtv_Password mythtv_Pass mythv_Pass mtv_Pass tv_Pass Password Password1 Password16 password17 password17~ password17~B passages17~B 17~Bpassword word17~B words17~B before17~B bedroom17~B shirts17~b passhrts 95735216; do echo; echo -n $p"  "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo;  done

mythtv_Password  

mythtv_Pass  

mythv_Pass  

mtv_Pass  

tv_Pass  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


Password  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


Password1  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


Password16  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


password17  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


password17~  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


password17~B  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


passages17~B  

17~Bpassword  

word17~B  

words17~B  

before17~B  

bedroom17~B  

shirts17~b  

passhrts  

95735216  
[baz@jackodesktop SOURCES]$

Just gets worse:
[baz@jackodesktop SOURCES]$ for p in christine williams williams1 williams#1 21#williams anne-marie anne-~marie christ21 christ21# 21christ# 21#christ 21#christ% 2christ3 2christ#; do echo; echo -n $p"  "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo;  done

christine  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


williams  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


williams1  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


williams#1  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


21#williams  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


anne-marie  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


anne-~marie  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


christ21  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


christ21#  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


21christ#  

21#christ  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


21#christ%  

2christ3  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


2christ#  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

[baz@jackodesktop SOURCES]$

Glad to see that my mythtv_Password gets through. You had me worried I was losing my mind with your standalone testing showing it would fail.

Just guessing but I suggest your name test shots will work if they contain an uppercase letter.

[baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo -n $p"  "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo;  done

cHristine  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


Williams  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


wilLiams1  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


williamsB1  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


21#Williams  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


anne-Marie  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


Anne-marie  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

Ah - been reading.

MariaDB [(none)]> uninstall plugin cracklib_password_check;

Now:

[baz@jackodesktop SOURCES]$ passarr=(williams christ humpty baz 12345 qwertyuiop) 
[baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo -n $p"  "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo;  done

williams  

christ  

humpty  

baz  

12345  

qwertyuiop  
[baz@jackodesktop SOURCES]$

MariaDB [(none)]> INSTALL PLUGIN cracklib_password_check SONAME 'cracklib_password_check.so';
Query OK, 0 rows affected (0.00 sec)

[baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo -n $p"  "; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";echo;  done

williams  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


christ  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


humpty  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


baz  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


12345  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements


qwertyuiop  ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

[baz@jackodesktop SOURCES]$


So it can be disabled from within mariadb but will it survive a reboot I wonder?

In a word - NO

I have removed all checking other than cracklib-check and now both agree for all cases I can think up,
So mariadb is using ONLY cracklib-check to check it's passwords.

[baz@jackodesktop SOURCES]$ for p in ${passarr[@]}; do echo; echo "$p" | /usr/sbin/cracklib-check ; mysql -uroot -p$mysqlpass -e "USE mysql; GRANT ALL PRIVILEGES ON zm.* TO $zm_db_user@localhost IDENTIFIED BY '$p';";  done

Mythetv_Password: OK

zmpass: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

zmpassw: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

zmpasswd: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

zmpass-wd: OK

james1_3: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

2james#123: OK

Williams: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

~williams: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

21christ#: OK

christ21#: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

mftkdp: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

mftkdpe: OK

mftkdpej: OK

123456: it is too simplistic/systematic
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

12345678: it is too simplistic/systematic
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

184629: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

1846295: it is based on a (reversed) dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

18462951: it is based on a (reversed) dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements

PASS_WD: it is based on a dictionary word
ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements
[baz@jackodesktop SOURCES]$ 


So whilst I disagree with some of the messages above ...

echo 'yourpassword' | /usr/sbin/cracklib-check

...is all that is required to verify that a password will work with mariadb

Now I know, I feel I made rather hard work of that!

I guess all we need then is to add a small explanation in errata something like:

============================================
Mariadb - In Mageia 6 mariadb-10.1.16 has strict_password_validation permanently set ON.
For passwords to be accepted they need to pass the cracklib-check test.
The following code snippet will do this for any intended password.
$ echo 'yourpassword' | /usr/sbin/cracklib-check
yourpassword: OK
===========================================

Strange that one passes!

Whiteboard: (none) => FOR_ERRATA

(In reply to Barry Jackson from comment #17)
> I guess all we need then is to add a small explanation in errata something
> like:
> 
> ============================================
> Mariadb - In Mageia 6 mariadb-10.1.16 has strict_password_validation
> permanently set ON.
> For passwords to be accepted they need to pass the cracklib-check test.
> The following code snippet will do this for any intended password.
> $ echo 'yourpassword' | /usr/sbin/cracklib-check
> yourpassword: OK
> ===========================================
> 
> Strange that one passes!

Thanks for the suggestion, Barry.
Added.

Keywords: FOR_ERRATA6 => IN_ERRATA6

Closing then

Status: NEW => RESOLVED
Resolution: (none) => WORKSFORME