萬盛學電腦網

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

MySQL 用戶授權與撤銷權限完整操作指南

GRANT 授權語法詳解

GRANT priv_type [(column_list)]
  ON [object_type] priv_level
  TO user_or_role [, user_or_role] ...
  [WITH GRANT OPTION]
  [AS user [WITH ROLE]]

常用授權場景

-- 1. 讀寫權限(應用常用)
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';

-- 2. 唯讀權限(報表/備份)
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 3. 開發權限(含建表/索引)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON mydb.* TO 'dev'@'%';

-- 4. 管理權限(DBA)
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;

-- 5. 複制權限(從庫同步)
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 6. 監控權限
GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'monitor'@'localhost';

欄位級授權(精細控制)

GRANT SELECT (id, name, email), UPDATE (email) ON mydb.users TO 'hr'@'%';

密碼管理

修改密碼

-- 修改他人密碼(需 SUPER 權限)
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass123!';
-- 或
SET PASSWORD FOR 'app'@'%' = 'NewPass123!';

-- 修改自身密碼
SET PASSWORD = 'NewPass123!';

密碼策略(8.0+)

ALTER USER 'app'@'%' 
  PASSWORD EXPIRE INTERVAL 90 DAY
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 2
  PASSWORD HISTORY 5
  PASSWORD REUSE INTERVAL 365 DAY;

REVOKE 撤銷權限

-- 撤銷特定權限
REVOKE INSERT, UPDATE ON mydb.* FROM 'app'@'%';

-- 撤銷所有權限(含 GRANT OPTION)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

-- 撤銷角色(8.0+)
REVOKE 'read_only_role' FROM 'app'@'%';

REVOKE 關鍵點

  • user@host 必須與原 GRANT 完全一致
  • 僅移除權限,**不刪除用戶帳號**
  • 撤銷後需 FLUSH PRIVILEGES(8.0 自動刷新)

徹底刪除用戶

-- 1. 撤銷所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';
-- 2. 刪除用戶
DROP USER 'app'@'%';
-- 3. 刷新(8.0 通常自動)
FLUSH PRIVILEGES;

查看用戶權限

-- 顯示特定用戶權限
SHOW GRANTS FOR 'app'@'%';
-- 顯示當前用戶權限
SHOW GRANTS;
-- 查看所有用戶
SELECT user, host, plugin, authentication_string FROM mysql.user;

實戰最佳實踐

  1. 最小權限原則:應用帳號僅授予必要資料庫的 CRUD,禁止 DROP、ALTER、GRANT
  2. 按環境隔離:app_prod@%、app_test@%、app_dev@%
  3. 禁用 Root 遠端:DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1'); FLUSH PRIVILEGES;
  4. 定期審計:每季執行 SHOW GRANTS 檢查權限蔓延
  5. 使用角色(8.0+):CREATE ROLE 'app_read'; GRANT SELECT ON mydb.* TO 'app_read'; GRANT 'app_read' TO 'user1'@'%';

總結

MySQL 權限系統採用「用戶@主機」雙主鍵,掌握 GRANT/REVOKE/DROP USER 三大核心指令,配合 8.0 新特性(角色、密碼策略、caching_sha2_password),即可構建安全、可審計的資料庫存取控制體系。記住:授權謹慎、撤銷精確、刷新及時、審計定期。

copyright © 萬盛學電腦網 all rights reserved