萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 用戶帳號與密碼管理:建立、授權、修改、審計最佳實務

用戶帳號核心概念

MySQL 用戶由 用戶名 + 主機 唯一標識:'username'@'host'。host 支援:

  • localhost:本機 Unix socket 連線
  • 127.0.0.1:本機 TCP 連線
  • %:任意主機(通配符)
  • 10.0.0.%:IP 範圍
  • %.example.com:域名後綴

建立用戶與授權(MySQL 8.0+ 標準語法)

1. 建立用戶

-- 基本建立
CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'StrongPass123!';

-- 指定認證插件 (8.0 預設 caching_sha2_password)
CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass!';

-- 要求 SSL 連線
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'Pass!' REQUIRE SSL;

-- 密碼過期策略
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'TempPass!' PASSWORD EXPIRE INTERVAL 30 DAY;

2. 授權權限

權限層級語法範例說明
全域GRANT SELECT, RELOAD ON *.* TO 'admin'@'%';跨所有資料庫
資料庫GRANT ALL PRIVILEGES ON mydb.* TO 'dev'@'%';特定資料庫所有物件
資料表GRANT SELECT, INSERT ON mydb.orders TO 'readonly'@'%';特定表
欄位GRANT SELECT (id, name, email) ON mydb.users TO 'api'@'%';欄位級權限
存儲程序GRANT EXECUTE ON PROCEDURE mydb.proc_name TO 'user'@'%';執行權限

3. 角色管理 (8.0+)

-- 建立角色
CREATE ROLE 'app_readonly', 'app_readwrite', 'db_admin';

-- 授權給角色
GRANT SELECT ON mydb.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_readwrite';
GRANT ALL ON mydb.* TO 'db_admin';

-- 將角色授予用戶
GRANT 'app_readwrite' TO 'app_user'@'10.0.0.%';

-- 設定預設角色(登入自動啟用)
SET DEFAULT ROLE 'app_readwrite' TO 'app_user'@'10.0.0.%';

-- 用戶啟用角色
SET ROLE 'app_readwrite';  -- 或在連線字串指定

修改密碼完整方法

方法 1:SET PASSWORD(推薦,無需 FLUSH)

-- 修改自己密碼
SET PASSWORD = 'NewPass123!';

-- 管理員修改他人密碼
SET PASSWORD FOR 'user'@'host' = 'NewPass123!';

方法 2:ALTER USER(功能最全)

ALTER USER 'user'@'host' IDENTIFIED BY 'NewPass123!';
-- 同時可設定:
ALTER USER 'user'@'host' 
    IDENTIFIED BY 'NewPass123!' 
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 2
    ACCOUNT UNLOCK;

方法 3:GRANT ... IDENTIFIED BY(舊版相容)

GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY 'NewPass123!';

方法 4:直接更新 user 表(不推薦,需 FLUSH)

UPDATE mysql.user SET authentication_string = '' WHERE User = 'user' AND Host = 'host';
FLUSH PRIVILEGES;

忘記 Root 密碼:生產環境安全重置流程

方案 A:--skip-grant-tables(需重啟,短暫安全窗口)

# 1. 停止服務
systemctl stop mysqld

# 2. 啟動跳過授權
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
systemctl start mysqld

# 3. 登入重置
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass2024!';
EXIT;

# 4. 正常重啟
systemctl unset-environment MYSQLD_OPTS
systemctl restart mysqld

方案 B:init_file 方式(無需 --skip-grant-tables,更安全)

# 1. 建立初始化 SQL 檔
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass2024!';" > /tmp/reset_root.sql

# 2. 停止服務
systemctl stop mysqld

# 3. 以 init_file 啟動
mysqld --init-file=/tmp/reset_root.sql --user=mysql &

# 4. 驗證後正常重啟
mysqladmin -u root -p'NewStrongPass2024!' shutdown
systemctl start mysqld

用戶審計與安全檢查

1. 列出所有用戶與權限

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

-- 查看完整權限
SELECT * FROM information_schema.USER_PRIVILEGES;
SHOW GRANTS FOR 'user'@'host';

2. 稽核高風險帳號

-- 空密碼帳號
SELECT User, Host FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;

-- 匿名用戶
SELECT User, Host FROM mysql.user WHERE User = '';

-- 過度授權帳號(擁有 SUPER/FILE/SHUTDOWN)
SELECT u.User, u.Host, p.Privilege_type
FROM mysql.user u
JOIN mysql.global_grants p ON u.User = p.User AND u.Host = p.Host
WHERE p.Privilege_type IN ('SUPER', 'FILE', 'SHUTDOWN', 'CREATE USER', 'RELOAD')
  AND u.User NOT IN ('root', 'mysql.sys', 'mysql.session');

3. 密碼複雜度驗證

-- 安裝 validate_password 元件
INSTALL COMPONENT 'file://component_validate_password';

-- 設定策略
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 = 1;
SET GLOBAL validate_password.special_char_count = 1;

自動化帳號生命週期管理

#!/bin/bash
# rotate_service_accounts.sh

DB_ADMIN="root"
DB_PASS="$(cat /run/secrets/db_root_pass)"

# 讀取服務帳號清單
accounts=(
    "app_user@10.0.0.%"
    "report_user@10.0.1.%"
    "backup_user@10.0.2.%"
)

for account in "${accounts[@]}"; do
    user_host=(${account//@/ })
    user=${user_host[0]}
    host=${user_host[1]}
    
    new_pass=$(openssl rand -base64 32)
    
    mysql -u"$DB_ADMIN" -p"$DB_PASS" -e "
        ALTER USER '$user'@'$host' IDENTIFIED BY '$new_pass' 
        PASSWORD EXPIRE INTERVAL 90 DAY;
        FLUSH PRIVILEGES;
    "
    
    # 更新 Vault/Consul/Secrets Manager
    vault kv put secret/mysql/$user password="$new_pass" rotation_date="$(date -I)"
    
    # 通知應用團隊(Webhook/郵件)
    curl -X POST "$WEBHOOK_URL" -d "{\"user\":\"$user\",\"host\":\"$host\"}"
done

最佳實務總結

  1. 專用帳號:每個應用/服務獨立帳號,嚴禁共用 root
  2. 最小權限:僅授予必要資料庫、表、欄位、操作權限
  3. 網路限制:Host 限定具體 IP/網段,避免 %
  4. 強密碼策略:啟用 validate_password,定期輪換(90 天)
  5. SSL 強制:REQUIRE SSL,生產環境全鏈路加密
  6. 審計日誌:啟用 MariaDB Audit Plugin / MySQL Enterprise Audit
  7. 定期清理:移除離職人員、廢棄專案、測試帳號
  8. 備份 user 表:mysqldump mysql user db columns_priv tables_priv procs_priv

嚴格的帳號管理是資料庫安全的第一道防線,自動化工具讓合規變得可持續!

copyright © 萬盛學電腦網 all rights reserved