查看用戶權(quán)限茄菊、授予用戶權(quán)限疯潭、收回用戶權(quán)限命令總結(jié)：

具體詳情見下。

一面殖、查看用戶權(quán)限

（一）使用show grant命令查看用戶權(quán)限

1竖哩、查詢當(dāng)前用戶權(quán)限：

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、查看指定用戶的權(quán)限

show grants for chushiyan@localhost; 

首先創(chuàng)建用戶chushiyan脊僚，然后授予test庫的查詢權(quán)限

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

查看用戶chushiyan的權(quán)限：

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命令將授權(quán)語句都打印出來了相叁。第一條記錄是使用create user命令創(chuàng)建用戶chushiyan時(shí)默認(rèn)的授權(quán)，第二條就是我們授予的test庫的查詢權(quán)限辽幌。

（二）使用select直接查詢mysql.user表查看用戶權(quán)限

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

注釋：\G使查詢到的每列打印到單獨(dú)的行增淹，也有’;'的作用

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)

二、授予用戶權(quán)限

示例：授予test庫中所有表的增刪改查權(quán)限：

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;

授權(quán)后查看該用戶的權(quán)限：

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中都有哪些權(quán)限舶衬？

mysql中用戶的權(quán)限：

三红淡、收回用戶權(quán)限

（一）收回指定權(quán)限

收回前面授予test庫中所有表的增刪改查權(quán)限：

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

或者也可以通過下面命令收回對test庫中所有表的所有權(quán)限：

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

# 當(dāng)然省略privileges也可以：
revoke all on test.* from chushiyan@localhost;

特殊情況：

1、usage權(quán)限是用戶一經(jīng)創(chuàng)建就擁有的降铸，使用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)

（二）收回所有權(quán)限

下面這個(gè)命令會收回該用戶所有權(quán)限（當(dāng)然除了用戶一創(chuàng)建就有的usage權(quán)限）

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)