MySQL 權限體系基於「登入帳號 + 來源主機」雙元組,權限層級分為全局、庫、表、欄位、存儲程序。本文通過完整實戰範例演示用戶建立、授權、查看、撤銷、密碼管理,並闡述最小權限原則與安全加固清單。
'username'@'host',host 支援 %、IP、CIDR、主機名FLUSH PRIVILEGES 或 8.0+ 自動生效-- 僅本地連接
CREATE USER 'byrd'@'localhost' IDENTIFIED BY 'admin123';
-- 允許任意主機(生產慎用)
CREATE USER 'byrd'@'%' IDENTIFIED BY 'admin123';
-- 指定 IP/網段
CREATE USER 'byrd'@'192.168.1.%' IDENTIFIED BY 'admin123';
-- 指定認證插件(8.0+ 預設 caching_sha2_password)
CREATE USER 'byrd'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin123';
-- 庫級所有權限(常用)
GRANT ALL PRIVILEGES ON gbk.* TO 'byrd'@'localhost';
-- 表級精確授權
GRANT SELECT, INSERT, UPDATE, DELETE ON gbk.orders TO 'byrd'@'localhost';
-- 欄位級授權(敏感欄位隔離)
GRANT SELECT (id, name, email), UPDATE (name, email) ON gbk.users TO 'byrd'@'localhost';
-- 存儲過程/函數執行權限
GRANT EXECUTE ON PROCEDURE gbk.proc_name TO 'byrd'@'localhost';
-- 授權選項:WITH GRANT OPTION 允許被授權者再授權(極度謹慎)
GRANT SELECT ON db.* TO 'byrd'@'localhost' WITH GRANT OPTION;
-- 當前用戶
SHOW GRANTS;
-- 指定用戶
SHOW GRANTS FOR 'byrd'@'localhost';
-- 輸出範例:
GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'
GRANT ALL PRIVILEGES ON `gbk`.* TO 'byrd'@'localhost'
-- 撤銷特定權限
REVOKE INSERT ON `gbk`.* FROM 'byrd'@'localhost';
-- 撤銷所有權限(保留用戶)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'byrd'@'localhost';
DROP USER 'byrd'@'localhost';
-- 8.0+ 標準語法
ALTER USER 'byrd'@'localhost' IDENTIFIED BY 'newStrongPass123!';
-- 5.7 兼容語法
SET PASSWORD FOR 'byrd'@'localhost' = PASSWORD('newStrongPass123!');
-- 根用戶密碼重置(跳過授權表啟動後)
UPDATE mysql.user SET authentication_string='' WHERE User='root';
FLUSH PRIVILEGES;
# 服務端:授權遠程 IP
GRANT ALL ON gbk.* TO 'remote_user'@'192.168.1.100' IDENTIFIED BY 'pass123';
# 客戶端連接測試
mysql -h server_ip -u remote_user -p -P 3306
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES,
SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER,
EVENT, TRIGGER, CREATE TABLESPACE
| 角色 | 典型權限集 | Host 限制 |
|---|---|---|
| 應用讀寫 | SELECT, INSERT, UPDATE, DELETE ON db.* | 應用服務器 IP 段 |
| 應用唯讀 | SELECT ON db.* | 應用服務器 IP 段 |
| 報表/分析 | SELECT ON db.* + PROCESS (查看進程) | 分析平台 IP |
| 遷移/同步工具 | REPLICATION SLAVE, REPLICATION CLIENT, SELECT | 同步工具 IP |
| DBA 管理 | ALL PRIVILEGES ON *.* | 堡壘機/跳板機 IP |
| 監控採集 | PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* | 監控系統 IP |
DROP USER ''@'%'; DROP USER ''@'localhost';DROP DATABASE test; DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');INSTALL COMPONENT 'file://component_validate_password'; 設定長度、複雜度、過期週期REQUIRE SSL 在 GRANT 中指定INSTALL PLUGIN audit_log SONAME 'audit_log.so';MySQL 權限管理核心:用戶=帳號+來源、分級授權、最小權限、定期審計。8.0 引入角色、密碼策略、雙因子認證,進一步強化企業級安全。將權限管理納入基礎設施即代碼,實現版本控制、自動化佈署與合規檢查。