用户和权限为用户授权

30.3　为用户授权

MySQL支持在创建用户后为用户赋予相应的权限，比如对数据库的查询、修改等权限。在MySQL中使用GRANT语句为用户授权。

注意：为用户授权的语法格式与使用GRANT语句创建用户的语法格式相同，不再赘述。另外，需要注意的是，在MySQL中，拥有GRANT权限的用户才可以使用GRANT语句为其他用户授权。MySQL中的各种权限，例如CREATE权限、DROP权限、ALTER权限、DELETE权限等，读者可以参见网址https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html，这里不再赘述每种权限的说明。

30.3.1　权限层级

在MySQL中，可以将用户的权限分为多个层级，本节就简单介绍下MySQL中的权限层级。

1．全局层级

·作用域是MySQL中的所有数据库。

·权限存储在mysql数据库下的user数据表中。

·授予权限使用GRANT ALL ON *.*语句。

·撤销权限使用REVOKE ALL ON *.*语句。

2．数据库层级

·作用域是某个特定的数据库。

·权限存储在mysql数据库下的db数据表中。

·授予权限使用GRANT ALL ON database_name.*语句。

·撤销权限使用REVOKE ALL ON database_name.*语句。

3．数据表层级

·作用域是数据库中某个特定的数据表。

·权限存储在mysql数据库下的tables_priv数据表中。

·授予权限使用GRANT ALL ON database_name.table_name语句。

·撤销权限使用REVOKE ALL ON database_name.table_name语句。

4．字段层级

·作用域是数据库下某张表的特定字段。

·权限存储在mysql数据库下的columns_priv数据表中。

·授予权限时，必须在权限名称后面跟上小括号，并在小括号中写上列名称。例如，使用GRANT SELECT(column_name) ON database_name.table_name语句，为用户赋予database_name数据库下table_name数据表中column_name字段的查询权限。

·撤销权限时，需要指定与授予权限时相同的列。例如使用REVOKE SELECT(column_name) ON database_name.table_name语句，为用户撤销database_name数据库下table_name数据表中column_name字段的查询权限。

5．子程序层级

·作用域是存储过程和函数。

·权限存储在mysql数据库下的procs_priv数据表中。

·权限包括：CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT。

MySQL中支持使用GRANT语句为用户授予相应的权限，也可以直接操作MySQL中的权限表为用户授权。

30.3.2　使用GRANT语句为用户授权

使用GRANT语句为用户授权的语法格式，与使用GRANT语句创建用户的语法格式相同，不再赘述，直接举例说明。

（1）为用户名为binghe的用户赋予在所有数据库上的执行权限，并且只能从本地服务器连接MySQL。

mysql> GRANT ALL PRIVILEGES ON *.* TO binghe@localhost;
Query OK, 0 rows affected (0.14 sec)

查看binghe用户所拥有的权限。

mysql> SELECT * FROM mysql.user WHERE user = 'binghe' AND host = 'localhost' \G
*************************** 1. row ***************************
                  Host: localhost
                  User: binghe
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
################省略部分输出结果#################

结果显示，binghe用户除了没有GRANT权限外，其他的所有权限都具有。

（2）为binghe用户赋予GRANT权限。

mysql> GRANT ALL PRIVILEGES ON *.* TO binghe@localhost WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，查看binghe用户所具有的权限。

mysql> SELECT * FROM mysql.user WHERE user = 'binghe' AND host = 'localhost' \G
*************************** 1. row ***************************
                  Host: localhost
                  User: binghe
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
###################省略部分输出结果########################

结果显示，binghe用户具有了GRANT权限。

（3）为binghe用户设置密码。

mysql> GRANT ALL PRIVILEGES ON *.* TO binghe@localhost IDENTIFIED BY '@Binghe123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看binghe用户的密码设置情况。

mysql> SELECT * FROM mysql.user WHERE user = 'binghe' AND host = 'localhost' \G
###############省略部分输出结果################
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *0DEB06AA6E096EB2F26EACEE157143ADB9481B5B
      password_expired: N
 password_last_changed: 2020-02-02 15:44:31
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

（4）为binghe用户赋予对goods数据库中的所有表进行增、删、改、查的权限，并且密码为@Binghe123456。

mysql> GRANT INSERT, DELETE, UPDATE, SELECT ON goods.* to 'binghe'@'localhost' IDENTIFIED BY 
'@Binghe123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看binghe用户所具有的权限。

mysql> SHOW GRANTS FOR 'binghe'@'localhost' \G
*************************** 1. row ***************************
Grants for binghe@localhost: GRANT USAGE ON *.* TO 'binghe'@'localhost'
*************************** 2. row ***************************
Grants for binghe@localhost: GRANT SELECT, INSERT, UPDATE, DELETE ON `goods`.* TO 'binghe'@'localhost' 
WITH GRANT OPTION
2 rows in set (0.00 sec)

结果显示，binghe用户具有对goods数据库的增、删、改、查权限，并且具有GRANT权限。

（5）指定binghe用户在192.168.175IP段内可以连接MySQL服务。

mysql> GRANT INSERT, DELETE, UPDATE, SELECT ON goods.* to 'binghe'@'192.168.175.%' IDENTIFIED BY 
'@Binghe123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

（6）指定binghe用户可以在所有主机上连接MySQL服务。

mysql> GRANT INSERT, DELETE, UPDATE, SELECT ON goods.* to 'binghe'@'%' IDENTIFIED BY '@Binghe123456' 
WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

（7）为binghe权限授予SUPER、PROCESS和FILE权限。

mysql> GRANT SUPER, PROCESS, FILE ON *.* to 'binghe'@'%';
Query OK, 0 rows affected (0.00 sec)

注意：SUPER、PROCESS和FILE这3个权限是数据库的管理权限，为用户授予这3个权限时，不能指定某个数据库，否则MySQL会报错。

mysql> GRANT SUPER, PROCESS, FILE ON goods.* to 'binghe'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

另外，如果没有特殊情况，尽量不要为普通用户授予SUPER、PROCESS和FILE权限。

（8）为binghe_login用户授予本地登录权限，密码为@Binghe123456。

mysql> GRANT USAGE ON *.* TO 'binghe_login'@'localhost' IDENTIFIED BY '@Binghe123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

此时，binghe_login用户只能用于数据库登录，无法对数据库进行任何操作。

30.3.3　通过操作权限表为用户授权

例如，为binghe_goods用户授予对goods数据库的增、删、改、查权限，并且可以在任何主机上连接MySQL服务，设置其密码为@Binghe123456。使用GRANT语句实现。

mysql> GRANT INSERT, DELETE, UPDATE, SELECT ON goods.* TO 'binghe_goods'@'%' IDENTIFIED BY '@Binghe123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

直接操作权限表。

mysql> INSERT INTO mysql.db
    -> (host, db, user, select_priv, insert_priv, update_priv, delete_priv)
    -> VALUES
    -> ('%', 'goods', 'binghe_goods', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 rows affected (0.00 sec)

注意：在MySQL中为用户授权时，直接操作权限表的方式不常见，不再赘述。