MySql--用户管理

摘要

• MySql知识点介绍:用户管理

• 本文基于mysql-8.0.30，https://dev.mysql.com/doc/refman/8.0/en/

用户管理

• 创建用户并授权

# 创建用户并初始密码
mysql> CREATE USER 'username'@'%' IDENTIFIED BY '123456';
# 修改密码
-- 这里使用mysql_native_password插件认证
-- 不过从Mysql8.0.4开始，默认使用的是 caching_sha2_password 插件认证
-- 不过笔者在使用最新版的8.4.0时发现其默认已经不支持mysql_native_password了，所以这里需要替换为caching_sha2_password
-- 也可以在配置文件中开启`mysql-native-password=ON`
mysql> ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
# 或者直接使用缺省的配置：caching_sha2_password
mysql> ALTER USER 'username'@'%' IDENTIFIED BY 'password';

# 授权
mysql> GRANT all privileges ON *.* TO 'username'@'%';
# 刷新数据库
mysql> FLUSH PRIVILEGES;
# 查询用户权限
mysql> show grants for username@"%"\G
*************************** 1. row ***************************
Grants for username@%: 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 `username`@`%`
*************************** 2. row ***************************
Grants for username@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,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,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `username`@`%`

# 查询当前系统用户
mysql> select user,host from mysql.user;

# 允许远程访问
mysql> update mysql.user set host='%' where user='username';
mysql> FLUSH PRIVILEGES;

• 删除用户

# 方式1
mysql> delete from user where user='username' and host='%';
mysql> FLUSH PRIVILEGES;
# 方式2
mysql> drop user 'username'@'%';

• 授予root用户system_user权限，否则在做一些授权操作时会提示没有权限

mysql> grant system_user on *.* to 'root'@'localhost';
mysql> flush privileges;

• 查看当前登录的用户

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

开启管理员独立端口登录:33062

• 从 MySQL 8.0.14 开始，MySQL 服务器允许专门为管理连接配置 TCP/IP 端口。这为用于普通连接的网络接口上允许的单个管理连接提供了一种替代方法，即使已经建立了 max_connections 连接，也就是说即使max_connections已经达到最大值，该端口依旧可以登录。

• 只有在启动时设置了 admin_address 系统变量以指示管理接口的 IP 地址时，该接口才可用。如果未指定 admin_address 值，则服务器不维护管理界面。

• 只有 SERVICE_CONNECTION_ADMIN 权限的用户才允许连接。没有限制管理连接的数量。

vim /etc/my.cnf
[mysqld]
# 开启管理员使用33062端口访问数据库的权限
# 当最大连接数达到最大值时，管理员依旧可以使用该端口登录数据库
# mysql -uroot -P33062 -p 这里表示在mysql服务器上可以登录，如果要在其它机器上访问，请设置对应的ip地址，注意这里不能设置为'%'
admin_address='localhost'

mysql> show variables like 'admin%';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| admin_address          | localhost       |
| admin_port             | 33062           |
| admin_ssl_ca           |                 |
| admin_ssl_capath       |                 |
| admin_ssl_cert         |                 |
| admin_ssl_cipher       |                 |
| admin_ssl_crl          |                 |
| admin_ssl_crlpath      |                 |
| admin_ssl_key          |                 |
| admin_tls_ciphersuites |                 |
| admin_tls_version      | TLSv1.2,TLSv1.3 |
+------------------------+-----------------+

netstat -tunpl | grep 3306
tcp        0      0 127.0.0.1:33062         0.0.0.0:*               LISTEN      12037/mysqld
tcp6       0      0 :::33060                :::*                    LISTEN      12037/mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      12037/mysqld