Password is not updated in `mysql`.`user` table when read-only database user's password is changed
I happened upon what seems to be a bug in ISPConfig (v 3.0.5.4p5).
When I attempt to change the password for a read-only MySQL database user, the password (hash) in MySQL's mysql
.user
is not changed accordingly.
For what it's worth, I am able to change a fully-privileged user's password without issue; this problem occurs only when the user has read-only privileges.
I have tested this on two different (though similarly-configured) ISPConfig servers and the result is the same.
Steps to reproduce (ISPConfig client ID is assumed to be "1" for this purpose):
1.) Create a new database user (e.g., "user") and supply a known password.
2.) Create another new database user, which will have read-only privileges (e.g., "userro"). Supply the same password as for the first user.
3.) Create a new database and set "Database user" to "c1user", and set "Read-only database user" to "c1userro". Save the database.
We see three entries added to the Jobqueue:
2015-03-06 12:54 localhost Insert web_database 2015-03-06 12:54 localhost ubuntu-vm Update web_database_user 2015-03-06 12:54 localhost Update web_database_user
These changes are processed as such:
06.03.2015-12:55 - DEBUG - Set Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock 06.03.2015-12:55 - DEBUG - Found 3 changes, starting update process. 06.03.2015-12:55 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'. 06.03.2015-12:55 - DEBUG - Processed datalog_id 234 06.03.2015-12:55 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'. 06.03.2015-12:55 - DEBUG - Processed datalog_id 235 06.03.2015-12:55 - DEBUG - Calling function 'db_insert' from plugin 'mysql_clientdb_plugin' raised by event 'database_insert'. 06.03.2015-12:55 - DEBUG - Created MySQL database: c1test 06.03.2015-12:55 - DEBUG - GRANT for user c1user at host localhost 06.03.2015-12:55 - DEBUG - GRANT ALL ON c1test.* TO 'c1user'@'localhost' IDENTIFIED BY PASSWORD '9AEAFA390F7DCA63B36F16537095960E6196778A'; success? yes 06.03.2015-12:55 - DEBUG - GRANT for user c1userro at host localhost 06.03.2015-12:55 - DEBUG - GRANT SELECT ON c1test. TO 'c1userro'@'localhost' IDENTIFIED BY PASSWORD '*9AEAFA390F7DCA63B36F16537095960E6196778A'; success? yes 06.03.2015-12:55 - DEBUG - Processed datalog_id 236 06.03.2015-12:55 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
So far, so good.
4.) Obtain the password hashes for both users, c1user and c1userro, so that we are able to compare them to the values after we change each user's password. Given that we supplied the same password for each user, the hashes should be identical.
mysql -uroot -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1314 Server version: 5.6.19-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Database changed
mysql> SELECT Host
, User
, Password
FROM user
WHERE User
IN('c1user', 'c1userro');
+-----------+----------+-------------------------------------------+
| Host | User | Password |
+-----------+----------+-------------------------------------------+
| localhost | c1userro | *9AEAFA390F7DCA63B36F16537095960E6196778A |
| localhost | c1user | *9AEAFA390F7DCA63B36F16537095960E6196778A |
+-----------+----------+-------------------------------------------+
2 rows in set (0.00 sec)
5.) Now, change the password for c1userro, to a different (but known) value.
Monitor the ISPConfig log (at DEBUG level) and ensure that the update is made before continuing; the relevant entries should look like this:
06.03.2015-13:03 - DEBUG - Set Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock 06.03.2015-13:03 - DEBUG - Found 1 changes, starting update process. 06.03.2015-13:03 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'. 06.03.2015-13:03 - DEBUG - Changing MySQL user password for: c1user@localhost 06.03.2015-13:03 - DEBUG - Processed datalog_id 237 06.03.2015-13:03 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
6.) Repeat the same MySQL query to determine whether or not the password hash for the user whose password we changed in the previous step was, in fact, updated:
mysql> SELECT Host
, User
, Password
FROM user
WHERE User
IN('c1user', 'c1userro');
+-----------+----------+-------------------------------------------+
| Host | User | Password |
+-----------+----------+-------------------------------------------+
| localhost | c1userro | *9AEAFA390F7DCA63B36F16537095960E6196778A |
| localhost | c1user | *4C367891B0E318002CA2EA353E87E0DB8EEF8263 |
+-----------+----------+-------------------------------------------+
2 rows in set (0.00 sec)
The c1user does indeed have a new password hash, so all is well thus far.
7.) This time, change the password for the read-only user, c1userro.
And, again, monitor the ISPConfig log to ensure that the change is processed. The relevant entries should look something like this:
06.03.2015-13:07 - DEBUG - Set Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock 06.03.2015-13:07 - DEBUG - Found 1 changes, starting update process. 06.03.2015-13:07 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'. 06.03.2015-13:07 - DEBUG - Processed datalog_id 238 06.03.2015-13:07 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
At this point, the astute observer notices that this log excerpt is missing one line that is present in the excerpt from step 5:
Changing MySQL user password for: [user]
where [user] is the target user whose password is being changed.
8.) Again, repeat the MySQL query to compare the password hashes, as stored in MySQL.
mysql> SELECT Host
, User
, Password
FROM user
WHERE User
IN('c1user', 'c1userro');
+-----------+----------+-------------------------------------------+
| Host | User | Password |
+-----------+----------+-------------------------------------------+
| localhost | c1userro | *9AEAFA390F7DCA63B36F16537095960E6196778A |
| localhost | c1user | *4C367891B0E318002CA2EA353E87E0DB8EEF8263 |
+-----------+----------+-------------------------------------------+
2 rows in set (0.00 sec)
The result is exactly the same as in step 6, confirming that the c1userro user's password was not changed.