MySQL 用户与权限管理

作者：UStarGao 日期：2021-03-05 14:18:54 人气：栏目：MySQL

一、创建用户

语法：CREATE USER '$user'@'% ' IDENTIFIED BY '$passwprd';

MySQL [(none)]> CREATE USER 'DB1'@'% ' IDENTIFIED BY 'starcto@163.com';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select host,user from mysql.user;
+-----------+----------------+
| host      | user           |
+-----------+----------------+
| %         | DB1            |
| %         | root           |
+-----------+----------------+
5 rows in set (0.00 sec)

二、权限管理

2.1 给用户授权

MySQL 赋权原则：自己有什么权限，才能给别人什么权限。

（1）查看用户拥有的权限

MySQL [(none)]> show grants for 'root'@'%'\G 
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.01 sec)

（2）用户授权

MySQL [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, ALTER, INDEX on *.* to 'DB1'@'%' WITH GRANT OPTION;  
Query OK, 0 rows affected (0.01 sec)

（3）root用户获取超级权限

update mysql.user set Super_priv='Y' where User='root'; 
flush privileges;

MySQL5.7官网介绍：https://dev.mysql.com/doc/refman/5.7/en/grant.html

2.2 查看用户权限

（1）查看user表权限信息

MySQL [(none)]> select * from mysql.user\G;
*************************** 4. row ***************************
                  Host: %
                  User: DB1
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          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
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *9877A31BDC660A746D19727E947CDFBA34A1D0F7
      password_expired: N
 password_last_changed: 2021-06-26 00:00:32
     password_lifetime: NULL
        account_locked: N

（2）查看'DB1'@'%'用户拥有的权限

MySQL [(none)]> SHOW GRANTS FOR 'DB1'@'%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for DB1@%                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON *.* TO 'DB1'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（3）查看当前用户拥有的权限

MySQL [(none)]> show grants\G
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.00 sec)

（4）查看当前连接的用户和访问域

MySQL [(none)]> select user() ,current_user();
+------------------+----------------+
| user()           | current_user() |
+------------------+----------------+
| root@10.25.25.25 | root@%         |
+------------------+----------------+
1 row in set (0.01 sec)

（5）经典案例分析

背景：创建了一个DB1用户，授予了create权限，但是业务创建表时扔报错权限不足，经排查发现DB1用户有两个访问域，即'DB1'@'%'和'DB1'@'10.%'。'DB1'@'%'访问域授予了create权限，'DB1'@'10.%'访问域没有授予create权限，'DB1'@'10.%'访问域的优先级比'DB1'@'%'访问域更高，所以导致业务出现了权限报错。如下：

# 'DB1'@'%'访问域有CREATE权限
MySQL [(none)]> show grants for DB1;  
+--------------------------------------------------------------------------------------------------------------------+
| Grants for DB1@%                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON *.* TO 'DB1'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


# 'DB1'@'10.%'访问域没有CREATE权限
MySQL [(none)]> show grants for 'DB1'@'10.%'; 
+---------------------------------------------------------------------------------------------------------------+
| Grants for DB1@10.%                                                                                           |
+---------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP, REFERENCES, INDEX, ALTER ON *.* TO 'DB1'@'10.%' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

访问域优先级参考官网：https://dev.mysql.com/doc/refman/5.7/en/connection-access.html

2.3. 移除授权

语法：
revoke SELECT on *.* from '$user'@'%' ;
revoke SELECT on *.* from '$user'@'x.x.%.%' ;

MySQL [(none)]> revoke SELECT on *.* from 'DB1'@'%';
Query OK, 0 rows affected (0.00 sec)

#select权限已经被回收
MySQL [(none)]> show grants for 'DB1'@'%';   
+------------------------------------------------------------------------------------------------------------+
| Grants for DB1@%                                                                                           |
+------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON *.* TO 'DB1'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL5.官网介绍：https://dev.mysql.com/doc/refman/5.7/en/revoke.html

三、更新操作

3.1 更新user访问源限制

（1）查询用户的访问域

MySQL [(none)]> select host,user from mysql.user;
+-----------+----------------+
| host      | user           |
+-----------+----------------+
| %         | DB1            |
| %         | root           |
| 10.%      | DB1            |
+-----------+----------------+
6 rows in set (0.00 sec)

（2）修改用户访问域

语法：
update user set host = 'xx.xx.%.%' where user = '$user';     #修改用户的访问域
flush privileges;

MySQL [(none)]> use mysql
MySQL [mysql]> update user set host = '10.25.25.25' where user = 'DB1';
Query OK, 1 row affected (0.00 sec)

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.2 修改用户密码

（1）MySQL 5.5/5.6

UPDATE mysql.user SET password=password('密码') WHERE user='ucloudbackup';
FLUSH PRIVILEGES;

（2）MySQL 5.7

UPDATE mysql.user SET authentication_string=password('密码') WHERE user='ucloudbackup';
FLUSH PRIVILEGES;

# 如果设置了密码过期时间，使用命令：
UPDATE mysql.user SET authentication_string=password('密码'),password_last_changed=now() WHERE user='root';
FLUSH PRIVILEGES;

（3）MySQL5.7/MySQL 8.0

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;

（4）MySQL密码密文校验

# 查询MySQL root用户密码密文
mysql> select * from mysql.user where user="root"\G;
*************************** 1. row ***************************
                  Host: %
                  User: root
                    ……
                plugin: mysql_native_password
 authentication_string: *1A48FEB61E6408357AF1C296B1D43987E2386EFD    # root密码密文
      password_expired: N
 password_last_changed: 2021-08-02 13:27:05
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

# 对MySQL root用户明文密码进行加密
mysql> select password("ucloud.cn");
+-------------------------------------------+
| password("ucloud.cn")                     |
+-------------------------------------------+
| *9C2987C998212139BD95E33BE0189FCD876F3967 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

【注】对比密文相同，则说明密码是对的；密文不同，则说明密码是错的。

四、删除user

4.1 drop

语法：drop user 'XXX'@'%';

MySQL [mysql]> select host,user from mysql.user;
+-------------+----------------+
| host        | user           |
+-------------+----------------+
| %           | root           |
| 10.25.25.25 | DB1            |
+-------------+----------------+
5 rows in set (0.00 sec)

MySQL [mysql]> drop user 'DB1'@'10.25.25.25';
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> select host,user from mysql.user;
+-----------+----------------+
| host      | user           |
+-----------+----------------+
| %         | root           |
+-----------+----------------+
4 rows in set (0.00 sec)

4.2 delete

语法：delete from user where user='XXX' and host='XXX';

区别：drop不仅会将user表中的数据删除，还会删除其他权限表的内容。而delete只删除user表中的内容，所以使用delete删除用户后需要执行FLUSH PRIVILEGES;刷新权限，否则下次使用create语句创建用户时会报错。

五、user表迁移技巧

mysql> select host,user from mysql.user;
+------+---------+
| host | user    |
+------+---------+
| %    | root    |
| %    | stargao |
+------+---------+
2 rows in set (0.00 sec)

5.1 mysqlpump工具介绍

官网介绍：https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

MySQL5.7之后多了一个备份工具：mysqlpump。它是mysqldump的一个衍生和mysqldump一样，属于逻辑备份，备份以SQL形式的文本保存。逻辑备份相对物理备份的好处是不关心undo log的大小，直接备份数据即可。它最主要的特点是：

（1）并行备份数据库和数据库中的对象的，加快备份过程。
（2）更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
（3）备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
（4）备份出来直接生成压缩后的备份文件。
（5）备份进度指示（估计值）。
（6）重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。
（7）备份可以排除或则指定数据库。

5.2 mysqlpump备份演示

[root@ansible ~]# mysqlpump -h10.25.34.23 --exclude-databases=% --users -uroot -pUCloudcn --set-gtid-purged=OFF > user.log
Dump completed in 892 milliseconds

[root@ansible ~]# cat user.log 
-- Dump created by MySQL pump utility, version: 5.7.31, linux-glibc2.12 (x86_64)
-- Dump start time: Wed Aug 25 16:43:16 2021
-- Server version: 5.7.16

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*1A48FEB61E6408357AF1C296B1D43987E2386EFD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION;
CREATE USER 'stargao'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*9C2987C998212139BD95E33BE0189FCD876F3967' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, FILE, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'stargao'@'%' WITH GRANT OPTION;
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Wed Aug 25 16:43:17 2021

5.3 pt-show-grants工具介绍

这个工具的主要作用就是能够规范的打印出授权的相关信息，方便对比不同MySQL数据库的权限是不是一样，通过这个工具我们可以轻松地将用户从一个服务器复制到另一个服务器，可以简单地从第一台服务器提取授权，并将输出管道直接导入另一台服务器，也可以做权限的版本控制。

官网介绍：https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html

5.4 pt-show-grants备份演示

[root@ansible ~]# pt-show-grants -h10.25.34.23 -uroot -pUCloudcn > user.sql

[root@ansible ~]# cat user.sql 
-- Grants dumped by pt-show-grants
-- Dumped from server 10.25.34.23 via TCP/IP, MySQL 5.7.16-ucloudrel1-log at 2021-08-25 16:54:55
-- Grants for 'root'@'%'
CREATE USER IF NOT EXISTS 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*1A48FEB61E6408357AF1C296B1D43987E2386EFD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INLOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SUPER, TRIGGER, UPDATE ON *.* TO 'root'@'%' WITH GRTION;
-- Grants for 'stargao'@'%'
CREATE USER IF NOT EXISTS 'stargao'@'%';
ALTER USER 'stargao'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*9C2987C998212139BD95E33BE0189FCD876F3967' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, EVENT, EXECUTE, FILE, INDEX, INSERT, SELECT, SHOW VIEW, TRIGGER, UPDA*.* TO 'stargao'@'%' WITH GRANT OPTION;

作者：UStarGao
链接：https://www.starcto.com/mysql/101.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud云平台推荐

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面