MySQL 用户管理与权限设置完全指南

欢迎来到8455线路检测中心技术小课堂，每天分享一个技术小知识。

在 MySQL 数据库管理中，用户管理和权限控制是确保数据安全的核心环节。合理的权限分配能够最小化安全风险，同时保证不同用户和应用的正常访问。本文将详细介绍 MySQL 用户的创建、修改、删除以及权限管理的各个方面。

1. 连接到 MySQL

在开始用户管理之前，首先需要以 root 用户或其他具有管理权限的用户连接到 MySQL：

# 使用命令行客户端连接

mysql -u root -p

# 指定主机连接

mysql -h localhost -u root -p

# 使用 socket 连接

mysql -S /var/run/mysqld/mysqld.sock -u root -p

进入 MySQL 命令行后，可以执行以下命令检查当前连接信息：

-- 查看当前用户

SELECT CURRENT_USER();

-- 查看服务器版本

SELECT VERSION();

-- 查看所有数据库

SHOW DATABASES;

2. 创建新用户

基本用户创建

-- 创建新用户（MySQL 8.0+ 推荐方式）

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- 创建用户并指定密码过期策略

CREATE USER 'tempuser'@'localhost'

IDENTIFIED BY 'TempPass123!'

PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建用户并锁定账户

CREATE USER 'lockeduser'@'localhost'

IDENTIFIED BY 'LockedPass123!'

ACCOUNT LOCK;

指定身份验证插件

-- 使用 caching_sha2_password（MySQL 8.0 默认）

CREATE USER 'user1'@'localhost'

IDENTIFIED WITH caching_sha2_password

BY 'Password123!';

-- 使用 mysql_native_password（兼容旧应用）

CREATE USER 'user2'@'localhost'

IDENTIFIED WITH mysql_native_password

BY 'Password123!';

-- 使用 sha256_password

CREATE USER 'user3'@'localhost'

IDENTIFIED WITH sha256_password

BY 'Password123!';

创建远程访问用户

-- 允许从任何主机访问（生产环境不推荐）

CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'RemotePass123!';

-- 允许从特定IP段访问

CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'NetworkPass123!';

-- 允许从特定主机名访问

CREATE USER 'user'@'client.example.com' IDENTIFIED BY 'HostPass123!';

3. 用户权限管理

查看用户权限

-- 查看特定用户的权限

SHOW GRANTS FOR 'username'@'localhost';

-- 查看当前用户的权限

SHOW GRANTS;

-- 查看所有用户及其权限

SELECT

    user,

    host,

    authentication_string

FROM mysql.user

ORDER BY user, host;

授予权限

-- 授予数据库所有权限

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- 授予特定权限

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

-- 授予存储过程权限

GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'username'@'localhost';

-- 授予表级别权限

GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'localhost';

-- 授予列级别权限

GRANT SELECT (column1, column2), UPDATE (column1)

ON database_name.table_name

TO 'username'@'localhost';

常用权限列表

-- 数据操作权限

GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'user'@'host';

-- 结构操作权限

GRANT CREATE, ALTER, DROP, INDEX ON db.* TO 'user'@'host';

-- 管理权限

GRANT CREATE USER, PROCESS, RELOAD, SHOW DATABASES ON *.* TO 'user'@'host';

-- 所有权限（谨慎使用）

GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' WITH GRANT OPTION;

权限层级说明

-- 全局权限（所有数据库）

GRANT SELECT ON *.* TO 'user'@'localhost';

-- 数据库级别权限

GRANT SELECT ON database_name.* TO 'user'@'localhost';

-- 表级别权限

GRANT SELECT ON database_name.table_name TO 'user'@'localhost';

-- 列级别权限

GRANT SELECT (id, name) ON database_name.table_name TO 'user'@'localhost';

-- 存储过程权限

GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'user'@'localhost';

撤销权限

-- 撤销特定权限

REVOKE INSERT, UPDATE ON database_name.* FROM 'username'@'localhost';

-- 撤销所有权限

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

-- 撤销全局权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';

-- 刷新权限

FLUSH PRIVILEGES;

4. 修改用户属性

修改密码

-- MySQL 8.0+ 修改密码

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewStrongPassword123!';

-- MySQL 5.7 修改密码

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('NewPassword123!');

-- 修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'NewPassword123!';

-- 使用随机密码

CREATE USER 'tempuser'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

-- 查看生成的随机密码

SHOW CREATE USER 'tempuser'@'localhost';

修改认证插件

-- 更改认证插件

ALTER USER 'username'@'localhost'

IDENTIFIED WITH caching_sha2_password

BY 'NewPassword123!';

账户锁定与解锁

-- 锁定账户

ALTER USER 'username'@'localhost' ACCOUNT LOCK;

-- 解锁账户

ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;

-- 查看账户锁定状态

SELECT user, host, account_locked

FROM mysql.user

WHERE user = 'username';

修改资源限制

-- 设置查询限制

ALTER USER 'username'@'localhost'

WITH

    MAX_QUERIES_PER_HOUR 1000

    MAX_UPDATES_PER_HOUR 100

    MAX_CONNECTIONS_PER_HOUR 50

    MAX_USER_CONNECTIONS 10;

5. 删除用户

-- 删除用户

DROP USER 'username'@'localhost';

-- 同时删除多个用户

DROP USER 'user1'@'localhost', 'user2'@'192.168.1.%';

-- 强制删除（即使有活动会话）

DROP USER IF EXISTS 'username'@'localhost';

-- 安全删除用户前检查

SELECT * FROM mysql.user WHERE user = 'username';

SHOW PROCESSLIST;  -- 检查是否有该用户的活动连接

8455线路检测中心官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，8455线路检测中心整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，8455线路检测中心期待与你一起探索。