MySQL 授予普通用户PROCESS权限

 

 

在MySQL中如何给普通用户授予查看装有用户线程/连接的权柄,当然,默认情形下show
processlist是可以查阅当前用户的线程/连接的。

 

 

mysql>
grant process on MyDB.* to test;

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

 

首先次给予这样的权力,错误原因是process权限是一个大局权限,不得以指定在某一个库上(个人测试库为MyDB),所以,把授权语句更改为如下即可:

 

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)

 

 

 

只要不给拥有授予PROESS权限
,show
processlist命令只可以看到眼前用户的线程,而予以了PROCESS权限后,使用show 
processlist就能见到有着用户的线程。官方文档的牵线如下:

 

SHOW
PROCESSLIST shows you which threads are running. You can also get this
information from the INFORMATION_SCHEMA PROCESSLIST table or the
mysqladmin processlist command. If you have the PROCESS privilege, you
can see all threads. Otherwise, you can see only your own threads (that
is, threads associated with the MySQL account that you are using). If
you do not use the FULL keyword, only the first 100 characters of each
statement are shown in the Info field.

 

 

咱俩先创立下边账号test2,然后测试如下:

 

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

 

mysql> grant select,insert,update,delete on MyDB.* to test2@'%' 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()          |

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

| test2@localhost |

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

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)

 

mysql> 

 

 

MySQL 1

 

 

 

下一场大家给用户test2授予process权限,
如下所示,再测试show processlist
就能看出所有用户的线程/连接音讯(假诺是在此以前早已建立连接的对话,必须退出重新登录,否则依然只可以看到眼前用户的线程。)

 

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)

 

mysql> 

 

 

The
PROCESS privilege
pertains to display of information about the threads executing within
the server (that is, information about the statements being executed by
sessions). The privilege enables use of SHOW PROCESSLIST or
mysqladmin
processlist
to see threads belonging to other accounts;
you can always see your own threads. The PROCESS privilege
also enables use of SHOW ENGINE.

MySQL, 

 

如上合法文档所说,假设给用户授予了PROCESS权限,
那么用户就颇具了选用SHOW ENGINES命令的权力,如下所示:

 

 

mysql> select user();

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

| user()         |

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

| test@localhost |

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

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> 
网站地图xml地图