A MySQL account is a user\/host combination. A MySQL connection is done by a user connecting from some host.<\/p>\n
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:<\/p>\n
\nCREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456';<\/pre>\n<\/blockquote>\nThe host as specified in the above account is a wildcard host. A connection by the ‘temp’<\/strong> user from ‘10.0.0.3’<\/strong> can map into that account. It thus happens that the connected user is ‘temp’@’10.0.0.3’<\/strong>, yet the assigned account is ‘temp’@’10.0.0.%’<\/strong>.<\/p>\n
MySQL provides with the USER()<\/strong> and CURRENT_USER()<\/strong> 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<\/a>.<\/p>\n
The problem<\/h4>\n
And the trouble is: MySQL only provides this functionality for the current session<\/em>. Surprisingly, given a user\/host combination, I cannot get MySQL to tell me which account matches those details.<\/p>\n
The inconsistency<\/h4>\n
And I care because there is an inconsistency. Namely, when I do SHOW PROCESSLIST<\/strong> MySQL tells me the user & host from which the connection is made. It does not<\/em> tell me the account for which the process is assigned.<\/p>\n
\nroot@mysql-5.1.51> SHOW PROCESSLIST;\r\n+----+------+----------------+---------------+---------+------+-------+------------------+\r\n| Id | User | Host\u00a0\u00a0\u00a0\u00a0\u00a0 | db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Command | Time | State | Info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------+----------------+---------------+---------+------+-------+------------------+\r\n| 16 | temp | 10.0.0.3:54142 | common_schema | Query\u00a0\u00a0 |\u00a0\u00a0\u00a0 0 | NULL\u00a0 | SELECT id, ... |\r\n+----+------+----------------+---------------+---------+------+-------+------------------+<\/pre>\n<\/blockquote>\nThe 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.<\/p>\n
I got into this because of a suggestion by Matthew Montgomery<\/a> to include a tool<\/a> of his into common_schema<\/a>.The tool says “Kill all slow queries which are not executed by users with the SUPER privilege”.<\/p>\n
Great idea! But then, how do you identify such users?<\/p>\n
The tool attempts to find an exact match between INFORMATION_SCHEMA.PROCESSLIST<\/strong>‘s user\/host and INFORMATION_SCHEMA.USER_PRIVILEGES<\/strong>‘s user\/host. This will do well when the account is ‘root’@’localhost’<\/strong>, but less so when it is ‘maatkit’@’%.mydomain’<\/strong>.<\/p>\n
Unfortunately, many things fall under SUPER<\/strong>‘s attention, and such accounts as monitoring, backup, management may require that privilege.<\/p>\n
Account matching<\/h4>\n
Matching is achievable, but not completely trivial. If you’re not aware of this, you should note that ‘temp’@’10.0.0.3’<\/strong> can match any of the following:<\/p>\n
\n+------+----------+\r\n| temp | 10.0.%\u00a0\u00a0 |\r\n| %\u00a0\u00a0\u00a0 | 10.0.0.3 |\r\n| temp | 10.0.0.3 |\r\n| temp | 10.0.0.% |\r\n+------+----------+<\/pre>\n<\/blockquote>\nAnd the rule is we must match by most specific host first, then by most specific user. The order of matching should be this:<\/p>\n
\n+------+----------+\r\n| temp | 10.0.0.3 |\r\n| %\u00a0\u00a0\u00a0 | 10.0.0.3 |\r\n| temp | 10.0.0.% |\r\n| temp | 10.0.%\u00a0\u00a0 |\r\n+------+----------+<\/pre>\n<\/blockquote>\nThe first row to match our connection’s user\/host is the matched account.<\/p>\n
The good news<\/h4>\n
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.<\/p>\n
The code<\/h4>\n
The following query assumes you have two session variables: @connection_user<\/strong> and @connection_host<\/strong>.<\/p>\n
\nSELECT\r\n user, host\r\nFROM\r\n mysql.user\r\nWHERE\r\n @connection_user RLIKE\r\n CONCAT('^',\r\n REPLACE(\r\n user,\r\n '%', '.*'),\r\n '$')\r\n AND SUBSTRING_INDEX(@connection_host, ':', 1) RLIKE\r\n CONCAT('^',\r\n REPLACE(\r\n REPLACE(\r\n host,\r\n '.', '\\\\.'),\r\n '%', '.*'),\r\n '$')\r\nORDER BY\r\n CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, '%', '')) ASC,\r\n CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, '.', '')) DESC,\r\n host ASC,\r\n CHAR_LENGTH(user) - CHAR_LENGTH(REPLACE(user, '%', '')) ASC,\r\n user ASC\r\nLIMIT 1\r\n;<\/pre>\n<\/blockquote>\nThere is still a slight fine-tuning to do for the above, but it should work for the majority of security setups.<\/p>\n