Don't Forget to Check Your Accounts

    The start of fall in the northern hemisphere is a time for many of use to prepare for the approaching winter. Part of that is clearing away and storing summer things and pulling out of storage the winter things. And one of those things I suggest to you do is look at the accounts on your MySQL instances.

    It is very easy, especially with multiple folks creating and alter doing user administration, to have redundant accounts.  You may also have  unneeded accounts.  Or you can have accounts that expired and nobody asked to have them reset.  And you may have accounts that you are not sure who or what uses them.

Start with this simple query to get a first look at your user data.

use mysql;
select User, Host, password_expired, account_locked, User_attributes from user;  

    Examine which accounts are expired and locked.  Do you know why they are in this state? 

    Next look at the User and Host data sorted by User. 

select User, Host from user order by User, Host;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| Foo              | %         |
| bar              | localhost |
| bill             | localhost |
| davetest         | localhost |
| demo             | localhost |
| demo2            | localhost |
| dstokes          | %         |
| dstokes          | localhost |
| foo              | localhost |
| foobar           | localhost |
| jack             | localhost |
| mary             | localhost |
| mike             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| myuser           | %         |
| root             | localhost |
+------------------+-----------+

    In this case notice that there are two separate and distinct accounts where the User column is 'dstokes'.  Someone might have wanted one account or maybe there are two different users to justify the account.  Always look at the account as the pair of the User and Host columns.  If this was supposed to be one account, save yourself some future headaches and merge the accounts.  Remember the MySQL authentication system is a wee bit promisors and lets the first matching account into the server.  This is why dstokes@localhost can not read the data that is granted to dstokes@%! 

    And when you discover that 'dstokes'@'%' is Dave Stokes and dstokes@'localhost is Dilbert Stokes please annotate this information.

ALTER USER 'dstokes'@'%' COMMENT 'This is remote account for Dave Stokes x1234';

ALTER USER 'dstokes'@'localhost' COMMENT 'Dilbert Stokes debug account';