MySQL grants Normal User process permissions

In MySQL, how to give ordinary users permission to view all user threads /connections, of course, show processlist is able to view the current user's thread/connection by default.

Mysql> Grant Process on mydb.* to test;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL privileges

The first time you grant such a permission, the error is that the process permission is a global permission and cannot be specified on a library (the Personal test Library is mydb), so change the authorization statement to the following:

Mysql> Grant Process on *. * To test;

Query OK, 0 rows affected (0.01 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.01 sec)

If you do not grant proess permission to the show processlist command to see only the current user's thread, and the process permission is granted, you can see all users ' threads by using show processlist. The official documentation is described below:

SHOW processlist shows you which threads is running. You can also get this information from the INFORMATION_SCHEMA processlist table or the mysqladmin processlist command. If you had the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that's, threads associated with the MySQL account, which is using). If you don't use the full keyword, the first characters of each statement is shown in the Info field.

We first create the following account Test2, and then test the following:

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Grant Select,insert,update,delete on mydb.* to [email protected] '% ' identified by ' test2 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.01 sec)

Mysql> Select User ();

+-----------------+

| User () |

+-----------------+

| [Email protected] |

+-----------------+

1 row in Set (0.00 sec)

Mysql> show Processlist;

+----+-------+-----------+------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------+-----------+------+---------+------+-------+------------------+

| 25 | Test2 | localhost | NULL |    Query | 0 | init | Show Processlist |

+----+-------+-----------+------+---------+------+-------+------------------+

1 row in Set (0.00 sec)

Mysql> Show full processlist;

+----+-------+-----------+------+---------+------+-------+-----------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------+-----------+------+---------+------+-------+-----------------------+

| 25 | Test2 | localhost | NULL |    Query | 0 | init | Show Full Processlist |

+----+-------+-----------+------+---------+------+-------+-----------------------+

1 row in Set (0.01 sec)

then we give the user test2 the process permission, as shown below, and then test show processlist to see the thread/connection information for all users (if it is a previously connected session, you must exit the login again, or you will still see only the current user's thread.) )

Mysql> Grant Process on *. * to Test2;

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Mysql> show Processlist;

+----+-------+-----------+------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------+-----------+------+---------+------+-------+------------------+

| 19 | Root | localhost | NULL |   Sleep |       16 | | NULL |

| 22 | Test | localhost | MyDB |  Sleep |       738 | | NULL |

| 24 | Test | localhost | NULL |  Sleep |       692 | | NULL |

| 25 | Test2 | localhost | NULL |  Sleep |       531 | | NULL |

| 27 | Test2 | localhost | NULL |    Query | 0 | init | Show Processlist |

+----+-------+-----------+------+---------+------+-------+------------------+

5 rows in Set (0.00 sec)

thePROCESSprivilege pertains to display of information about the threads executing within the server (which is, information abou T the statements being executed by sessions). The privilege enables use ofSHOW processlistormysqladmin processlistTo see threads belonging to other accounts; You can always see your own threads. thePROCESSprivilege also enables use ofSHOW ENGINE.

As the official document says, if the user is granted process permissions, then the user has permission to use the show Engines command, as shown here:

Mysql> Select User ();

+----------------+

| User () |

+----------------+

| [Email protected] |

+----------------+

1 row in Set (0.00 sec)

Mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Engine | Support | Comment | Transactions | XA | savepoints |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV Storage Engine | NO | NO | NO |

| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO |

| Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |

 | archive            | yes     | Archive Storage engine                                           | no           | no   | no         | 

| Performance_schema | YES | Performance Schema | NO | NO | NO |

| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |

+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+

9 Rows in Set (0.00 sec)

MySQL grants Normal User process permissions