兩種授權方式對比

維度	GRANT/REVOKE (推薦)	直接操作授權表
語法簡潔性	高，語義清晰	低，需手動對應 40+ 權限欄位
錯誤率	低，語法檢查完善	高，易漏欄位、型別不符
生效機制	**即時生效**，無需 FLUSH	**必須 FLUSH PRIVILEGES** 或重啟
版本相容性	跨版本穩定	授權表結構隨版本變動 (5.7/8.0 大幅調整)
審計追蹤	一般日誌記錄完整 SQL	難以區分業務與管理操作

GRANT 實戰範例：從入門到進階

建立超級管理員 (僅限本地/堡壘機)

CREATE USER 'dba'@'10.0.0.%' IDENTIFIED BY 'SuperStrongPass#2024';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.%' WITH GRANT OPTION;

建立應用讀寫帳號 (單庫)

CREATE USER 'app_rw'@'192.168.10.%' IDENTIFIED BY 'AppRwPass#2024';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `myapp_db`.* TO 'app_rw'@'192.168.10.%';

建立唯讀報表帳號 (多庫)

CREATE USER 'report_ro'@'192.168.20.%' IDENTIFIED BY 'ReportRoPass#2024';
GRANT SELECT ON `db_sales`.* TO 'report_ro'@'192.168.20.%';
GRANT SELECT ON `db_warehouse`.* TO 'report_ro'@'192.168.20.%';

建立開發人員帳號 (僅特定表、含 DDL)

CREATE USER 'dev'@'192.168.30.%' IDENTIFIED BY 'DevPass#2024';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON `myapp_db`.`orders` TO 'dev'@'192.168.30.%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON `myapp_db`.`order_items` TO 'dev'@'192.168.30.%';

建立監控帳號 (最小權限)

CREATE USER 'monitor'@'10.0.1.%' IDENTIFIED BY 'MonitorPass#2024' REQUIRE SSL;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON `performance_schema`.* TO 'monitor'@'10.0.1.%';
GRANT SELECT ON `sys`.* TO 'monitor'@'10.0.1.%';

權限回收與帳號刪除

REVOKE INSERT, UPDATE ON `myapp_db`.* FROM 'app_rw'@'192.168.10.%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_dev'@'%';
DROP USER 'old_dev'@'%';

※ REVOKE 不會自動刪除使用者，需額外 DROP USER。

授權表結構解析 (5.7/8.0 關鍵變化)

表	範圍	關鍵欄位 (5.7)	8.0 變化
user	全域	Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string	移除 Password 欄位，統一 authentication_string；新增 account_locked、password_expired、password_last_changed、password_lifetime
db	資料庫級	Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Execute_priv, Event_priv, Trigger_priv	大體相同
tables_priv	資料表級	Host, Db, User, Table_name, Grantor, Timestamp, Table_priv, Column_priv	相同
columns_priv	欄位級	Host, Db, User, Table_name, Column_name, Timestamp, Column_priv	相同
procs_priv	預存程序級	Host, Db, User, Routine_name, Routine_type, Grantor, Proc_priv, Timestamp	相同
proxies_priv	代理使用者	Host, User, Proxied_host, Proxied_user, Grantor, Proxied_priv, With_grant, Timestamp	8.0 新增
global_grants	全域動態權限	—	8.0 新增，取代 user 表部分管理權限欄位
default_roles	預設角色	—	8.0 新增

角色基礎存取控制 (MySQL 8.0+)

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

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

-- 將角色授予使用者
CREATE USER 'dev1'@'%' IDENTIFIED BY 'pass';
GRANT 'app_readwrite' TO 'dev1'@'%';

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

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

權限驗證與審計查詢

-- 查看使用者完整權限
SHOW GRANTS FOR 'app_rw'@'192.168.10.%';

-- 查看當前會話權限
SHOW GRANTS FOR CURRENT_USER();

-- 審計所有使用者權限彙總
SELECT User, Host,
  GROUP_CONCAT(DISTINCT CONCAT(Table_priv, ' ON ', Db, '.', Table_name) SEPARATOR '; ') AS table_privs
FROM mysql.tables_priv GROUP BY User, Host;

安全加固清單

1. 移除匿名帳號：DELETE FROM mysql.user WHERE User='';
2. 移除測試資料庫權限：DELETE FROM mysql.db WHERE Db LIKE 'test%';
3. 禁用 root 遠端：DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');
4. 所有帳號強制 SSL：ALTER USER 'app_rw'@'%' REQUIRE SSL;
5. 啟用密碼策略：INSTALL COMPONENT 'file://component_validate_password'; SET GLOBAL validate_password.length=12;
6. 定期輪換密碼，撤銷離職人員帳號。