MySQL 8.0 权限与安全管理

MySQL的权限与安全管理是数据库管理的重要组成部分，它确保只有授权用户才能访问和操作数据库资源。本教程将详细介绍MySQL 8.0的权限系统、账户管理、权限管理、访问控制和安全性提升方法。

MySQL权限系统概述

MySQL的权限系统用于控制用户对数据库资源的访问权限。它基于以下几个关键概念：

  • 用户账户：由用户名和主机名组成，标识一个可以连接到MySQL服务器的用户
  • 权限：定义用户可以执行的操作，如SELECT、INSERT、UPDATE、DELETE等
  • 权限层级：MySQL支持全局级别、数据库级别、表级别、列级别和存储程序级别的权限控制
  • 权限表：MySQL使用一系列系统表来存储用户账户和权限信息

提示：MySQL 8.0引入了角色管理功能，允许管理员创建角色并为角色分配权限，然后将角色授予用户，大大简化了权限管理。

MySQL权限表

MySQL使用存储在mysql数据库中的一系列权限表来管理用户账户和权限。主要的权限表包括：

1. user表

user表存储用户账户信息和全局级别权限（适用于所有数据库）。

user表的主要列

列名 描述
Host 用户可以从哪个主机连接到MySQL服务器
User 用户名
Password 用户密码的哈希值（MySQL 5.7.6之前）
authentication_string 用户密码的哈希值（MySQL 5.7.6及以后）
ssl_type SSL连接类型
ssl_cipher SSL加密算法
x509_issuer X.509证书颁发者
x509_subject X.509证书主题
account_locked 账户是否被锁定
password_expired 密码是否过期
password_last_changed 密码最后修改时间
password_lifetime 密码有效期（天）
各类权限列（如Select_priv, Insert_priv等） 表示用户拥有的全局权限

查看user表内容

-- 查看当前所有用户账户
SELECT Host, User, authentication_string FROM mysql.user;

-- 查看特定用户的全局权限
SELECT * FROM mysql.user WHERE User = 'username' AND Host = 'host';

2. db表

db表存储数据库级别的权限，控制用户对特定数据库的访问权限。

db表的主要列

列名 描述
Host 主机名
Db 数据库名
User 用户名
各类权限列（如Select_priv, Insert_priv等） 表示用户对该数据库拥有的权限

查看db表内容

-- 查看所有数据库级别的权限
SELECT Host, Db, User FROM mysql.db;

-- 查看特定用户对特定数据库的权限
SELECT * FROM mysql.db WHERE User = 'username' AND Db = 'database_name';

3. tables_priv表

tables_priv表存储表级别的权限，控制用户对特定表的访问权限。

tables_priv表的主要列

列名 描述
Host 主机名
Db 数据库名
User 用户名
Table_name 表名
Grantor 授权者
Timestamp 授权时间戳
Table_priv 表级别权限（如Select, Insert, Update, Delete等）
Column_priv 列级别权限（通过tables_priv表也可以存储列权限）

查看tables_priv表内容

-- 查看所有表级别的权限
SELECT Host, Db, User, Table_name FROM mysql.tables_priv;

-- 查看特定用户对特定表的权限
SELECT * FROM mysql.tables_priv WHERE User = 'username' AND Table_name = 'table_name';

4. columns_priv表

columns_priv表存储列级别的权限，控制用户对特定表中特定列的访问权限。

columns_priv表的主要列

列名 描述
Host 主机名
Db 数据库名
User 用户名
Table_name 表名
Column_name 列名
Timestamp 授权时间戳
Column_priv 列级别权限（如Select, Insert, Update等）

查看columns_priv表内容

-- 查看所有列级别的权限
SELECT Host, Db, User, Table_name, Column_name FROM mysql.columns_priv;

-- 查看特定用户对特定列的权限
SELECT * FROM mysql.columns_priv WHERE User = 'username' AND Column_name = 'column_name';

5. procs_priv表

procs_priv表存储存储程序（存储过程和函数）的权限。

procs_priv表的主要列

列名 描述
Host 主机名
Db 数据库名
User 用户名
Routine_name 存储程序名
Routine_type 存储程序类型（PROCEDURE或FUNCTION）
Grantor 授权者
Proc_priv 存储程序权限（如Execute, Alter Routine等）
Timestamp 授权时间戳

查看procs_priv表内容

-- 查看所有存储程序的权限
SELECT Host, Db, User, Routine_name, Routine_type FROM mysql.procs_priv;

-- 查看特定用户对特定存储程序的权限
SELECT * FROM mysql.procs_priv WHERE User = 'username' AND Routine_name = 'procedure_name';

提示：在MySQL 8.0中，权限表的结构可能会有所变化，特别是在引入角色管理功能后。建议使用MySQL提供的权限管理语句（如GRANT、REVOKE等）来管理权限，而不是直接修改权限表。

账户管理

MySQL账户管理包括创建用户、删除用户、修改用户密码等操作。下面详细介绍这些操作的方法。

1. 登录和退出MySQL服务器

使用命令行登录MySQL服务器

-- 基本登录语法
mysql -u username -p [database_name]

-- 示例：以root用户登录MySQL服务器
mysql -u root -p

-- 示例：以特定用户登录指定数据库
mysql -u username -p database_name

-- 示例：指定主机和端口登录
mysql -h host_name -P port_number -u username -p

退出MySQL服务器

-- 方法1：使用EXIT命令
exit;

-- 方法2：使用QUIT命令
quit;

-- 方法3：使用\q命令
\q;

2. 新建普通用户

使用CREATE USER语句创建用户

-- 基本语法
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例1：创建一个只能从本地主机连接的用户
CREATE USER 'john'@'localhost' IDENTIFIED BY 'john_password';

-- 示例2：创建一个可以从任何主机连接的用户
CREATE USER 'mary'@'%' IDENTIFIED BY 'mary_password';

-- 示例3：创建一个只能从特定IP地址连接的用户
CREATE USER 'bob'@'192.168.1.100' IDENTIFIED BY 'bob_password';

-- 示例4：创建一个使用SSL连接的用户
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'alice_password'
REQUIRE SSL;

-- 示例5：创建一个使用X.509证书的用户
CREATE USER 'david'@'localhost' IDENTIFIED BY 'david_password'
REQUIRE SUBJECT '/C=US/ST=California/L=San Francisco/O=MySQL AB/CN=David';

-- 示例6：创建一个具有密码过期策略的用户（MySQL 5.7.4及以后）
CREATE USER 'sarah'@'localhost' IDENTIFIED BY 'sarah_password'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 示例7：创建一个初始密码即过期的用户
CREATE USER 'tom'@'localhost' IDENTIFIED BY 'tom_password'
PASSWORD EXPIRE;

3. 删除普通用户

使用DROP USER语句删除用户

-- 基本语法
DROP USER 'username'@'host';

-- 示例1：删除特定用户
DROP USER 'john'@'localhost';

-- 示例2：同时删除多个用户
DROP USER 'mary'@'%', 'bob'@'192.168.1.100';

注意：删除用户时，用户的所有权限也会被自动撤销，但不会删除用户创建的数据库对象。

4. 修改用户密码

root用户修改自己的密码

-- 方法1：使用ALTER USER语句（MySQL 5.7.6及以后）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 方法2：使用SET PASSWORD语句
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password'); -- MySQL 5.7.5及以前
SET PASSWORD FOR 'root'@'localhost' = 'new_password'; -- MySQL 5.7.6及以后

-- 方法3：使用mysqladmin命令行工具
mysqladmin -u root -p'old_password' password 'new_password';

root用户修改普通用户的密码

-- 方法1：使用ALTER USER语句
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 示例：修改john用户的密码
ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_john_password';

-- 方法2：使用SET PASSWORD语句
SET PASSWORD FOR 'username'@'host' = 'new_password'; -- MySQL 5.7.6及以后
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password'); -- MySQL 5.7.5及以前

-- 示例：修改mary用户的密码
SET PASSWORD FOR 'mary'@'%' = 'new_mary_password';

普通用户修改自己的密码

-- 方法1：使用ALTER USER语句
ALTER USER CURRENT_USER() IDENTIFIED BY 'new_password';

-- 方法2：使用SET PASSWORD语句，不需要指定用户名
SET PASSWORD = 'new_password'; -- MySQL 5.7.6及以后
SET PASSWORD = PASSWORD('new_password'); -- MySQL 5.7.5及以前

提示：在MySQL 8.0中，密码管理功能得到了增强，包括密码过期策略、密码历史记录和密码复杂度要求等。建议使用强密码并定期更换。

权限管理

MySQL权限管理包括授予权限、收回权限和查看权限等操作。下面详细介绍这些操作的方法。

1. MySQL的各种权限

MySQL支持多种类型的权限，按权限级别分类如下：

全局级别权限

全局级别权限适用于所有数据库，存储在mysql.user表中。

  • ALL PRIVILEGES：授予所有权限（除了GRANT OPTION）
  • ALTER：允许修改表结构
  • ALTER ROUTINE：允许修改或删除存储过程和函数
  • CREATE：允许创建数据库和表
  • CREATE ROUTINE：允许创建存储过程和函数
  • CREATE TABLESPACE：允许创建表空间
  • CREATE TEMPORARY TABLES：允许创建临时表
  • CREATE USER：允许创建、修改和删除用户
  • CREATE VIEW：允许创建视图
  • DELETE：允许删除表中的数据
  • DROP：允许删除数据库、表和视图
  • EVENT：允许使用事件调度器
  • EXECUTE：允许执行存储过程和函数
  • FILE：允许在服务器主机上读写文件
  • GRANT OPTION：允许授予或收回权限
  • INDEX：允许创建和删除索引
  • INSERT：允许向表中插入数据
  • LOCK TABLES：允许锁定表
  • PROCESS：允许查看服务器进程信息
  • PROXY：允许代理另一个用户
  • REFERENCES：允许创建外键约束
  • RELOAD：允许重新加载权限表
  • REPLICATION CLIENT：允许查询复制服务器状态
  • REPLICATION SLAVE：允许复制从服务器连接到主服务器
  • SELECT：允许从表中查询数据
  • SHOW DATABASES：允许查看所有数据库名
  • SHOW VIEW：允许查看视图的定义
  • SHUTDOWN：允许关闭MySQL服务器
  • SUPER：允许执行超级用户操作
  • TRIGGER：允许使用触发器
  • UPDATE：允许更新表中的数据
  • USAGE：无权限（创建用户的默认权限）

数据库级别权限

数据库级别权限适用于特定的数据库，存储在mysql.db表中。

  • ALL PRIVILEGES：授予指定数据库的所有权限
  • ALTER：允许修改指定数据库中的表结构
  • ALTER ROUTINE：允许修改或删除指定数据库中的存储过程和函数
  • CREATE：允许在指定数据库中创建表
  • CREATE ROUTINE：允许在指定数据库中创建存储过程和函数
  • CREATE TEMPORARY TABLES：允许在指定数据库中创建临时表
  • CREATE VIEW：允许在指定数据库中创建视图
  • DELETE：允许删除指定数据库中的表数据
  • DROP：允许删除指定数据库中的表和视图
  • EVENT：允许在指定数据库中使用事件调度器
  • EXECUTE：允许执行指定数据库中的存储过程和函数
  • GRANT OPTION：允许授予或收回指定数据库的权限
  • INDEX：允许在指定数据库的表中创建和删除索引
  • INSERT：允许向指定数据库中的表插入数据
  • LOCK TABLES：允许锁定指定数据库中的表
  • REFERENCES：允许在指定数据库中创建外键约束
  • SELECT：允许从指定数据库中的表查询数据
  • SHOW VIEW：允许查看指定数据库中的视图定义
  • TRIGGER：允许使用指定数据库中的触发器
  • UPDATE：允许更新指定数据库中的表数据

表级别权限

表级别权限适用于特定的表，存储在mysql.tables_priv表中。

  • ALL PRIVILEGES：授予指定表的所有权限
  • ALTER：允许修改指定表的结构
  • CREATE VIEW：允许基于指定表创建视图
  • DELETE：允许删除指定表中的数据
  • DROP：允许删除指定表
  • GRANT OPTION：允许授予或收回指定表的权限
  • INDEX：允许在指定表中创建和删除索引
  • INSERT：允许向指定表插入数据
  • LOCK TABLES：允许锁定指定表
  • REFERENCES：允许在指定表上创建外键约束
  • SELECT：允许从指定表查询数据
  • SHOW VIEW：允许查看基于指定表的视图定义
  • TRIGGER：允许使用指定表的触发器
  • UPDATE：允许更新指定表中的数据

列级别权限

列级别权限适用于特定表的特定列，存储在mysql.columns_priv表中。

  • GRANT OPTION：允许授予或收回指定列的权限
  • INSERT：允许向指定列插入数据
  • SELECT：允许从指定列查询数据
  • UPDATE：允许更新指定列的数据

存储程序级别权限

存储程序级别权限适用于特定的存储过程和函数，存储在mysql.procs_priv表中。

  • ALTER ROUTINE：允许修改或删除指定的存储过程或函数
  • EXECUTE：允许执行指定的存储过程或函数
  • GRANT OPTION：允许授予或收回指定存储程序的权限

2. 授权

授权是指授予用户执行特定操作的权限。在MySQL中，使用GRANT语句来授权。

GRANT语句的基本语法

GRANT privileges ON database.table TO 'username'@'host' [WITH GRANT OPTION];

全局级别授权

-- 授予用户所有全局权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' WITH GRANT OPTION;

-- 授予用户特定的全局权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'host';

数据库级别授权

-- 授予用户指定数据库的所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

-- 示例：授予john用户对hr数据库的所有权限
GRANT ALL PRIVILEGES ON hr.* TO 'john'@'localhost';

-- 授予用户指定数据库的特定权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'host';

-- 示例：授予mary用户对sales数据库的SELECT和INSERT权限
GRANT SELECT, INSERT ON sales.* TO 'mary'@'%';

表级别授权

-- 授予用户指定表的所有权限
GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';

-- 示例：授予bob用户对hr.employees表的所有权限
GRANT ALL PRIVILEGES ON hr.employees TO 'bob'@'192.168.1.100';

-- 授予用户指定表的特定权限
GRANT SELECT, UPDATE ON database_name.table_name TO 'username'@'host';

-- 示例：授予alice用户对sales.orders表的SELECT和UPDATE权限
GRANT SELECT, UPDATE ON sales.orders TO 'alice'@'localhost';

列级别授权

-- 授予用户指定表中特定列的权限
GRANT SELECT (column1, column2), UPDATE (column3) ON database_name.table_name TO 'username'@'host';

-- 示例：授予david用户对hr.employees表中特定列的权限
GRANT SELECT (employee_id, first_name, last_name), UPDATE (salary) ON hr.employees TO 'david'@'localhost';

存储程序级别授权

-- 授予用户执行存储过程的权限
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'username'@'host';

-- 示例：授予sarah用户执行hr.calculate_salary存储过程的权限
GRANT EXECUTE ON PROCEDURE hr.calculate_salary TO 'sarah'@'localhost';

-- 授予用户执行存储函数的权限
GRANT EXECUTE ON FUNCTION database_name.function_name TO 'username'@'host';

-- 示例：授予tom用户执行sales.calculate_discount存储函数的权限
GRANT EXECUTE ON FUNCTION sales.calculate_discount TO 'tom'@'localhost';

使用WITH GRANT OPTION子句

-- 授予用户权限，并允许用户将这些权限授予其他用户
GRANT SELECT, INSERT ON database_name.* TO 'username'@'host' WITH GRANT OPTION;

-- 示例：授予john用户对hr数据库的所有权限，并允许他授予这些权限给其他用户
GRANT ALL PRIVILEGES ON hr.* TO 'john'@'localhost' WITH GRANT OPTION;

注意：使用WITH GRANT OPTION子句时应谨慎，因为它允许被授权用户将权限传递给其他用户，可能导致权限扩散和安全风险。

3. 收回权限

收回权限是指撤销用户已有的权限。在MySQL中，使用REVOKE语句来收回权限。

REVOKE语句的基本语法

REVOKE privileges ON database.table FROM 'username'@'host';

收回全局级别权限

-- 收回用户的所有全局权限
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';

-- 收回用户的特定全局权限
REVOKE SELECT, INSERT ON *.* FROM 'username'@'host';

收回数据库级别权限

-- 收回用户在指定数据库的所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';

-- 示例：收回john用户对hr数据库的所有权限
REVOKE ALL PRIVILEGES ON hr.* FROM 'john'@'localhost';

-- 收回用户在指定数据库的特定权限
REVOKE SELECT, INSERT ON database_name.* FROM 'username'@'host';

-- 示例：收回mary用户对sales数据库的SELECT和INSERT权限
REVOKE SELECT, INSERT ON sales.* FROM 'mary'@'%';

收回表级别权限

-- 收回用户在指定表的所有权限
REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';

-- 示例：收回bob用户对hr.employees表的所有权限
REVOKE ALL PRIVILEGES ON hr.employees FROM 'bob'@'192.168.1.100';

-- 收回用户在指定表的特定权限
REVOKE SELECT, UPDATE ON database_name.table_name FROM 'username'@'host';

-- 示例：收回alice用户对sales.orders表的SELECT和UPDATE权限
REVOKE SELECT, UPDATE ON sales.orders FROM 'alice'@'localhost';

收回列级别权限

-- 收回用户在指定表中特定列的权限
REVOKE SELECT (column1, column2), UPDATE (column3) ON database_name.table_name FROM 'username'@'host';

-- 示例：收回david用户对hr.employees表中特定列的权限
REVOKE SELECT (employee_id, first_name, last_name), UPDATE (salary) ON hr.employees FROM 'david'@'localhost';

收回存储程序级别权限

-- 收回用户执行存储过程的权限
REVOKE EXECUTE ON PROCEDURE database_name.procedure_name FROM 'username'@'host';

-- 示例：收回sarah用户执行hr.calculate_salary存储过程的权限
REVOKE EXECUTE ON PROCEDURE hr.calculate_salary FROM 'sarah'@'localhost';

-- 收回用户执行存储函数的权限
REVOKE EXECUTE ON FUNCTION database_name.function_name FROM 'username'@'host';

-- 示例：收回tom用户执行sales.calculate_discount存储函数的权限
REVOKE EXECUTE ON FUNCTION sales.calculate_discount FROM 'tom'@'localhost';

收回GRANT OPTION权限

-- 收回用户的GRANT OPTION权限
REVOKE GRANT OPTION ON *.* FROM 'username'@'host';

-- 示例：收回john用户的GRANT OPTION权限
REVOKE GRANT OPTION ON *.* FROM 'john'@'localhost';

4. 查看权限

在MySQL中，可以使用多种方法查看用户的权限。

使用SHOW GRANTS语句查看权限

-- 查看当前用户的权限
SHOW GRANTS;

-- 查看指定用户的权限
SHOW GRANTS FOR 'username'@'host';

-- 示例：查看john用户的权限
SHOW GRANTS FOR 'john'@'localhost';

-- 示例：查看mary用户的权限
SHOW GRANTS FOR 'mary'@'%';

通过查询权限表查看权限

-- 查看全局级别权限
SELECT * FROM mysql.user WHERE User = 'username' AND Host = 'host';

-- 查看数据库级别权限
SELECT * FROM mysql.db WHERE User = 'username' AND Db = 'database_name';

-- 查看表级别权限
SELECT * FROM mysql.tables_priv WHERE User = 'username' AND Table_name = 'table_name';

-- 查看列级别权限
SELECT * FROM mysql.columns_priv WHERE User = 'username' AND Column_name = 'column_name';

-- 查看存储程序级别权限
SELECT * FROM mysql.procs_priv WHERE User = 'username' AND Routine_name = 'routine_name';

访问控制

MySQL的访问控制包括两个主要阶段：连接核实阶段和请求核实阶段。

1. 连接核实阶段

当用户尝试连接到MySQL服务器时，MySQL会执行连接核实阶段的检查：

  1. 用户账户验证：检查用户是否存在，以及提供的密码是否正确
  2. 主机验证：检查用户是否被允许从指定的主机连接
  3. 连接限制检查：检查是否超过了最大连接数限制
  4. SSL/TLS验证：如果配置了SSL/TLS连接，则进行相应的验证

如果连接核实阶段失败，MySQL会拒绝用户连接，并返回相应的错误消息。

2. 请求核实阶段

连接成功后，当用户执行SQL语句时，MySQL会执行请求核实阶段的检查：

  1. 权限检查：检查用户是否拥有执行请求操作的权限
  2. 对象访问检查：检查用户是否有权访问请求中涉及的数据库对象
  3. 视图和存储程序检查：如果请求涉及视图或存储程序，则进行相应的权限检查

如果请求核实阶段失败，MySQL会拒绝执行请求，并返回相应的错误消息。

提示：MySQL的访问控制是基于权限表的，当用户权限发生变化时，需要执行FLUSH PRIVILEGES语句刷新权限表，使更改生效。

提升安全性

为了保护MySQL数据库的安全，应该采取一系列安全措施。下面介绍几种提升MySQL安全性的方法。

1. AES 256加密

MySQL支持AES（Advanced Encryption Standard）加密算法，可以用于保护敏感数据。

使用AES_ENCRYPT和AES_DECRYPT函数

-- 创建加密密钥
SET @encryption_key = 'MySuperSecretKey123!';

-- 插入加密数据
INSERT INTO sensitive_data (user_id, encrypted_info)
VALUES (1, AES_ENCRYPT('Sensitive information', @encryption_key));

-- 查询并解密数据
SELECT user_id, AES_DECRYPT(encrypted_info, @encryption_key) AS decrypted_info
FROM sensitive_data;

-- 示例：创建存储敏感信息的表
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    encrypted_info VARBINARY(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 示例：使用AES 256加密模式（MySQL 5.7.4及以后）
INSERT INTO sensitive_data (user_id, encrypted_info)
VALUES (2, AES_ENCRYPT('More sensitive information', @encryption_key, 'aes-256-cbc'));

-- 示例：使用随机初始化向量（IV）增强安全性
SET @iv = RANDOM_BYTES(16);
INSERT INTO sensitive_data (user_id, encrypted_info)
VALUES (3, AES_ENCRYPT('Very sensitive information', @encryption_key, @iv));

注意：加密密钥的安全管理非常重要。不要将密钥硬编码在应用程序中或存储在不安全的地方。考虑使用密钥管理系统来安全地存储和管理加密密钥。

2. 密码到期更换策略

MySQL 5.7.4及以后版本支持密码到期策略，可以强制用户定期更换密码。

设置全局密码过期策略

-- 在my.cnf或my.ini配置文件中设置
[mysqld]
default_password_lifetime=90

-- 或者在运行时设置全局系统变量
SET GLOBAL default_password_lifetime = 90;

-- 设置密码永不过期
SET GLOBAL default_password_lifetime = 0;

为特定用户设置密码过期策略

-- 创建用户时设置密码过期策略
CREATE USER 'username'@'host' IDENTIFIED BY 'password'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 修改现有用户的密码过期策略
ALTER USER 'username'@'host'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 设置用户密码永不过期
ALTER USER 'username'@'host'
PASSWORD EXPIRE NEVER;

-- 立即过期用户密码（强制用户下次登录时修改密码）
ALTER USER 'username'@'host'
PASSWORD EXPIRE;

检查用户密码过期状态

-- 查看用户密码过期信息
SELECT User, Host, password_expired, password_lifetime, password_last_changed
FROM mysql.user;

3. 安全模式安装

MySQL提供了安全模式安装功能，可以在安装过程中设置root密码、删除匿名用户、禁止root远程登录等。

使用mysql_secure_installation脚本

-- 在命令行中运行安全安装脚本
shell> mysql_secure_installation

-- 脚本会引导你完成以下操作：
-- 1. 设置root用户密码
-- 2. 删除匿名用户
-- 3. 禁止root用户远程登录
-- 4. 删除test数据库
-- 5. 重新加载权限表

-- 在Windows系统中，可以使用以下命令
C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql_secure_installation.exe"

手动执行安全配置

-- 连接到MySQL服务器
mysql -u root -p

-- 设置root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';

-- 删除匿名用户
DROP USER ''@'localhost';
DROP USER ''@'hostname';

-- 禁止root用户远程登录
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- 删除test数据库
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';

-- 刷新权限表
FLUSH PRIVILEGES;

4. 其他安全最佳实践

  • 使用强密码：确保所有用户都使用强密码，包含大小写字母、数字和特殊字符
  • 最小权限原则：只为用户授予完成其工作所需的最低权限
  • 定期审查权限：定期审查和清理用户权限，撤销不再需要的权限
  • 限制远程访问：尽量限制root用户的远程访问，只允许从可信的IP地址连接
  • 使用SSL/TLS加密连接：配置MySQL服务器和客户端使用SSL/TLS加密连接
  • 启用审计日志：启用审计日志记录用户活动，以便监控和排查安全问题
  • 定期备份：定期备份数据库，以便在发生安全事件时能够恢复数据
  • 及时更新：及时安装MySQL的安全更新和补丁
  • 禁用不必要的功能：禁用不必要的存储引擎、插件和功能
  • 使用防火墙：配置防火墙限制对MySQL服务器的访问

MySQL 8.0的新特性——管理角色

MySQL 8.0引入了角色管理功能，允许管理员创建角色并为角色分配权限，然后将角色授予用户，大大简化了权限管理。

1. 创建和删除角色

-- 创建角色
CREATE ROLE 'role_name';

-- 示例：创建只读角色
CREATE ROLE 'readonly'@'localhost';

-- 示例：创建管理员角色
CREATE ROLE 'admin'@'localhost';

-- 删除角色
DROP ROLE 'role_name';

-- 示例：删除只读角色
DROP ROLE 'readonly'@'localhost';

2. 为角色分配权限

-- 为角色分配权限
GRANT privileges ON database.table TO 'role_name';

-- 示例：为只读角色分配SELECT权限
GRANT SELECT ON *.* TO 'readonly'@'localhost';

-- 示例：为管理员角色分配所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

3. 将角色授予用户

-- 将角色授予用户
GRANT 'role_name' TO 'username'@'host';

-- 示例：将只读角色授予john用户
GRANT 'readonly'@'localhost' TO 'john'@'localhost';

-- 示例：将多个角色授予mary用户
GRANT 'readonly'@'localhost', 'admin'@'localhost' TO 'mary'@'%';

4. 设置默认角色

-- 设置用户的默认角色
SET DEFAULT ROLE 'role_name' FOR 'username'@'host';

-- 示例：设置john用户的默认角色为只读角色
SET DEFAULT ROLE 'readonly'@'localhost' FOR 'john'@'localhost';

-- 示例：设置mary用户的默认角色为多个角色
SET DEFAULT ROLE 'readonly'@'localhost', 'admin'@'localhost' FOR 'mary'@'%';

5. 激活和停用角色

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

-- 激活特定角色
SET ROLE 'role_name';

-- 示例：激活只读角色
SET ROLE 'readonly'@'localhost';

-- 激活所有授予的角色
SET ROLE ALL;

-- 停用所有角色
SET ROLE NONE;

6. 查看角色信息

-- 查看所有角色
SELECT * FROM mysql.user WHERE account_type = 'role';

-- 查看角色的权限
SHOW GRANTS FOR 'role_name';

-- 示例：查看只读角色的权限
SHOW GRANTS FOR 'readonly'@'localhost';

-- 查看哪些用户拥有特定角色
SELECT FROM_USER, TO_USER FROM mysql.role_edges WHERE TO_USER = 'role_name';

提示：角色管理功能是MySQL 8.0的重要新特性，它可以大大简化权限管理，特别是在有多个用户需要相同权限的情况下。使用角色可以减少权限管理的复杂度和错误率。