MySQL 用戶由 用戶名 + 主機 唯一標識:'username'@'host'。host 支援:
localhost:本機 Unix socket 連線127.0.0.1:本機 TCP 連線%:任意主機(通配符)10.0.0.%:IP 範圍%.example.com:域名後綴-- 基本建立
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;
| 權限層級 | 語法範例 | 說明 |
|---|---|---|
| 全域 | 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'@'%'; | 執行權限 |
-- 建立角色
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'; -- 或在連線字串指定
-- 修改自己密碼
SET PASSWORD = 'NewPass123!';
-- 管理員修改他人密碼
SET PASSWORD FOR 'user'@'host' = 'NewPass123!';
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;
GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY 'NewPass123!';
UPDATE mysql.user SET authentication_string = '' WHERE User = 'user' AND Host = 'host';
FLUSH PRIVILEGES;
# 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
# 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
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';
-- 空密碼帳號
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');
-- 安裝 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
%validate_password,定期輪換(90 天)REQUIRE SSL,生產環境全鏈路加密mysqldump mysql user db columns_priv tables_priv procs_priv嚴格的帳號管理是資料庫安全的第一道防線,自動化工具讓合規變得可持續!