MySQL 8.0新特性 — 用户管理

原创

brightdeng@DBA

修改于 2020-09-21 10:20:48

1.2K1

修改于 2020-09-21 10:20:48

文章被收录于专栏：腾讯云数据库专家服务

前言

在最新的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通过操作系统认证、系统用户与普通用户的分类、权限新增、角色引入等，实现了用户管理的多样化和精细化，可以更好地实现权限分离。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

登录后参与评论

0 条评论

热度