MySQL：数据库用户

数据库用户

在关系型数据库管理系统中，数据库用户（USER）是指具有特定权限和访问权限的登录账户。每个用户都有自己的用户名和密码，以便系统可以通过认证来识别他们的身份。数据库用户可以登录数据库，在其中执行各种类型的操作，如创建表格、插入、更新和删除数据、执行查询等。

数据库用户可以分为多个级别，如超级用户、普通用户、只读用户等，他们各自具有不同的权限和访问权限。超级用户具有完全控制数据库的权限，包括创建和删除数据库、用户、表格等，而只读用户只能查看数据库中的数据，无法对其进行任何修改操作。

用户权限

MySQL 的 USER 权限级别：

1. 全局性管理权限：作用于整个 MySQL 实例级别

2. 数据库级别：作用于指定的某个数据库上或者所有数据库上

3. 数据库对象级别权限：作用于指定的数据库对象上（表或者视图）

MySQL 的 USER 权限：

Mysql数据库用户权限存储在 MySQL 库的 user, db, tables_priv, columns_priv, procs_priv 这几个系统表中，MySQL实例启动后就加载到内存中:

1. user：存放用户账户信息以及全局级别（所有数据库）权限，（存放了那些用户可以访问那些数据库的权限）

2. db：数据库级别权限，决定了来自哪些主机的哪些用户可以访问此数据库

3. tables_priv：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的那张表

4. columns_priv：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的字段

5. procs_priv：存放存储过程和函数级别的权限

root 用户

root 用户是具有最高权限的用户，拥有 MySQL 中的所有权限。作为 root 用户，可以执行任何操作，包括创建用户、授予权限、修改数据库结构等。

然而，出于安全考虑，使用 root 用户进行日常任务是不推荐的，应该创建一个具有适当权限的普通用户来执行常规操作。通过这种方式，可以限制特权的滥用和减少潜在的安全风险。

创建用户

使用 CREATE USER 语句创建 MySQL 用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'

• username：要创建的用户名，
• host：用户允许登录的主机名或 IP 地址，
• password：用户的密码。

CREATE USER 'user' IDENTIFIED BY '123456';

-- 所有ip都可用账号
CREATE USER 'user'@'%' IDENTIFIED BY '123456';

-- 本地可用账号
CREATE USER 'user'@'localhost' IDENTIFIED BY '123456';

-- 指定 IP 可用账号
CREATE USER 'user'@'192.138.17.2' IDENTIFIED BY '123456';

-- 指定网段可用账号
CREATE USER 'user'@'192.138.17.%' IDENTIFIED BY '123456'; 

SQL 创建用户

CREATE USER <username> [WITH][DBA|RESOURCE|CONNECT];

只有系统的超级用户才有权创建一个新的数据库用户。

CREATE USER 命令中如果没有指定创建的新用户的权限，默认该用户拥有 CONNECT 权限。

• 拥有 CONNECT 权限的用户不能创建新用户，不能创建模式，也不能创建基本表，只能登录数据库。由数据库管理员或其他用户授予他应有的权限，根据获得的授权情况他可以对数据库对象进行权限范围内的操作。

• 拥有 RESOURCE 权限的用户能创建基本表和视图，成为所创建对象的属主，但不能创建模式 SCHEMA（数据库），不能创建新的用户。数据库对象的属主可以使用 GRANT 语句把该对象上的存取权限授予其他用户。

• 拥有 DBA 权限的用户是系统中的超级用户，可以创建新的用户、创建模式、创建基本表和视图等；DBA 拥有对所有数据库对象的存取权限，还可以把这些权限授予一般用户。

修改用户信息

修改用户名

使用 RENAME USER 语句修改用户名：

RENAME USER 'username' TO 'newname';

设置用户密码

MySQL 数据库一般在创建用户时就给用户设置了密码，如果在用户创建完成后想修改密码，可通过相应的命令来完成。

1. 修改其他用户密码（当前用户要有权限），一般在 root 用户下操作

使用 MySQL 语句

ALTER USER 'username'@'host' IDENTIFIED BY 'newPassword';

SET PASSWORD FOR 'username'@'host'=PASSWORD('newpassword');

GRANT USER ON permission 
TO 'username'@'host' IDENTIFIED BY 'newPassword';

在系统命令行下修改：

mysqladmin –u uername –h host password "newpassword"

2. 修改当前用户密码

SET PASSWORD FOR 'username' = PASSWORD('new_password');

ALTER USER user() IDENTIFIED BY 'new_password';

用户的锁定与解锁

在创建用户时或用户创建后可以将用户锁定，用户被锁定后，此用户无法完成登录：

-- 在创建用户时锁定用户
CREATE USER 'username'@'host' IDENTIFIED 'password' 
ACCOUNT LOCK;

-- 用户创建后，锁定用户
ALTER USER 'username'@'host' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'username'@'host' ACCOUNT LOCK;

密码过期策略

自 MySQL 5.7.4 版本开始，MySQL 引入了默认密码过期策略，用于增强安全保护机制。

默认过期策略要求新创建的 MySQL 用户在第一次登录时强制更改其初始密码，以避免安全漏洞。

当创建新用户时，MySQL 会为其分配一个默认密码，并将用户设置为需要修改密码。一旦用户登录 MySQL 服务器，就会提示用户修改其初始密码。如果用户在规定时间内（default_password_lifetime 变量定义的时间，默认为 360 天）没有更改密码，系统将拒绝该用户的登录请求。

设置密码过期策略

Mysql 数据库用户同系统用户一样，可以设置密码过期策略，密码的过期天数可以在配置文件里面设置，也可以通过命令设置。

配置文件中设置

选项：default_password_lifetime="过期天数" ：如果设置为0，则密码永不过期。

通过 MySQL 命令设置

通过命令为每个用户设置密码的过期天数则会覆盖系统配置文件中的设置。

-- 设置过期天数
ALTER USER 'username'@'localhost' PASSWORD
EXPIRE INTERVAL number day;

-- 设置密码不过期
ALTER USER 'username'@'localhost' PASSWORD
EXPIRE never; 

-- 默认过期策略
ALTER USER 'username'@'localhost' PASSWORD
EXPIRE DEFAULT; 

-- 手动强制密码过期
ALTER USER 'username'@'localhost' PASSWORD EXPIRE; 

自定义密码过期策略

如果存在自定义密码策略，则 MySQL 会使用自定义策略，而不是默认密码过期策略。如果需要禁用默认密码过期策略，可以使用以下命令：

SET GLOBAL default_password_lifetime = 0;

禁用默认密码过期策略，并允许在创建或更改用户时将其密码永久化。

查看默认密码过期策略下用户的状态

要查看默认密码过期策略下用户的状态，可以使用以下命令：

SELECT User, Host, plugin, authentication_string, password_expired FROM mysql.user;

• User 和 Host 代表用户名和主机名
• authentication_string 代表用户的加密密码
• plugin 代表用户的身份验证插件
• password_expired 字段表示用户是否需要更改密码。

MySQL 数据库用户资源限制

通过修改 MySQL 数据库资源限制可以限制用户在某个时间段内连接 MySQL 数据库的时间、执行语句的次数等。

1. 设置参数：

• MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接 MySQL 的时间

• MAX_USER_CONNECTIONS：一个用户可以在同一时间连接 MySQL 实例的数量

• MAX_EXECUTION_TIME：限制单个查询执行的最大时间。

• MAX_QUERIES_PER_HOUR：查询速率限制，一个用户在一个小时内可以执行查询的次数（基本包含所有语句）

• MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句）

• MAX_ALLOWED_PACKET：限制单个数据包传输的最大大小

• QUERY_CACHE_SIZE：限制查询缓存的最大大小

• MAX_CONNECT_ERRORS：限制在指定时间内允许的连接失败次数

2. 设置 MySQL 数据库用户资源限制

设置 MySQL 数据库用户资源限制时可以在创建用户时设置，也可以在用户创建完成后通过命令设置：

-- 创建用户时进行资源控制
CREATE USER 'username'@'localhost' INDENTIFIED BY '123456'
WITH
MAX_QURIES_PER_HOUR 20
MAX_CONNECTIONS_PER_HOUR 5 
MAX_USER_CONNECTIONS 1;

-- 对已存在的用户进行资源控制
ALTER USER 'username'@localhost
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5 
MAX_USER_CONNECTIONS 1;