萬盛學電腦網

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

MySQL 用戶授權與權限管理實戰:建立用戶、授權、撤銷、最小權限原則

MySQL 權限體系基於「登入帳號 + 來源主機」雙元組,權限層級分為全局、庫、表、欄位、存儲程序。本文通過完整實戰範例演示用戶建立、授權、查看、撤銷、密碼管理,並闡述最小權限原則與安全加固清單。

核心概念

  • 用戶標識:'username'@'host',host 支援 %、IP、CIDR、主機名
  • 權限層級:GLOBAL > DATABASE > TABLE > COLUMN > ROUTINE
  • 權限生效:FLUSH PRIVILEGES 或 8.0+ 自動生效

實戰操作全流程

1. 建立用戶

-- 僅本地連接
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';

2. 授權

-- 庫級所有權限(常用)
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;

3. 查看權限

-- 當前用戶
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'

4. 撤銷權限

-- 撤銷特定權限
REVOKE INSERT ON `gbk`.* FROM 'byrd'@'localhost';

-- 撤銷所有權限(保留用戶)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'byrd'@'localhost';

5. 刪除用戶

DROP USER 'byrd'@'localhost';

6. 修改密碼

-- 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;

7. 遠程連接驗證

# 服務端:授權遠程 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

ALL PRIVILEGES 包含的具體權限

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

安全加固清單

  1. 刪除匿名用戶:DROP USER ''@'%'; DROP USER ''@'localhost';
  2. 移除測試庫:DROP DATABASE test; DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
  3. 禁止 root 遠程登入:DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');
  4. 強制密碼策略:INSTALL COMPONENT 'file://component_validate_password'; 設定長度、複雜度、過期週期
  5. 啟用 SSL/TLS:REQUIRE SSL 在 GRANT 中指定
  6. 審計日誌:INSTALL PLUGIN audit_log SONAME 'audit_log.so';
  7. 定期輪換密鑰、審計權限變更記錄

總結

MySQL 權限管理核心:用戶=帳號+來源、分級授權、最小權限、定期審計。8.0 引入角色、密碼策略、雙因子認證,進一步強化企業級安全。將權限管理納入基礎設施即代碼,實現版本控制、自動化佈署與合規檢查。

copyright © 萬盛學電腦網 all rights reserved