processlist_grantees
NAME
processlist_grantees: Assigning of GRANTEEs for connected processesTYPE
ViewDESCRIPTION
processlist_grantees Lists connected processes, as with PROCESSLIST. For each process, it analyzes the connected GRANTEE. It does so by inspecting the user+host presented by PROCESSLIST, and matches those values, in a similar algorithm to that of the MySQL server, to the list of known accounts.
MySQL's PROCESSLIST fails to make the connection between a process ID and the account for which this process is assigned. It only tells us the connection's HOST and the specified USER. But these do not necessarily map directly to the known grantees: MySQL accounts can specify wildcards for both user and host.
MySQL offers the USER() and CURRENT_USER() functions, which provide desired data, but only for current connection.
processlist_grantees bridges the two by utilizing match_grantee() for each process in the PROCESSLIST. It also provides with additional useful information about the matched account.
STRUCTURE
mysql> DESC common_schema.processlist_grantees; +---------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------+---------------+------+-----+---------+-------+ | ID | bigint(4) | NO | | 0 | | | USER | varchar(16) | NO | | | | | HOST | varchar(64) | NO | | | | | DB | varchar(64) | YES | | NULL | | | COMMAND | varchar(16) | NO | | | | | TIME | int(7) | NO | | 0 | | | STATE | varchar(64) | YES | | NULL | | | INFO | longtext | YES | | NULL | | | GRANTEE | varchar(81) | YES | | | | | grantee_user | char(16) | YES | | | | | grantee_host | char(60) | YES | | | | | is_super | decimal(23,0) | YES | | NULL | | | is_repl | int(1) | NO | | 0 | | | is_current | int(1) | NO | | 0 | | | sql_kill_query | varbinary(31) | NO | | | | | sql_kill_connection | varbinary(25) | NO | | | | +---------------------+---------------+------+-----+---------+-------+
SYNOPSIS
Rows of this view map directly to rows in INFORMATION_SCHEMA.PROCESSLIST. This view extends PROCESSLIST by including all existing columns, and adding some of its own.
Columns of this view:
- ID: process ID, as in PROCESSLIST
- USER: name of connected user, as in PROCESSLIST
- HOST: connection's host + port, as in PROCESSLIST
- DB: connection's current schema, as in PROCESSLIST
- COMMAND: connection's command, as in PROCESSLIST
- TIME: current command runtime in seconds, as in PROCESSLIST
- STATE: connection's state, as in PROCESSLIST
- INFO: command info, as in PROCESSLIST
- GRANTEE: account which is calculated by match_grantee() to match this process.
- grantee_user: user part of the GRANTEE.
- grantee_host: host part of the GRANTEE. This does not include port specification.
- is_super: 1 if the grantee has the SUPER privilege; 0 if not.
- is_repl: 1 if the connection appears to be a replication thread; 0 if not.
- is_current: 1 if the process is the current connection; 0 if not.
- sql_kill_query:
generated statement to kill current query.
Use with eval() to apply query. - sql_kill_connection:
generated statement to kill current connection.
Use with eval() to apply query.
EXAMPLES
Show grantees for all processes:
mysql> SELECT ID, USER, HOST, GRANTEE, grantee_user, grantee_host,
is_super, is_repl, sql_kill_query, sql_kill_connection
FROM
common_schema.processlist_grantees;
+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
| ID | USER | HOST | GRANTEE | grantee_user | grantee_host | is_super | is_repl | sql_kill_query | sql_kill_connection |
+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
| 650472 | replica | jboss00.myweb:34266 | 'replica'@'%.myweb' | replica | %.myweb | 0 | 1 | KILL QUERY 650472 | KILL 650472 |
| 692346 | openarkkit | jboss02.myweb:43740 | 'openarkkit'@'%.myweb' | openarkkit | %.myweb | 0 | 0 | KILL QUERY 692346 | KILL 692346 |
| 842853 | root | localhost | 'root'@'localhost' | root | localhost | 1 | 0 | KILL QUERY 842853 | KILL 842853 |
| 843443 | jboss | jboss03.myweb:40007 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 843443 | KILL 843443 |
| 843444 | jboss | jboss03.myweb:40012 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 843444 | KILL 843444 |
| 843510 | jboss | jboss00.myweb:49850 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 843510 | KILL 843510 |
| 844559 | jboss | jboss01.myweb:37031 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844559 | KILL 844559 |
| 844577 | jboss | jboss03.myweb:38238 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844577 | KILL 844577 |
| 844592 | jboss | jboss02.myweb:34405 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844592 | KILL 844592 |
| 844593 | jboss | jboss01.myweb:37089 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844593 | KILL 844593 |
| 844595 | jboss | jboss04.myweb:46488 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844595 | KILL 844595 |
| 844596 | jboss | jboss00.myweb:41046 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844596 | KILL 844596 |
| 844600 | jboss | jboss01.myweb:37108 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844600 | KILL 844600 |
| 844614 | jboss | jboss04.myweb:46500 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844614 | KILL 844614 |
| 844618 | jboss | jboss02.myweb:44449 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844618 | KILL 844618 |
| 844620 | jboss | jboss02.myweb:44456 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844620 | KILL 844620 |
| 844626 | jboss | jboss04.myweb:46526 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844626 | KILL 844626 |
| 844628 | jboss | jboss02.myweb:44466 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844628 | KILL 844628 |
| 844631 | jboss | jboss03.myweb:38291 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844631 | KILL 844631 |
+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
In the above, 'root'@'localhost' is a trivial match, but other connections are mapped to accounts based on wildcards. All jboss users are connected from jboss??.myweb servers, and are matched to the 'jboss'@'%.myweb' account.