MySQL核心-用户权限管理

清峰大约 9 分钟

MySQL核心-用户权限管理

业务场景引入

在一个大型企业的MySQL环境中，不同角色需要不同的数据访问权限：

开发人员：只能访问开发库，不能修改生产数据
测试人员：可以访问测试库，进行数据的增删改查
数据分析师：只能读取生产库数据，用于报表分析
DBA：拥有所有数据库的完整管理权限
应用系统：只能访问特定业务库的特定表
备份程序：只需要备份相关的权限

合理的权限管理确保数据安全，防止误操作和恶意访问。

MySQL权限体系

权限级别层次

权限存储表

-- 查看MySQL权限相关系统表
USE mysql;

-- 用户账户信息
SELECT user, host, account_locked, password_expired FROM user;

-- 数据库级权限
SELECT * FROM db;

-- 表级权限
SELECT * FROM tables_priv;

-- 列级权限
SELECT * FROM columns_priv;

-- 存储过程权限
SELECT * FROM procs_priv;

用户管理实战

创建用户账户

-- 基础用户创建
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'ReadOnlyPass456!';

-- 创建用户并设置密码策略
CREATE USER 'dev_user'@'192.168.1.%' 
IDENTIFIED BY 'DevPass789!' 
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建应用专用用户
CREATE USER 'ecommerce_app'@'10.0.1.%' 
IDENTIFIED BY 'EcomApp2024#' 
PASSWORD EXPIRE NEVER 
ACCOUNT UNLOCK;

-- 查看用户创建结果
SELECT 
    user, 
    host, 
    account_locked, 
    password_expired, 
    password_lifetime,
    password_last_changed
FROM mysql.user 
WHERE user IN ('app_user', 'readonly_user', 'dev_user', 'ecommerce_app');

角色管理（MySQL 8.0+）

-- 创建角色
CREATE ROLE 'app_developer', 'data_analyst', 'dba_admin';

-- 为角色分配权限
-- 开发者角色
GRANT SELECT, INSERT, UPDATE, DELETE ON development.* TO 'app_developer';
GRANT SELECT ON INFORMATION_SCHEMA.* TO 'app_developer';

-- 数据分析师角色
GRANT SELECT ON production.orders TO 'data_analyst';
GRANT SELECT ON production.order_items TO 'data_analyst';
GRANT SELECT ON production.products TO 'data_analyst';
GRANT SELECT ON production.users TO 'data_analyst';

-- DBA管理员角色
GRANT ALL PRIVILEGES ON *.* TO 'dba_admin' WITH GRANT OPTION;

-- 将角色分配给用户
GRANT 'app_developer' TO 'dev_user'@'192.168.1.%';
GRANT 'data_analyst' TO 'analyst'@'10.0.2.%';
GRANT 'dba_admin' TO 'admin'@'localhost';

-- 设置默认角色
SET DEFAULT ROLE 'app_developer' TO 'dev_user'@'192.168.1.%';

-- 查看角色信息
SELECT * FROM mysql.role_edges;
SELECT * FROM mysql.default_roles;

权限分配策略

最小权限原则

-- 电商应用数据库权限设计

-- 1. 应用读写用户（最常用）
CREATE USER 'ecom_app'@'10.0.1.%' IDENTIFIED BY 'AppUser2024#';

-- 只授予业务表的必要权限
GRANT SELECT, INSERT, UPDATE ON ecommerce.users TO 'ecom_app'@'10.0.1.%';
GRANT SELECT, INSERT, UPDATE ON ecommerce.products TO 'ecom_app'@'10.0.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.orders TO 'ecom_app'@'10.0.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.order_items TO 'ecom_app'@'10.0.1.%';
GRANT SELECT, INSERT ON ecommerce.user_logs TO 'ecom_app'@'10.0.1.%';

-- 存储过程执行权限
GRANT EXECUTE ON PROCEDURE ecommerce.ProcessOrder TO 'ecom_app'@'10.0.1.%';
GRANT EXECUTE ON PROCEDURE ecommerce.UpdateInventory TO 'ecom_app'@'10.0.1.%';

-- 2. 只读分析用户
CREATE USER 'ecom_readonly'@'%' IDENTIFIED BY 'ReadOnly2024#';

-- 只读权限，且排除敏感字段
GRANT SELECT ON ecommerce.products TO 'ecom_readonly'@'%';
GRANT SELECT ON ecommerce.orders TO 'ecom_readonly'@'%';
GRANT SELECT ON ecommerce.order_items TO 'ecom_readonly'@'%';

-- 排除敏感列的权限控制
-- 创建视图来控制数据访问
CREATE VIEW ecommerce.users_safe AS 
SELECT user_id, username, real_name, email, registration_date, is_active
FROM ecommerce.users;

GRANT SELECT ON ecommerce.users_safe TO 'ecom_readonly'@'%';

-- 3. 备份专用用户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupUser2024#';

GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
GRANT SHOW DATABASES ON *.* TO 'backup_user'@'localhost';

开发环境权限配置

-- 开发环境用户权限配置

-- 1. 开发者用户（开发库完整权限）
CREATE USER 'developer'@'192.168.100.%' IDENTIFIED BY 'DevEnv2024#';

GRANT ALL PRIVILEGES ON development.* TO 'developer'@'192.168.100.%';
GRANT ALL PRIVILEGES ON testing.* TO 'developer'@'192.168.100.%';

-- 允许创建临时数据库
GRANT CREATE ON `dev_%`.* TO 'developer'@'192.168.100.%';
GRANT CREATE ON `test_%`.* TO 'developer'@'192.168.100.%';

-- 查看权限相关的信息
GRANT SELECT ON mysql.user TO 'developer'@'192.168.100.%';
GRANT SELECT ON information_schema.* TO 'developer'@'192.168.100.%';

-- 2. 测试用户（测试库权限）
CREATE USER 'tester'@'192.168.101.%' IDENTIFIED BY 'TestEnv2024#';

GRANT SELECT, INSERT, UPDATE, DELETE ON testing.* TO 'tester'@'192.168.101.%';
GRANT CREATE, DROP ON `temp_%`.* TO 'tester'@'192.168.101.%';

-- 3. 实习生用户（受限权限）
CREATE USER 'intern'@'192.168.102.%' IDENTIFIED BY 'InternUser2024#';

-- 只能查看特定表
GRANT SELECT ON development.products TO 'intern'@'192.168.102.%';
GRANT SELECT ON development.categories TO 'intern'@'192.168.102.%';

-- 设置连接限制
ALTER USER 'intern'@'192.168.102.%' 
WITH MAX_CONNECTIONS_PER_HOUR 100
     MAX_QUERIES_PER_HOUR 1000
     MAX_USER_CONNECTIONS 5;

生产环境权限管控

-- 生产环境严格权限控制

-- 1. 应用服务器用户
CREATE USER 'prod_app'@'10.100.1.10' IDENTIFIED BY 'ProdApp2024#$';
CREATE USER 'prod_app'@'10.100.1.11' IDENTIFIED BY 'ProdApp2024#$';

-- 只授予必要的业务权限
GRANT SELECT, INSERT, UPDATE ON production.users TO 'prod_app'@'10.100.1.%';
GRANT SELECT, INSERT, UPDATE ON production.products TO 'prod_app'@'10.100.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON production.orders TO 'prod_app'@'10.100.1.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON production.order_items TO 'prod_app'@'10.100.1.%';

-- 严禁DDL操作
-- 严禁TRUNCATE、DROP等危险操作

-- 2. 监控用户
CREATE USER 'monitor'@'10.100.2.%' IDENTIFIED BY 'Monitor2024#';

GRANT PROCESS ON *.* TO 'monitor'@'10.100.2.%';
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'10.100.2.%';
GRANT SELECT ON performance_schema.* TO 'monitor'@'10.100.2.%';
GRANT SELECT ON information_schema.* TO 'monitor'@'10.100.2.%';

-- 3. 报表用户（只读）
CREATE USER 'reporter'@'10.100.3.%' IDENTIFIED BY 'Reporter2024#';

-- 通过视图控制访问范围
CREATE VIEW production.order_report AS
SELECT 
    order_id,
    order_date,
    total_amount,
    order_status,
    CASE 
        WHEN user_id IS NOT NULL THEN 'REGISTERED'
        ELSE 'GUEST'
    END AS user_type
FROM production.orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 2 YEAR);

GRANT SELECT ON production.order_report TO 'reporter'@'10.100.3.%';

权限安全加固

密码策略配置

-- 查看密码验证组件
SHOW PLUGINS;

-- 安装密码验证插件（如果未安装）
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- 配置密码策略
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 2;
SET GLOBAL validate_password.special_char_count = 1;

-- 查看密码策略配置
SHOW VARIABLES LIKE 'validate_password%';

-- 强制密码过期策略
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 密码历史策略（防止重复使用旧密码）
SET GLOBAL password_history = 5;
SET GLOBAL password_reuse_interval = 90;

SSL加密连接

-- 查看SSL状态
SHOW VARIABLES LIKE 'have_ssl';
SHOW STATUS LIKE 'Ssl_cipher';

-- 要求用户使用SSL连接
ALTER USER 'secure_user'@'%' REQUIRE SSL;

-- 要求特定的SSL配置
ALTER USER 'admin_user'@'10.0.1.%' 
REQUIRE SSL 
AND SUBJECT '/C=CN/ST=Beijing/L=Beijing/O=Company/CN=admin'
AND CIPHER 'AES256-SHA';

-- 查看用户SSL要求
SELECT user, host, ssl_type, ssl_cipher, x509_issuer, x509_subject 
FROM mysql.user 
WHERE ssl_type != '';

账户安全检查

-- 检查空密码账户
SELECT user, host 
FROM mysql.user 
WHERE authentication_string = '';

-- 检查匿名用户
SELECT user, host 
FROM mysql.user 
WHERE user = '';

-- 检查过期密码
SELECT user, host, password_expired, password_last_changed 
FROM mysql.user 
WHERE password_expired = 'Y';

-- 检查锁定账户
SELECT user, host, account_locked 
FROM mysql.user 
WHERE account_locked = 'Y';

-- 检查危险权限
SELECT user, host 
FROM mysql.user 
WHERE Super_priv = 'Y' OR File_priv = 'Y';

权限监控与审计

连接监控

-- 查看当前连接
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
WHERE user NOT IN ('root', 'mysql.session', 'mysql.sys');

-- 查看用户连接统计
SELECT 
    user,
    current_connections,
    total_connections
FROM performance_schema.users;

-- 查看用户活动统计
SELECT 
    user,
    host,
    current_connections,
    total_connections,
    total_ssl_connections
FROM performance_schema.accounts
WHERE user IS NOT NULL;

权限变更审计

-- 创建权限变更审计表
CREATE TABLE security_audit_log (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    event_type ENUM('USER_CREATE', 'USER_DROP', 'GRANT', 'REVOKE', 'PASSWORD_CHANGE'),
    user_account VARCHAR(100),
    target_user VARCHAR(100),
    privileges_changed TEXT,
    client_host VARCHAR(100),
    executed_by VARCHAR(100),
    sql_statement TEXT
) ENGINE=InnoDB;

-- 权限变更监控触发器（需要在mysql库上创建）
-- 注意：这需要特殊权限，通常由DBA操作

-- 查看最近的权限变更
SELECT 
    event_time,
    event_type,
    user_account,
    target_user,
    privileges_changed
FROM security_audit_log 
ORDER BY event_time DESC 
LIMIT 20;

登录失败监控

-- 启用错误日志记录
SET GLOBAL log_error_verbosity = 3;

-- 查看错误日志中的认证失败
-- 通过系统命令查看日志
-- grep "Access denied" /var/log/mysql/error.log | tail -20

-- 创建登录失败记录表
CREATE TABLE login_failures (
    failure_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    failure_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    client_host VARCHAR(100),
    attempted_user VARCHAR(100),
    failure_reason VARCHAR(200),
    
    INDEX idx_failure_time (failure_time),
    INDEX idx_client_host (client_host),
    INDEX idx_attempted_user (attempted_user)
) ENGINE=InnoDB;

-- 应用层记录登录失败
-- 当应用检测到数据库连接失败时，记录到此表

权限管理工具脚本

权限分析脚本

#!/bin/bash
# MySQL权限分析脚本

MYSQL_USER="root"
MYSQL_PASS="password"
OUTPUT_FILE="/tmp/mysql_permissions_report_$(date +%Y%m%d_%H%M%S).txt"

echo "MySQL Permission Analysis Report" > ${OUTPUT_FILE}
echo "Generated on: $(date)" >> ${OUTPUT_FILE}
echo "================================" >> ${OUTPUT_FILE}

# 用户账户概览
echo -e "\n1. User Accounts Overview:" >> ${OUTPUT_FILE}
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT 
    user, 
    host, 
    account_locked, 
    password_expired,
    password_lifetime,
    max_connections,
    max_user_connections
FROM mysql.user 
ORDER BY user, host;" >> ${OUTPUT_FILE}

# 危险权限检查
echo -e "\n2. Users with Dangerous Privileges:" >> ${OUTPUT_FILE}
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT user, host, 'SUPER' as dangerous_priv FROM mysql.user WHERE Super_priv = 'Y'
UNION ALL
SELECT user, host, 'FILE' FROM mysql.user WHERE File_priv = 'Y'
UNION ALL
SELECT user, host, 'PROCESS' FROM mysql.user WHERE Process_priv = 'Y'
UNION ALL
SELECT user, host, 'SHUTDOWN' FROM mysql.user WHERE Shutdown_priv = 'Y'
ORDER BY user, host;" >> ${OUTPUT_FILE}

# 数据库级权限
echo -e "\n3. Database Level Privileges:" >> ${OUTPUT_FILE}
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT user, host, db, 
CONCAT_WS(',', 
    IF(Select_priv='Y', 'SELECT', NULL),
    IF(Insert_priv='Y', 'INSERT', NULL),
    IF(Update_priv='Y', 'UPDATE', NULL),
    IF(Delete_priv='Y', 'DELETE', NULL),
    IF(Create_priv='Y', 'CREATE', NULL),
    IF(Drop_priv='Y', 'DROP', NULL),
    IF(Alter_priv='Y', 'ALTER', NULL)
) as privileges
FROM mysql.db 
ORDER BY user, host, db;" >> ${OUTPUT_FILE}

echo "Permission analysis completed: ${OUTPUT_FILE}"

用户权限清理脚本

#!/bin/bash
# 清理无效用户和权限

MYSQL_USER="root"
MYSQL_PASS="password"
LOG_FILE="/var/log/mysql_cleanup.log"

echo "$(date '+%Y-%m-%d %H:%M:%S') - Starting MySQL user cleanup" >> ${LOG_FILE}

# 查找并删除空密码用户
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
SELECT CONCAT('DROP USER \\'', user, '\\'@\\'', host, '\\';') as cleanup_sql
FROM mysql.user 
WHERE authentication_string = '' AND user != 'root';" > /tmp/empty_password_users.sql

if [ -s /tmp/empty_password_users.sql ]; then
    echo "Found users with empty passwords, cleaning up..." >> ${LOG_FILE}
    mysql -u${MYSQL_USER} -p${MYSQL_PASS} < /tmp/empty_password_users.sql
fi

# 查找并删除匿名用户
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;" >> ${LOG_FILE} 2>&1

# 删除测试数据库
mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\\_%';
FLUSH PRIVILEGES;" >> ${LOG_FILE} 2>&1

echo "$(date '+%Y-%m-%d %H:%M:%S') - MySQL user cleanup completed" >> ${LOG_FILE}

权限管理最佳实践

权限设计原则

最小权限原则
- 只授予完成工作所需的最小权限
- 定期审查和清理不必要的权限
- 使用角色管理简化权限分配
职责分离原则
- 开发、测试、生产环境权限分离
- 应用账户和管理账户分离
- 读写权限和只读权限分离
权限生命周期管理
- 新员工入职权限申请流程
- 员工离职权限回收流程
- 定期权限审核和更新

常见安全配置

-- 删除默认的危险账户和数据库
DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';
DROP DATABASE IF EXISTS test;

-- 重命名root用户（可选）
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'VerySecurePassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
-- DROP USER 'root'@'localhost';  -- 谨慎操作

-- 限制root用户远程访问
UPDATE mysql.user SET host='localhost' WHERE user='root';
FLUSH PRIVILEGES;

-- 设置安全的默认配置
SET GLOBAL local_infile = 0;  -- 禁用LOAD DATA LOCAL INFILE
SET GLOBAL skip_show_database = 1;  -- 隐藏数据库列表

权限监控告警

-- 创建权限监控视图
CREATE VIEW security_monitor AS
SELECT 
    u.user,
    u.host,
    u.account_locked,
    u.password_expired,
    u.password_last_changed,
    CASE 
        WHEN u.Super_priv = 'Y' OR u.File_priv = 'Y' THEN 'HIGH_RISK'
        WHEN u.Process_priv = 'Y' OR u.Reload_priv = 'Y' THEN 'MEDIUM_RISK'
        ELSE 'LOW_RISK'
    END AS risk_level,
    p.current_connections,
    p.total_connections
FROM mysql.user u
LEFT JOIN performance_schema.accounts p ON u.user = p.user AND u.host = p.host
WHERE u.user NOT IN ('mysql.session', 'mysql.sys', 'mysql.infoschema');

-- 定期检查高风险用户
SELECT * FROM security_monitor WHERE risk_level = 'HIGH_RISK';

MySQL权限管理是数据库安全的核心组成部分，需要建立完善的权限分配、监控和审计机制，确保数据库系统的安全性和合规性。