Bug 19223 - mariadb - Your password does not satisfy the current policy requirements
Summary: mariadb - Your password does not satisfy the current policy requirements
Status: RESOLVED WORKSFORME
Alias: None
Product: Mageia
Classification: Unclassified
Component: RPM Packages (show other bugs)
Version: Cauldron
Hardware: All Linux
Priority: Normal critical
Target Milestone: ---
Assignee: AL13N
QA Contact:
URL:
Whiteboard:
Keywords: IN_ERRATA6
: 19222 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-08-20 18:16 CEST by Barry Jackson
Modified: 2017-08-08 11:57 CEST (History)
2 users (show)

See Also:
Source RPM: mariadb-10.1.16-1.mga6
CVE:
Status comment:


Attachments

Description Barry Jackson 2016-08-20 18:16:27 CEST
Description of problem:
mariadb complains about passwords but gives no idea about what the new "current policy" has been changed to regarding passwords.

This has broken the zoneminder 'zmsetup' script as previously the password strength was not an issue.

Is there a test function to check passwords to know in advance whether they will pass the mariadb "policy", or do I have to write one from scratch - assuming I can find the details of it.

As far as I can tell from querying mariadb there is no password verification plugin enabled, so where has this change in cauldron come from?

It's a real PITA when stuff that was working fine gets gratuitously broken for no good reason.


Version-Release number of selected component (if applicable):


How reproducible:


Steps to Reproduce:
1.
2.
3.
Comment 1 Marja Van Waes 2016-08-21 11:44:46 CEST
Assigning to maintainer

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

Comment 2 Marja Van Waes 2016-08-21 11:46:13 CEST
*** Bug 19222 has been marked as a duplicate of this bug. ***
Comment 3 Barry Jackson 2016-08-21 16:08:51 CEST
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?).
Comment 4 Barry Jackson 2016-08-22 19:52:26 CEST
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.
Comment 5 Bit Twister 2016-08-24 07:57:37 CEST
> 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

Comment 6 Barry Jackson 2016-08-24 14:11:14 CEST
(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.
Comment 7 Barry Jackson 2016-08-24 14:26:41 CEST
Sorry that should have been chkpasswd() at line 65
http://svnweb.mageia.org/packages/cauldron/zoneminder/current/SOURCES/zmsetup?revision=1047873&view=markup
Comment 8 Barry Jackson 2016-08-24 18:32:16 CEST
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 ;)
Comment 9 Bit Twister 2016-08-25 01:00:27 CEST
Looking like either the Release Notes or Errata needs to have a mariadb section about this new "feature" requirement.
Comment 10 Barry Jackson 2016-08-25 13:22:10 CEST
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]$
Comment 11 Barry Jackson 2016-08-25 14:01:39 CEST
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]$
Comment 12 Bit Twister 2016-08-25 14:36:03 CEST
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.
Comment 13 Barry Jackson 2016-08-25 16:01:55 CEST
[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
Comment 14 Barry Jackson 2016-08-25 16:58:25 CEST
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?
Comment 15 Barry Jackson 2016-08-25 17:04:20 CEST
In a word - NO
Comment 16 Barry Jackson 2016-08-25 19:31:54 CEST
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!
Comment 17 Barry Jackson 2016-08-25 22:54:40 CEST
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

Barry Jackson 2016-08-31 14:54:39 CEST

Whiteboard: FOR_ERRATA => FOR_ERRATA6

Samuel Verschelde 2016-10-18 13:16:15 CEST

Whiteboard: FOR_ERRATA6 => (none)
Keywords: (none) => FOR_ERRATA6

Comment 18 Marja Van Waes 2017-03-06 17:04:22 CET
(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

Comment 19 Barry Jackson 2017-03-20 13:47:29 CET
Closing then

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

papoteur 2017-08-08 11:57:55 CEST

See Also: (none) => https://bugs.mageia.org/show_bug.cgi?id=21472


Note You need to log in before you can comment on or make changes to this bug.