MySQL 8.0用户管理

程裕强

发布于 2021-11-15 17:05:01

5320

发布于 2021-11-15 17:05:01

文章被收录于专栏：大数据学习笔记

1、禁止root用户远程访问

（1）在安装MySQL数据库软件时，我们已经设置了禁止root远程登录

参见，https://cloud.tencent.com/developer/article/1901455

（2）root用户只能在服务器端访问

（3）远程访问被禁止

2、新建用户

mysql> CREATE USER 'employees'@'%' IDENTIFIED BY '123' ;
Query OK, 0 rows affected (0.02 sec)
 
mysql> CREATE USER 'employees_read'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'dbadmin'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | dbadmin          | mysql_native_password |
| %         | employees        | caching_sha2_password |
| %         | employees_read   | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
7 rows in set (0.00 sec)

mysql>

mysql> select User,authentication_string from mysql.user \G
*************************** 1. row ***************************
                 User: dbadmin
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
*************************** 2. row ***************************
                 User: employees
authentication_string: $A$005$vN1/%\U7R.+z10[ZcrtNBpjENqYGVvJ6f6hA3Sp.a3bqfkeMha90skN4D2
*************************** 3. row ***************************
                 User: employees_read
authentication_string: $A$005$7<~zK*%ET3eSdnmo
%^NN1WbH34KmTsb1WkgQupwFGq.QzNekCc34fcyP2fYS2
*************************** 4. row ***************************
                 User: mysql.infoschema
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 5. row ***************************
                 User: mysql.session
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 6. row ***************************
                 User: mysql.sys
authentication_string: $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
*************************** 7. row ***************************
                 User: root
authentication_string: $A$005$8ivIx*+zb
                                       36DBgFXUk/vnm7kAYhFV8tYO9hpav/zRjsWoJ5EPBCxpZ1
7 rows in set (0.00 sec)

mysql>

用户的密码都是以密文形式存储。

3、授权

mysql> grant select(first_name, last_name) on employees.employees to 'employees_read'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert,delete,update on employees.* to 'employees'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'dbadmin'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

4、测试权限

（1）只读用户

mysql> exit
Bye
[root@node1 ~]# mysql -u employees_rea -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'employees_rea'@'localhost' (using password: YES)
[root@node1 ~]# mysql -u employees_read -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> select * from employees.employees;
ERROR 1143 (42000): SELECT command denied to user 'employees_read'@'localhost' for column 'emp_no' in table 'employees'
mysql> select first_name,last_name from employees.employees limit 10;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi     | Facello   |
| Bezalel    | Simmel    |
| Parto      | Bamford   |
| Chirstian  | Koblick   |
| Kyoichi    | Maliniak  |
| Anneke     | Preusig   |
| Tzvetan    | Zielinski |
| Saniya     | Kalloufi  |
| Sumant     | Peac      |
| Duangkaew  | Piveteau  |
+------------+-----------+
10 rows in set (0.00 sec)

mysql> desc employees.employees;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(14) | NO   |     | NULL    |       |
| last_name  | varchar(16) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

（2）employees用户

[root@node1 ~]# mysql -u employees -p123 -e "select * from employees.employees limit 10"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
[root@node1 ~]#

（3）验证dbadmin的远程访问

（4）查看用户已授权限

mysql> show grants for employees@'%' ;
+--------------------------------------------------------------------------+
| Grants for employees@%                                                   |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `employees`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `employees`@`%` |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql> show grants for root@'localhost' \G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, 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, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.00 sec)

mysql>

mysql> show grants for dbadmin@'%' \G
*************************** 1. row ***************************
Grants for dbadmin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, 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, CREATE ROLE, DROP ROLE ON *.* TO `dbadmin`@`%`
*************************** 2. row ***************************
Grants for dbadmin@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dbadmin`@`%`
2 rows in set (0.00 sec)

mysql>

5、修改用户的加密规则

MySQL 8.0 默认身份认证插件是 caching_sha2_password。用户远程访问MySQL服务器时，会报错。

两种解决方法：

5.1 创建用户时，指定加密规则

例如上面创建的dbadmin用户

create user 'dbadmin'@'%' identified with mysql_native_password by '123456';

5.2 修改配置

（1）修改default_authentication_plugin配置项

[root@node1 ~]# vi /etc/my.cnf

将[mysqld]节点下的default_authentication_plugin=mysql_native_password的注释去掉，保存退出。

（2）然后重启MySQL服务

[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]#

（3）新建用户

mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | dbadmin          | mysql_native_password |
| %         | employees        | caching_sha2_password |
| %         | employees_read   | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
7 rows in set (0.00 sec)

mysql> drop user employees@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'employees'@'%' IDENTIFIED BY '123' ;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | dbadmin          | mysql_native_password |
| %         | employees        | mysql_native_password |
| %         | employees_read   | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
7 rows in set (0.00 sec)

mysql>

mysql> grant select,insert,delete,update on employees.* to 'employees'@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql>