MySQL Bugs: #15165: Account Without Password doesn't report all users without a pwd

Bug #15165	Account Without Password doesn't report all users without a pwd
Submitted:	23 Nov 2005 0:25	Modified:	14 Oct 2008 16:03
Reporter:	Andy Bang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S3 (Non-critical)
Version:	Build 843	OS:	Any (all)
Assigned to:	Andy Bang	CPU Architecture:	Any

Description:
The Account Without Password doesn't report all the users that don't have a password -- it only reports one.

How to repeat:
1) Make sure you have more than one named user account that doesn't have a password. Here's what my user table looks like:

mysql> select user, host, password from user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *91D70B1A27857A67CDCD73F7D043E9484E34B247 |
| root      | %         | *91D70B1A27857A67CDCD73F7D043E9484E34B247 |
|           | localhost |                                           |
|           | %         |                                           |
| andy      | localhost | *F96F1632B76CC8C3AE6A600874573B1077718130 |
| bookorama | %         |                                           |
| bookorama | localhost |                                           |
| merlin    | localhost | *91D70B1A27857A67CDCD73F7D043E9484E34B247 |
| merlin    | %         | *91D70B1A27857A67CDCD73F7D043E9484E34B247 |
| hyperic   | %         |                                           |
| hyperic   | localhost |                                           |
| lamorugby | %         | *00295676C93221AD05965C59EFB7CED5B0D9FE69 |
| lamorugby | localhost | *00295676C93221AD05965C59EFB7CED5B0D9FE69 |
+-----------+-----------+-------------------------------------------+
13 rows in set (0.00 sec)

2) Run the Account Without Password advisor.
3) It says "User account 'hyperic' on server 'Andys Laptop' does not have a password", but it doesn't mention the 'bookorama' account, which doesn't have a password either.

Suggested fix:
Show all the user accounts that don't have a password.

Account Without Password 	 [ show ] [ close ]
User account '%dc.0' on server '%server.0' does not have a password set. Assign a strong password to all user accounts. A strong password should be at least 8 characters long and should contain both numeric and alpha symbols in mixed case.
Tested with 3.1- still a bug

Use GROUP_CONCAT for this and similar ones.

Note that the solution uses GROUP_CONCAT to create a bulleted list of all users that don't have a password.  However, GROUP_CONCAT truncates the string it returns to group_concat_max_len characters (default=1024).  So if you have a LOT of users with no password (> 50?), then the list will be truncated, but there's nothing we can do about that.  And if someone has that many users without a password, either they don't care about security or they have some special reason for it, in which case they shouldn't run this rule.

This required a change in both the Advisor repo (Pushed up to revision 226) and the Agent repo (Pushed up to revision 1032).  It shouldn't be tested until a new agent build and a new set of advisors are created.  And the advisors won't be posted to Enterprise until the fix is tested, so please manually import the latest advisors when you test this rather than importing them from Enterprise.

verified fixed in build 2.0.0.7072

An entry was added to the 2.0 changelog:

The Account Without Password advisor did not report all users who were without a password, it only reported one.