How to find unused MariaDB/MySQL accounts

In MariaDB and Oracle MySQL, an account is a user_name@host combination, where host can be an ip address (v4 or v6) or a hostname. Also, the host part can be specified as a LIKE pattern (such as '161.58.%'). The account used by the connection determines the privileges for that connection. When a connection matches more than one account, only one is used, and privileges assigned to other accounts will be ignored.

It can happen that you think that a certain client uses an account, but it uses a different account. For this reason, in this post I’ll show the queries to get all defined accounts, plus the accounts that were not used since last server startup.

The second query will use performance_schema. Audit plugins and the General Log can be used to get more information, such as the accounts that are not used since 1 month. However, if performance_schema is activated in your server, these queries are useful to “debug” your accounts.

How to list all accounts:

MariaDB [(none)]> SELECT User, Host FROM mysql.user ORDER BY User;
+-----------+-------------+
| User      | Host        |
+-----------+-------------+
| arancia   | computadora |
| drupal    | hal         |
| drupal    | %           |
| genoveffa | computadora |
| genoveffa | hal         |
| mandarino | hal         |
| mandarino | computadora |
| root      | ::1         |
| root      | 127.0.0.1   |
| root      | n           |
| root      | localhost   |
| test      | 127.%       |
| wp        | %           |
| wp        | hal         |
+-----------+-------------+
14 rows in set (0.00 sec)

How to list accounts which never connected since last restart:

MariaDB [(none)]> SELECT DISTINCT m.User, m.Host FROM mysql.User m LEFT JOIN performance_schema.ACCOUNTS p ON m.User = p.USER AND p.HOST LIKE m.Host  WHERE p.USER IS NULL ORDER BY User;
+-----------+-------------+
| User      | Host        |
+-----------+-------------+
| arancia   | computadora |
| drupal    | hal         |
| genoveffa | hal         |
| genoveffa | computadora |
| mandarino | hal         |
| mandarino | computadora |
| root      | n           |
| root      | ::1         |
| root      | 127.0.0.1   |
| test      | 127.%       |
| wp        | hal         |
+-----------+-------------+
11 rows in set (0.01 sec)

Of course, if an account is unused, it is also possible that the user associated to that account never connected. To get a list of users which never connected since last startup:

MariaDB [(none)]> SELECT DISTINCT m.User FROM mysql.User m LEFT JOIN performance_schema.USERS p ON m.User = p.USER WHERE p.USER IS NULL ORDER BY User;
+-----------+
| User      |
+-----------+
| mandarino |
+-----------+
3 rows in set (0.00 sec)

This doesn’t always mean that the user is unused! An account which never connects could be the definer of some Views or Stored Programs, and may be used to check those programs privileges (SQL SECURITY DEFINER); in that case you don’t want to drop him. So, let’s see how to get a list of totally unused accounts (never connected since last restart and not used to check Stored Programs/Views privileges):

SELECT DISTINCT
	m.User, m.Host
	FROM mysql.User m
	LEFT JOIN performance_schema.ACCOUNTS p
	ON m.User = p.USER AND p.HOST LIKE m.Host
	LEFT JOIN information_schema.VIEWS is_v
	ON is_v.SECURITY_TYPE = 'DEFINER' AND is_v.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	LEFT JOIN information_schema.ROUTINES is_r
	ON is_r.SECURITY_TYPE = 'DEFINER' AND is_r.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	LEFT JOIN information_schema.EVENTS is_e
	ON is_e.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	LEFT JOIN information_schema.TRIGGERS is_t
	ON is_t.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	WHERE p.USER IS NULL
		AND is_v.DEFINER IS NULL
		AND is_r.DEFINER IS NULL
		AND is_e.DEFINER IS NULL
		AND is_t.DEFINER IS NULL
	ORDER BY User, Host;

Enjoy!