為什麼不再用 INSERT 寫 user 表？

MySQL 5.7+ mysql.user 表新增 ssl_cipher、x509_issuer、account_locked、password_expired 等**非空無預設值欄位**，直接 INSERT 會報 Field 'xxx' doesn't have a default value。**必須使用 CREATE USER / GRANT 官方語句**。

建立用戶 (CREATE USER)

標準語法 (5.7/8.0 通用)

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';
-- 指定認證插件 (8.0 預設 caching_sha2_password，舊驅動需 mysql_native_password)
CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass456!';
-- 鎖定帳號 (8.0)
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'pass' ACCOUNT LOCK;
-- 密碼過期策略 (8.0)
CREATE USER 'audit_user'@'%' IDENTIFIED BY 'pass' PASSWORD EXPIRE INTERVAL 90 DAY;

授權 (GRANT)

權限層級與語法

-- 全域權限 (慎用)
GRANT SELECT, RELOAD ON *.* TO 'monitor'@'192.168.1.100';

-- 庫級權限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'192.168.1.%';

-- 表級權限
GRANT SELECT (id, name, email) ON app_db.users TO 'api_user'@'%';  -- 欄位級

-- 存儲過程/函數
GRANT EXECUTE ON PROCEDURE app_db.proc_name TO 'dev'@'%';

-- 代理用戶 (Proxy User)
GRANT PROXY ON 'real_user'@'%' TO 'proxy_user'@'%';

WITH GRANT OPTION：允許轉授權限

GRANT SELECT ON app_db.* TO 'admin'@'192.168.1.100' WITH GRANT OPTION;

刷新生效

FLUSH PRIVILEGES;

MySQL 8.0+ 權限修改通常即時生效，但習慣性執行無妨。

MySQL 8.0 角色機制：批量權限管理利器

-- 1. 建立角色
CREATE ROLE 'app_readonly', 'app_readwrite', 'app_admin';

-- 2. 角色授權
GRANT SELECT ON app_db.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_readwrite';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_admin';

-- 3. 用戶授予角色
CREATE USER 'svc_order'@'%' IDENTIFIED BY 'pass';
GRANT 'app_readwrite' TO 'svc_order'@'%';

-- 4. 設定預設角色 (登入自動生效)
SET DEFAULT ROLE 'app_readwrite' TO 'svc_order'@'%';

-- 5. 會話中切換角色
SET ROLE 'app_readonly';  -- 臨時降權

角色變更權限，所有關聯用戶**即時生效**，無需逐個修改。

修改密碼與認證插件

-- 修改密碼 (8.0 推薦)
ALTER USER 'user'@'host' IDENTIFIED BY 'NewPass123!';

-- 修改密碼並切換認證插件
ALTER USER 'user'@'host' IDENTIFIED WITH mysql_native_password BY 'NewPass123!';

-- 鎖定/解鎖帳號
ALTER USER 'user'@'host' ACCOUNT LOCK;
ALTER USER 'user'@'host' ACCOUNT UNLOCK;

撤銷權限 (REVOKE)

REVOKE INSERT, UPDATE ON app_db.* FROM 'readonly'@'192.168.1.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_admin'@'%';
FLUSH PRIVILEGES;

⚠️ 必須**精確匹配原 GRANT 的層級與權限**，否則不生效。用 SHOW GRANTS FOR 'user'@'host'; 核對。

刪除用戶

DROP USER 'user'@'host';
-- 連帶刪除其資料庫 (謹慎)
DROP DATABASE user_db;

密碼策略與安全加固 (validate_password)

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

-- 設定策略 (全域變數)
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;
SET GLOBAL validate_password.policy = 'STRONG';  -- LOW/MEDIUM/STRONG

-- 檢查密碼強度
SELECT VALIDATE_PASSWORD_STRENGTH('WeakPass');  -- 返回分數，< 50 拒絕

審計與合規檢查查詢

-- 掃描通配主機帳號
SELECT User, Host FROM mysql.user WHERE Host = '%';

-- 掃描空密碼/弱密碼帳號 (8.0 authentication_string 為雜湊，需配合插件)
SELECT User, Host, plugin FROM mysql.user WHERE authentication_string = '' OR authentication_string IS NULL;

-- 掃描超級權限帳號
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y' AND NOT (User='root' AND Host='localhost');

-- 顯示所有用戶權限
SELECT User, Host, GROUP_CONCAT(privilege_type) 
FROM information_schema.user_privileges 
GROUP BY User, Host;

總結

MySQL 5.7/8.0 用戶管理核心：**CREATE USER 建立身份 → GRANT 最小權限 → 角色批量管理 → validate_password 強制強度 → 定期審計清理**。拒絕 INSERT INTO user、拒絕 root@%、拒絕弱密碼、拒絕過度授權，建立「最小權限、可審計、可輪換」的資料庫安全基線。