MySQL 用户权限管理完全指南
目录
- MySQL 用户权限管理完全指南
1. 用户管理基础
1.1 创建新用户
基本语法：
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
参数说明：
username: 用户名host: 允许连接的主机，% 表示任意主机password: 用户密码
示例：
-- 创建本地用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
-- 创建允许远程连接的用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'AnotherStrongPass456!';
-- 创建只允许特定IP连接的用户
CREATE USER 'report_user'@'192.168.1.100' IDENTIFIED BY 'ReportPass789!';
-- 使用密码过期策略
CREATE USER 'temp_user'@'%'
IDENTIFIED BY 'TempPass123!'
PASSWORD EXPIRE INTERVAL 30 DAY;
-- 使用加密密码
CREATE USER 'secure_user'@'localhost'
IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF';
1.2 删除用户
语法：
DROP USER 'username'@'host';
示例：
-- 删除单个用户
DROP USER 'old_user'@'localhost';
-- 删除多个用户
DROP USER 'user1'@'%', 'user2'@'192.168.1.%';
1.3 修改用户密码
MySQL 5.7及以上版本：
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
MySQL 5.6及以下版本：
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');
示例：
-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewStrongPass789!';
-- 设置密码永不过期
ALTER USER 'admin_user'@'localhost'
IDENTIFIED BY 'AdminPass123!'
PASSWORD EXPIRE NEVER;
-- 设置密码立即过期（下次登录需修改）
ALTER USER 'temp_user'@'%' PASSWORD EXPIRE;
1.4 重命名用户
语法：
RENAME USER 'old_username'@'old_host' TO 'new_username'@'new_host';
示例：
-- 重命名用户
RENAME USER 'test_user'@'localhost' TO 'dev_user'@'localhost';
-- 同时修改用户名和主机
RENAME USER 'app_user'@'%' TO 'app_user'@'192.168.1.%';
2. 权限管理详解
2.1 权限类型
数据操作权限：
SELECT: 查询数据INSERT: 插入数据UPDATE: 更新数据DELETE: 删除数据EXECUTE: 执行存储过程/函数
结构操作权限：
CREATE: 创建数据库/表/索引等DROP: 删除数据库/表/索引等ALTER: 修改表结构INDEX: 创建/删除索引CREATE VIEW: 创建视图SHOW VIEW: 查看视图定义
管理权限：
CREATE USER: 创建用户DROP USER: 删除用户RELOAD: 重载权限SHUTDOWN: 关闭服务器SUPER: 超级权限GRANT OPTION: 授予权限给其他用户
2.2 权限级别
- 全局级别 - 影响所有数据库
- 数据库级别 - 影响特定数据库
- 表级别 - 影响特定表
- 列级别 - 影响表中的特定列
- 子程序级别 - 影响存储过程和函数
3. 权限授予操作
3.1 授予数据库级别权限
语法：
GRANT privileges ON database_name.* TO 'username'@'host';
示例：
-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON my_database.* TO 'app_user'@'localhost';
-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON sales_db.* TO 'sales_user'@'%';
-- 授予创建视图权限
GRANT CREATE VIEW, SHOW VIEW ON report_db.* TO 'report_user'@'192.168.1.%';
-- 授予权限管理权限
GRANT SELECT, INSERT ON app_db.* TO 'dev_user'@'localhost' WITH GRANT OPTION;
3.2 授予表级别权限
语法：
GRANT privileges ON database_name.table_name TO 'username'@'host';
示例：
-- 授予表的所有权限
GRANT ALL PRIVILEGES ON customers_db.users TO 'admin_user'@'localhost';
-- 授予特定表的查询权限
GRANT SELECT ON sales_db.orders TO 'readonly_user'@'%';
-- 授予多个表的权限
GRANT SELECT, INSERT ON inventory_db.products, inventory_db.stock TO 'inventory_user'@'192.168.1.%';
3.3 授予列级别权限
语法：
GRANT privileges (column1, column2, ...) ON database_name.table_name TO 'username'@'host';
示例：
-- 授予特定列的更新权限
GRANT UPDATE (email, phone) ON customers_db.users TO 'support_user'@'localhost';
-- 授予多列的查询权限
GRANT SELECT (id, name, email) ON hr_db.employees TO 'hr_viewer'@'%';
-- 授予插入特定列的权限
GRANT INSERT (name, email, department) ON hr_db.employees TO 'hr_assistant'@'192.168.1.%';
3.4 授予存储过程/函数权限
语法：
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'username'@'host';
GRANT EXECUTE ON FUNCTION database_name.function_name TO 'username'@'host';
示例：
-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE sales_db.calculate_revenue TO 'report_user'@'%';
-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION utils.format_date TO 'app_user'@'localhost';
-- 授予所有存储过程和函数的执行权限
GRANT EXECUTE ON database_name.* TO 'app_user'@'%';
3.5 授予所有权限
全局权限：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
注意： 全局权限应谨慎授予，通常只给数据库管理员使用。
4. 权限撤销操作
4.1 撤销数据库级别权限
语法：
REVOKE privileges ON database_name.* FROM 'username'@'host';
示例：
-- 撤销删除权限
REVOKE DELETE ON app_db.* FROM 'app_user'@'localhost';
-- 撤销多个权限
REVOKE INSERT, UPDATE ON sales_db.* FROM 'readonly_user'@'%';
4.2 撤销表级别权限
语法：
REVOKE privileges ON database_name.table_name FROM 'username'@'host';
示例：
-- 撤销表的更新权限
REVOKE UPDATE ON customers_db.users FROM 'support_user'@'localhost';
-- 撤销多个表的权限
REVOKE SELECT ON sales_db.orders, sales_db.order_items FROM 'temp_user'@'%';
4.3 撤销所有权限
撤销特定数据库的所有权限：
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
撤销所有数据库的所有权限：
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'host';
示例：
-- 撤销用户在特定数据库的所有权限
REVOKE ALL PRIVILEGES ON test_db.* FROM 'test_user'@'%';
-- 撤销用户的权限授予权限
REVOKE GRANT OPTION ON *.* FROM 'power_user'@'localhost';
5. 权限查看
5.1 查看用户列表
-- MySQL 5.7及以上
SELECT user, host FROM mysql.user;
-- 查看所有用户信息
SELECT user, host, authentication_string, plugin FROM mysql.user;
-- 查看活跃用户
SELECT user, host, max_connections, max_user_connections
FROM mysql.user WHERE user <> '';
5.2 查看用户权限
语法：
SHOW GRANTS FOR 'username'@'host';
示例：
-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';
-- 查看所有权限详情
SHOW GRANTS FOR 'root'@'localhost';
5.3 查看当前用户权限
SHOW GRANTS;
5.4 查看权限表结构
-- 查看用户表
DESCRIBE mysql.user;
-- 查看权限表
DESCRIBE mysql.db;
DESCRIBE mysql.tables_priv;
DESCRIBE mysql.columns_priv;
DESCRIBE mysql.procs_priv;
6. 权限刷新与生效
6.1 刷新权限
当修改权限后，需要刷新权限表使更改生效：
FLUSH PRIVILEGES;
6.2 权限生效时间
- 新权限：立即生效（除了已连接的会话）
- 撤销权限：对新会话立即生效，对现有会话在下次操作时生效
- 密码修改：对新连接立即生效，现有连接不受影响
7. 常见权限组合示例
7.1 只读用户
-- 创建只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadonlyPass123!';
-- 授予查询权限
GRANT SELECT ON database_name.* TO 'readonly'@'%';
-- 授予特定表的只读权限
GRANT SELECT ON database_name.important_table TO 'readonly'@'%';
7.2 数据维护用户
-- 创建数据维护用户
CREATE USER 'data_maintain'@'localhost' IDENTIFIED BY 'MaintainPass456!';
-- 授予增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'data_maintain'@'localhost';
-- 不授予结构修改权限
7.3 开发测试用户
-- 创建开发用户
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'DevPass789!';
-- 授予开发所需权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX
ON test_db.* TO 'dev_user'@'%';
-- 授予存储过程权限
GRANT EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON test_db.* TO 'dev_user'@'%';
7.4 管理员用户
-- 创建管理员用户
CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'AdminPass123!';
-- 授予数据库管理权限
GRANT ALL PRIVILEGES ON *.* TO 'db_admin'@'localhost' WITH GRANT OPTION;
-- 移除危险权限
REVOKE SHUTDOWN, FILE, SUPER ON *.* FROM 'db_admin'@'localhost';
8. 安全最佳实践
8.1 最小权限原则
- 只授予用户完成工作所需的最小权限
- 定期审查和回收不必要的权限
- 使用角色或权限组管理权限
8.2 密码策略
-- 设置密码验证插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
-- 查看密码策略配置
SHOW VARIABLES LIKE 'validate_password%';
-- 设置密码策略
SET GLOBAL validate_password_policy = 'STRONG';
SET GLOBAL validate_password_length = 12;
SET GLOBAL validate_password_number_count = 2;
SET GLOBAL validate_password_special_char_count = 2;
8.3 远程访问控制
-- 限制远程访问IP
CREATE USER 'remote_admin'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';
-- 禁止root远程登录
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
-- 使用防火墙限制MySQL端口访问
-- iptables -A INPUT -p tcp -s 192.168.1.0/24 --dport 3306 -j ACCEPT
-- iptables -A INPUT -p tcp --dport 3306 -j DROP
8.4 定期权限审计
-- 审计用户权限脚本
SELECT user, host,
IF(Select_priv='Y','SELECT,','') AS Select_priv,
IF(Insert_priv='Y','INSERT,','') AS Insert_priv,
IF(Update_priv='Y','UPDATE,','') AS Update_priv,
IF(Delete_priv='Y','DELETE,','') AS Delete_priv,
IF(Create_priv='Y','CREATE,','') AS Create_priv,
IF(Drop_priv='Y','DROP,','') AS Drop_priv,
IF(Reload_priv='Y','RELOAD,','') AS Reload_priv,
IF(Shutdown_priv='Y','SHUTDOWN,','') AS Shutdown_priv,
IF(Process_priv='Y','PROCESS,','') AS Process_priv,
IF(File_priv='Y','FILE,','') AS File_priv,
IF(Grant_priv='Y','GRANT OPTION,','') AS Grant_priv,
IF(References_priv='Y','REFERENCES,','') AS References_priv,
IF(Index_priv='Y','INDEX,','') AS Index_priv,
IF(Alter_priv='Y','ALTER,','') AS Alter_priv,
IF(Show_db_priv='Y','SHOW DATABASES,','') AS Show_db_priv,
IF(Super_priv='Y','SUPER,','') AS Super_priv
FROM mysql.user
ORDER BY user, host;
8.5 使用SSL加密连接
-- 创建使用SSL的用户
CREATE USER 'secure_user'@'%'
IDENTIFIED BY 'SecurePass123!'
REQUIRE SSL;
-- 要求特定的SSL证书
CREATE USER 'cert_user'@'%'
IDENTIFIED BY 'CertPass123!'
REQUIRE X509;
-- 查看SSL状态
SHOW VARIABLES LIKE '%ssl%';
SHOW STATUS LIKE '%ssl%';
9. 故障排查
9.1 连接问题
-- 检查用户是否存在
SELECT user, host FROM mysql.user WHERE user='username';
-- 检查用户密码是否正确
SELECT user, host, authentication_string FROM mysql.user WHERE user='username';
-- 检查连接权限
SHOW GRANTS FOR 'username'@'host';
-- 检查防火墙设置
-- netstat -tlnp | grep 3306
-- iptables -L -n
9.2 权限不生效
-- 刷新权限
FLUSH PRIVILEGES;
-- 检查权限表
SELECT * FROM mysql.db WHERE user='username';
SELECT * FROM mysql.tables_priv WHERE user='username';
-- 检查当前会话权限
SHOW GRANTS;
-- 重新连接数据库
9.3 密码重置
MySQL 5.7及以上：
-- 重置root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPass123!';
-- 跳过密码验证启动（紧急情况）
-- mysqld_safe --skip-grant-tables &
-- mysql -u root
-- FLUSH PRIVILEGES;
-- ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword!';
10. 权限管理脚本示例
10.1 创建应用用户脚本
-- 创建应用用户脚本
DELIMITER //
CREATE PROCEDURE create_app_user(
IN p_username VARCHAR(32),
IN p_host VARCHAR(60),
IN p_password VARCHAR(32),
IN p_database VARCHAR(64)
)
BEGIN
-- 创建用户
SET @create_user_sql = CONCAT(
'CREATE USER ''', p_username, '''@''', p_host, ''' IDENTIFIED BY ''', p_password, ''''
);
PREPARE stmt FROM @create_user_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 授予权限
SET @grant_sql = CONCAT(
'GRANT SELECT, INSERT, UPDATE, DELETE ON ', p_database, '.* TO ''', p_username, '''@''', p_host, ''''
);
PREPARE stmt FROM @grant_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 刷新权限
FLUSH PRIVILEGES;
SELECT CONCAT('User ', p_username, '@', p_host, ' created successfully with access to ', p_database) AS message;
END //
DELIMITER ;
-- 使用示例
CALL create_app_user('myapp', '%', 'AppPass123!', 'my_database');
10.2 权限审计脚本
-- 权限审计脚本
DELIMITER //
CREATE PROCEDURE audit_user_permissions()
BEGIN
-- 创建临时表存储结果
CREATE TEMPORARY TABLE IF NOT EXISTS permission_audit (
username VARCHAR(32),
host VARCHAR(60),
permission_level VARCHAR(20),
database_name VARCHAR(64),
table_name VARCHAR(64),
privileges TEXT
);
-- 插入全局权限
INSERT INTO permission_audit
SELECT user, host, 'GLOBAL', '*', '*',
CONCAT_WS(', ',
IF(Select_priv='Y','SELECT',''),
IF(Insert_priv='Y','INSERT',''),
IF(Update_priv='Y','UPDATE',''),
IF(Delete_priv='Y','DELETE',''),
IF(Create_priv='Y','CREATE',''),
IF(Drop_priv='Y','DROP',''),
IF(Reload_priv='Y','RELOAD',''),
IF(Shutdown_priv='Y','SHUTDOWN',''),
IF(Process_priv='Y','PROCESS',''),
IF(File_priv='Y','FILE',''),
IF(Grant_priv='Y','GRANT OPTION',''),
IF(References_priv='Y','REFERENCES',''),
IF(Index_priv='Y','INDEX',''),
IF(Alter_priv='Y','ALTER',''),
IF(Show_db_priv='Y','SHOW DATABASES',''),
IF(Super_priv='Y','SUPER','')
)
FROM mysql.user
WHERE user <> '';
-- 插入数据库权限
INSERT INTO permission_audit
SELECT user, host, 'DATABASE', db, '*',
CONCAT_WS(', ',
IF(Select_priv='Y','SELECT',''),
IF(Insert_priv='Y','INSERT',''),
IF(Update_priv='Y','UPDATE',''),
IF(Delete_priv='Y','DELETE',''),
IF(Create_priv='Y','CREATE',''),
IF(Drop_priv='Y','DROP',''),
IF(Grant_priv='Y','GRANT OPTION',''),
IF(References_priv='Y','REFERENCES',''),
IF(Index_priv='Y','INDEX',''),
IF(Alter_priv='Y','ALTER','')
)
FROM mysql.db;
-- 返回审计结果
SELECT * FROM permission_audit ORDER BY username, host, permission_level;
-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS permission_audit;
END //
DELIMITER ;
-- 使用示例
CALL audit_user_permissions();
10.3 批量权限管理
-- 批量创建测试用户
DELIMITER //
CREATE PROCEDURE create_test_users(
IN p_prefix VARCHAR(20),
IN p_count INT,
IN p_database VARCHAR(64)
)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= p_count DO
SET @username = CONCAT(p_prefix, '_user_', LPAD(i, 3, '0'));
SET @password = CONCAT('TestPass_', LPAD(i, 3, '0'), '!');
-- 创建用户
SET @create_sql = CONCAT(
'CREATE USER ''', @username, '''@''%'' IDENTIFIED BY ''', @password, ''''
);
PREPARE stmt FROM @create_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 授予权限
SET @grant_sql = CONCAT(
'GRANT SELECT, INSERT, UPDATE ON ', p_database, '.* TO ''', @username, '''@''%'''
);
PREPARE stmt FROM @grant_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
FLUSH PRIVILEGES;
SELECT CONCAT('Created ', p_count, ' test users with prefix ', p_prefix) AS message;
END //
DELIMITER ;
-- 使用示例
CALL create_test_users('test', 5, 'test_database');