Finding CURRENT_USER for any user

A MySQL account is a user/host combination. A MySQL connection is done by a user connecting from some host.

However, the user/host from which the connection is made are not the same as the user/host as specified in the account. For example, the account may be created thus:

CREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456';

The host as specified in the above account is a wildcard host. A connection by the ‘temp’ user from ‘10.0.0.3’ can map into that account. It thus happens that the connected user is ‘temp’@’10.0.0.3’, yet the assigned account is ‘temp’@’10.0.0.%’.

MySQL provides with the USER() and CURRENT_USER() which map to the connected user and the assigned account, respectively, and which lets the current session identify the relation between the two. Read more on this on the MySQL docs.

The problem

And the trouble is: MySQL only provides this functionality for the current session. Surprisingly, given a user/host combination, I cannot get MySQL to tell me which account matches those details.

The inconsistency

And I care because there is an inconsistency. Namely, when I do SHOW PROCESSLIST MySQL tells me the user & host from which the connection is made. It does not tell me the account for which the process is assigned.

root@mysql-5.1.51> SHOW PROCESSLIST;
+----+------+----------------+---------------+---------+------+-------+------------------+
| Id | User | Host           | db            | Command | Time | State | Info             |
+----+------+----------------+---------------+---------+------+-------+------------------+
| 16 | temp | 10.0.0.3:54142 | common_schema | Query   |    0 | NULL  | SELECT id, ...   |
+----+------+----------------+---------------+---------+------+-------+------------------+

The absurdness is that a super user, the manager of a MySQL server, has the full listing of connections, yet is unable to map those connections to accounts.

I got into this because of a suggestion by Matthew Montgomery to include a tool of his into common_schema.The tool says “Kill all slow queries which are not executed by users with the SUPER privilege”.

Great idea! But then, how do you identify such users?

The tool attempts to find an exact match between INFORMATION_SCHEMA.PROCESSLIST‘s user/host and INFORMATION_SCHEMA.USER_PRIVILEGES‘s user/host. This will do well when the account is ‘root’@’localhost’, but less so when it is ‘maatkit’@’%.mydomain’.

Unfortunately, many things fall under SUPER‘s attention, and such accounts as monitoring, backup, management may require that privilege.

Account matching

Matching is achievable, but not completely trivial. If you’re not aware of this, you should note that ‘temp’@’10.0.0.3’ can match any of the following:

+------+----------+
| temp | 10.0.%   |
| %    | 10.0.0.3 |
| temp | 10.0.0.3 |
| temp | 10.0.0.% |
+------+----------+

And the rule is we must match by most specific host first, then by most specific user. The order of matching should be this:

+------+----------+
| temp | 10.0.0.3 |
| %    | 10.0.0.3 |
| temp | 10.0.0.% |
| temp | 10.0.%   |
+------+----------+

The first row to match our connection’s user/host is the matched account.

The good news

This means the problem is reduced to an ORDER BY and to regular expressions. Easy enough to do with SQL. We prefer hosts with no wildcard to those with; we prefer more subdomains, we prefer no wildcard for users.

The code

The following query assumes you have two session variables: @connection_user and @connection_host.

SELECT
  user, host
FROM
  mysql.user
WHERE
  @connection_user RLIKE
    CONCAT('^',
      REPLACE(
        user,
        '%', '.*'),
      '$')
  AND SUBSTRING_INDEX(@connection_host, ':', 1) RLIKE
    CONCAT('^',
      REPLACE(
      REPLACE(
        host,
        '.', '\\.'),
        '%', '.*'),
      '$')
ORDER BY
  CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, '%', '')) ASC,
  CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, '.', '')) DESC,
  host ASC,
  CHAR_LENGTH(user) - CHAR_LENGTH(REPLACE(user, '%', '')) ASC,
  user ASC
LIMIT 1
;

There is still a slight fine-tuning to do for the above, but it should work for the majority of security setups.

The above (in rewritten form) and derivative work will, of course, be part of the next common_schema release, expected early September.