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

[23 Nov 2005 0:25] Andy Bang
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.
[14 Apr 2006 20:08] Punita Srivastava
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
[26 Jun 2007 21:05] Andy Bang
Use GROUP_CONCAT for this and similar ones.
[9 Sep 2008 23:14] Andy Bang
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.
[9 Oct 2008 16:36] Bill Weber
verified fixed in build 2.0.0.7072
[14 Oct 2008 16:03] Tony Bedford
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.