mysql笔记：13. 用户管理

MySQL是一个多用户数据库管理系统，不同的用户分配不同的权限，其用户大致分为root用户和普通用户。root用户是超级管理员，拥有所有权限，如创建用户、删除用户、管理用户等。普通用户只有被授予的指定权限。
在校学习和日常开发的时候，为了方便，一般都可以通过root用户登录数据库进行相关的操作；但生产环境为保证数据库的安全，需要对不同用户的操作权限进行合理的管理，让用户只能在指定的权限范围内操作。

一、user表

安装MySQL时，系统自动创建一个名称为mysql的数据库，该数据库主要用于维护数据库的用户及权限的控制和管理。它包含的数据表有user、db、host等，其中user表保存了所有用户信息，即包含了允许连接到服务器的账号信息，也有一些全局级的权限信息，全局级的权限适用于给定服务器中的所有数据库。

1. 用户字段

user表存储了用户连接MySQL数据库时需要输入的信息。

字段名	数据类型	允许NULL	默认值	说明
Host	char(255)	否	-	主机名，主键
User	char(32)	否	-	用户名，主键
authentication_string	text	是	-	密码

当用户与MySQL服务端建立连接时，MySQL将用户输入的用户名、主机名、密码与user表用户字段中存储的值进行匹配。只有这三个字段全部匹配成功，才允许用户建立连接。

2. 权限字段

user表的权限字段包括Select_priv、Insert_priv、Update_priv、Delete_priv等以priv结尾的字段，这些字段决定了用户的权限，包括查询权限、修改权限、关闭服务等。

可以通过show create table user查看这些字段信息。
user表对应的权限是针对所有数据库的，并且这些权限字段的数据类型都是ENUM，取值为N或Y；默认值是N。

3. 安全字段

user表的安全字段包含安全连接、身份验证和密码相关等字段，主要用于管理用户的安全信息。
包括SSL加密连接相关、X509证书相关、插件相关、密码相关（password开头的字段）、是否锁定、用户属性等字段。

4. 资源控制字段

user表的资源控制字段包含以max开头的4个字段，它们用于限制用户对服务器资源的使用，防止用户登录服务器后的不法操作或不合规范的操作导致服务器资源的浪费。

字段名	数据类型	默认值	描述
max_questions	int	0	每小时允许用户执行查询操作的次数
max_updates	int	0	每小时允许用户执行更新操作的次数
max_connections	int	0	每小时允许用户执行连接操作的次数
max_user_connections	int	0	允许单个用户同时建立连接的数量

5. 默认用户

MySQL数据库安装配置成功后，MySQL就创建了几个默认用户。

主机	用户名	说明
localhost	mysql.infoschema	MySQL数据库的系统用户，用来管理和访问系统自带的information_schema数据库
localhost	mysql.session	MySQL的插件将会使用该用户来访问MySQL数据库服务器。客户端不能直接使用该用户进行连接
localhost	mysql.sys	用于MySQL数据库中对象的定义。使用该用户可避免数据库管理员重命名或者删除root用户时发生的问题。客户端不能直接使用该用户进行连接
localhost	root	MySQL的超级用户，用于管理MySQL数据库。该用户拥有所有权限，可执行任何操作。不建议使用该用户操作MySQL数据库

二、创建用户

1. CRESTE USER

MySQL中的用户信息都保存在mysql.user表中，因此可以利用root用户向user表中插入记录的方式创建用户。但为了保证数据安全，推荐使用更安全的CRESTE USER语句创建用户，语法：

CREATE USER 'username'@'hostname' [IDENIFIED BY [PASSWORD] 'password'] [, 'username'@'hostname' [IDENIFIED BY [PASSWORD] 'password'] ... ]

• username：新创建用户的名称
• hostname：主机名
• IDENIFIED BY：用于设置用户的密码
• PASSWORD：表示使用哈希值设置密码，这是可选项。如果密码是一个普通字符串，就不需要使用PASSWORD关键字
• password：用户登录时使用的密码，需要用单引号

使用CREATE USER语句创建新用户时，每创建一个新用户，都会在mysql.user表中添加一条记录并同时自动修改相应的授权表。

CREATE USER语句创建的新用户默认情况下只有连接权限。

授权用户的组成

MySQL的授权用户由两部分组成：用户名和主机。基本语法：'username'@'hostname'

• 可以使用localhost代表本机，可以使用127.0.0.1代表IPv4的本机地址，也可以使用::1代表IPv6的本机地址。
• hostname中允许使用通配符%和_。例如：192.168.10.%代表所有来自192.168.10网段的主机，%.csdn.net代表所有来自csdn.net这个域名下的主机。
• 一般情况下，单引号不是必需的，但如果其中包含特殊字符则是必需的。

2. GRANT

在MySQL8.0之前的版本中，可以使用GRANT语句创建用户，并在创建用户时对用户授权。语法格式：

GRANT privileges ON database.table TO 'username'@'hostname' [IDENIFIED BY [PASSWORD] 'password'] [, 'username'@'hostname' [IDENIFIED BY [PASSWORD] 'password'] ... ]

• privileges：表示该用户具有的权限信息
• ** database.table**：表示新用户的权限范围表

用户使用GRANT语句创建新用户时，必须有GRANT权限。

三、重命名用户

在MySQL中，可以使用RENAME USER语句修改一个或多个已经存在的MySQL用户，语法格式：

mysql> RENAME USER old_user TO new_user;

可以通过help rename user查看使用帮助信息。

使用RENAME USER语句，必须拥有UPDATE权限或全局CREATE USER权限。

四、修改用户密码

1. mysqladmin

mysqladmin通常用于执行一些管理性的任务，以及显示服务器状态等。使用mysqladmin命令修改密码的语法：

> mysqladmin -u username [-h hostname] -p password new_password

2. ALTER USER

ALTER USER username IDENTIFIED BY 'new_password';

要求用户拥有修改mysql.user表的权限。

3. SET PASSWORD

SET PASSWORD [FOR user] auth_option
    [REPLACE 'current_auth_string']
    [RETAIN CURRENT PASSWORD]

auth_option: {
    = 'auth_string'
  | TO RANDOM
}

# 示例：修改当前用户的密码
SET PASSWORD = 'auth_string';

4. UPDATE

使用root用户登录后，通过UPDATE语句直接修改mysql.user表中的数据，语法：

UPDATE mysql.user SET authentication_string=PASSWORD('new_password')
    WHERE User='username' AND Host='hostname';

1. 修改密码后，还需要使用FLUSH PRIVILEGES重新加载权限表。
2. MySQL8.0及后续的版本中已经废弃PASSWORD()函数，因此不推荐第4种方法。

5. 密码过期设置与用户锁定

5.1 密码过期设置

MySQL的用户密码过期功能，允许设置用户密码的过期时间。

mysql> select host, user, password_expired, password_lifetime from user;

默认情况下，MySQL并没有启用用户的密码过期设置。

# 启用root用户的密码过期策略，并设定密码过期时间为30天
mysql> alter user 'root'@'localhost' password expire interval 30 day;

一旦用户启用了密码过期的策略或者密码过期后，在用户没有设置新密码前不能运行任何查询语句。
MySQL数据库还有一个全局变量default_password_lifetime来设置所有用户的密码过期时间，此全局变量可以设置一个全局的自动密码过期策略。该变量默认值是0，表示用户密码永不过期。

# 查看配置
mysql> show variables like 'default_password_lifetime';

# 修改配置：设置所有用户密码过期的时间是90天。
mysql> set global default_password_lifetime=90;

5.2 用户的锁定与解锁

# 锁定
mysql> alter user tomy account lock;

# 解锁
mysql> alter user tomy account unlock;

五、删除用户

在MySQL中，通常会创建多个普通用户来管理数据库。如果某些用户已经没必要再使用，就可以将其删除。

1. DROP USER

DROP USER与DROP DATABASE类似，语法如下：

DROP USER 'username'@'hostname' [, 'username'@'hostname', ...];

执行删除操作的用户必须拥有DROP USER权限。

2. DELETE

用户信息保存在mysql.user表，使用DELETE语句删除表中的数据，即删除了用户。

DELETE FROM mysql.user WHERE host='hostname' AND user='username';

执行DELETE操作的用户必须拥有mysql.user表的DELETE权限。

六、root密码丢失怎么办

root用户是超级管理员，拥有很多权限，它丢了怎么办呢？MySQL提供了对应的处理机制，可以通过如下特殊方法登录MySQL服务器，然后重新为root用户设置密码。

1. 停止MySQL服务

net stop MySQL8
# 或Linux系
systemctl stop mysqld

• MySQL8：这是MySQL服务的名称，根据安装MySQL时定义的服务名称调整命令。

2. 使用--skip-grant-tables登录

mysqld --shared-memory --skip-grant-tables

执行该命令后，命令行或终端窗口进入类似阻塞状态，不要关闭窗口。
也可以编辑配置文件/etc/my.cnf，在末尾添加一行

skip-grant-tables

不过，这种方式没有命令行方便，所以不推荐。

3. 登录MySQL

重新打开一个命令行或终端窗口，使用免密登录MySQL服务器

mysql -uroot

4. 加载权限表

FLUSH PRIVILEGES;

5. ALTER USER重置密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

重置成功后，使用exit退出服务器。

6. 重新启动MySQL服务

关闭刚才打开的两个窗口，重新打开另一个，然后启动MySQL服务。

net start MySQL8

MySQL服务启动后，就可以使用root用户的新密码进行登录。