MySQL 8.0新特性 之 用户管理

200 阅读2分钟

前言

在最新的8.0版本中,MySQL在用户管理这一块,也是引入了很多新特性,包括操作系统认证、用户分类、新的权限、角色等等,接下来我们就看一看。

用户管理

操作系统认证

在Oracle、DB2、SQL Server这些传统的商业数据库中,都包括了两种认证方式,一种是操作系统身份认证,另一种是数据库身份认证。在MySQL之前的版本中,仅仅只支持数据库身份认证方式,即通过数据库的用户名和密码进行登录;不过,在最新的8.0版本中,MySQL也引入了操作系统身份认证。

(1)安装插件

mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)

(2)创建一个数据库用户,这里必须指定为localhost

mysql> create user test@'localhost' identified with auth_socket;
Query OK, 0 rows affected (0.01 sec)

(3)创建一个同名的操作系统用户

# useradd test

(4)切换到test用户,直接登录即可,不用输入密码

# su - test
$ mysql -utest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 user();
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)

(5)切换到mysql用户,直接登录会报错1698

# su - mysql
$ mysql -utest
ERROR 1698 (28000): Access denied for user 'test'@'localhost'

(6)操作系统用户和数据库用户是否一定要同名呢?其实不一定,可以手动指定操作系统用户

mysql> alter user test@'localhost' identified with auth_socket as 'mysql';
Query OK, 0 rows affected (0.01 sec)

(7)再次切换到mysql用户,直接登录成功

# su - mysql
$ mysql -utest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 user();
+----------------+
| user()         |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)

系统用户与普通用户

在MySQL 8.0中,是对用户进行了分类,分为系统用户和普通用户,目的是为了避免出现类似"拥有drop user的用户把超级用户root删除了"或“拥有super权限的用户把超级用户的会话杀掉了”等问题。

(1)系统用户:拥有system_user权限的用户;可以对系统用户和普通用户进行操作(包括账号管理、杀会话等)。

(2)普通用户:无system_user权限的用户;只能对普通用户进行操作(包括账号管理、杀会话等)。

通过用户分类,就可以很好地将管理用户和业务用户隔离出来,实现权限分离。

权限管理

在MySQL 8.0中,引入了很多新的权限,进一步实现了权限的精细化管理,具体包括如下:

(1)CREATE ROLE:创建角色的权限

(2)DROP ROLE:删除角色的权限

(3)APPLICATION_PASSWORD_ADMIN:管理双重密码的权限,允许执行RETAIN CURRENT PASSWORD和DISCARD OLD PASSWORD语句

(4)AUDIT_ADMIN:配置企业级审计日志的权限

(5)BACKUP_ADMIN:备份锁的权限,允许执行LOCK INSTANCE FOR BACKUP语句

(6)BINLOG_ADMIN:二进制日志的权限,允许执行PURGE BINARY LOGS和BINLOG语句

(7)BINLOG_ENCRYPTION_ADMIN:二进制日志和中继日志的加密权限,允许设置binlog_encryption参数

(8)CLONE_ADMIN:克隆的权限(包括BACKUP_ADMIN和SHUTDOWN权限),允许执行CLONE语句

(9)CONNECTION_ADMIN:杀会话的权限,允许执行KILL语句

(10)FIREWALL_ADMIN:配置企业级防火墙的权限(所有用户)

(11)FIREWALL_USER:配置企业级防火墙的权限(自己)

(12)GROUP_REPLICATION_ADMIN:组复制的管理权限

(13)INNODB_REDO_LOG_ARCHIVE:重做日志的归档权限

(14)PERSIST_RO_VARIABLES_ADMIN:允许执行SET PERSIST_ONLY语句的权限

(15)SYSTEM_VARIABLES_ADMIN:允许执行SET GLOBAL、SET PERSIST、SET PERSIST_ONLY语句的权限

(16)SESSION_VARIABLES_ADMIN:允许执行SET SESSION语句的权限

(17)REPLICATION_APPLIER:主从复制的检查权限

(18)REPLICATION_SLAVE_ADMIN:主从复制的管理权限

(19)RESOURCE_GROUP_ADMIN:资源组的管理权限

(20)RESOURCE_GROUP_USER:资源组的分配权限

(21)ROLE_ADMIN:角色的管理权限

(22)SERVICE_CONNECTION_ADMIN:通过特定的管理端口连接实例的权限

欢迎你的加入,不管你是大牛还是小白都欢迎入驻

除此之外,在权限管理这一块,还支持了部分权限回收:

mysql> SET PERSIST partial_revokes = ON;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,insert on *.* to test@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> revoke insert on test.* FROM test@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for test@'%';
+--------------------------------------------+
| Grants for test@%                          |
+--------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `test`@`%`  |
| REVOKE INSERT ON `test`.* FROM `test`@`%`  |
+--------------------------------------------+

角色管理

在MySQL 8.0中,还引入了一个很重要的功能——角色;简单地来说,角色就是权限的集合;通过角色,我们可以更方便地进行权限管理。

(1)创建三个角色,分别授予业务库的读写、只读、只写权限

mysql> CREATE ROLE 'app_developer', 'app_read', 'app_write';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON app_db.* TO 'app_developer';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON app_db.* TO 'app_read';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
Query OK, 0 rows affected (0.01 sec)

(2)将上述三个角色,分别授予四个不同用户,以实现权限管理

mysql> CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT 'app_developer' TO 'dev1'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
Query OK, 0 rows affected (0.01 sec)

(3)用户登录后,可以通过下列语句手动激活角色(激活某个角色/默认角色/全部角色)

mysql> set role $role_name/default/all;

(4)也可以设置默认角色,这样每次登录时都会自动激活

mysql> set default role $role_name/default/all to $user;

(5)用户与角色之间,是可以相互转化的

总结

MySQL通过操作系统认证、系统用户与普通用户的分类、权限新增、角色引入等,实现了用户管理的多样化和精细化,可以更好地实现权限分离。