查看用户权限、授予用户权限、收回用户权限命令总结：

具体详情见下。

一、查看用户权限

（一）使用show grant命令查看用户权限

1、查询当前用户权限：

show grants;

示例：

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7BB4897EB74329520EE1456DDA7DC45ED2CA2AD0' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2、查看指定用户的权限

show grants for chushiyan@localhost; 

首先创建用户chushiyan，然后授予test库的查询权限

create user chushiyan@localhost identified by “123456”;
grant select on test.* to chushiyan@localhost;

查看用户chushiyan的权限：

mysql> show grants for chushiyan@localhost ;
+------------------------------------------------------------------------------------------------------------------+
| Grants for chushiyan@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chushiyan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `test`.* TO 'chushiyan'@'localhost'                                                              |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

show grants命令将授权语句都打印出来了。第一条记录是使用create user命令创建用户chushiyan时默认的授权，第二条就是我们授予的test库的查询权限。

（二）使用select直接查询mysql.user表查看用户权限

SELECT * FROM mysql.user WHERE user='chushiyan'\G

注释：\G使查询到的每列打印到单独的行，也有’;'的作用

mysql> SELECT * FROM mysql.user WHERE user='chushiyan'\G
*************************** 1. row ***************************
                 Host: localhost
                 User: chushiyan
             Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
          Reload_priv: N
        Shutdown_priv: N
         Process_priv: N
            File_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
         Show_db_priv: N
           Super_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
         Execute_priv: N
      Repl_slave_priv: N
     Repl_client_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Create_user_priv: N
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

二、授予用户权限

示例：授予test库中所有表的增删改查权限：

grant insert on test.* to chushiyan@localhost;
grant delete on test.* to chushiyan@localhost;
grant update on test.* to chushiyan@localhost;
grant select on test.* to chushiyan@localhost;

授权后查看该用户的权限：

mysql> show grants for chushiyan@localhost ;
+------------------------------------------------------------------------------------------------------------------+
| Grants for chushiyan@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chushiyan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'chushiyan'@'localhost'                                      |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

那mysql中都有哪些权限？

mysql中用户的权限：

三、收回用户权限

（一）收回指定权限

收回前面授予test库中所有表的增删改查权限：

revoke insert,delete,update,select on test.* from chushiyan@localhost;

或者也可以通过下面命令收回对test库中所有表的所有权限：

revoke all privileges on test.* from chushiyan@localhost;

# 当然省略privileges也可以：
revoke all on test.* from chushiyan@localhost;

特殊情况：

1、usage权限是用户一经创建就拥有的，使用revoke命令无法收回：

mysql> revoke usage on *.* from chushiyan@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for chushiyan@localhost ;
+------------------------------------------------------------------------------------------------------------------+
| Grants for chushiyan@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chushiyan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（二）收回所有权限

下面这个命令会收回该用户所有权限（当然除了用户一创建就有的usage权限）

revoke all ,grant option from chushiyan@localhost;

mysql> revoke all ,grant option from chushiyan@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for chushiyan@localhost ;
+------------------------------------------------------------------------------------------------------------------+
| Grants for chushiyan@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chushiyan'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)