萬盛學電腦網

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

MySQL 用戶管理權威指南:建立、授權、改密、撤銷、刪除、最小權限實踐

用戶與權限體系基礎

  • 用戶標識:'username'@'host'(host 支援 % 通配、IP、主機名)。
  • 權限層級:全域 *.* → 庫級 db.* → 表級 db.table → 欄位級。
  • 權限表:mysql.user、db、tables_priv、columns_priv、procs_priv、proxies_priv。

建立用戶

MySQL 5.7 / 8.0 標準語法

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123!';
-- 或指定認證插件(8.0 預設 caching_sha2_password)
CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass456!';

舊版 INSERT 方式(不推薦,5.7+ 可能報錯)

INSERT INTO mysql.user (Host, User, authentication_string) 
VALUES ('localhost', 'old_user', PASSWORD('pass'));
FLUSH PRIVILEGES;

授權 (GRANT)

常用權限代碼

權限說明
ALL PRIVILEGES除 GRANT OPTION 外所有權限
SELECT, INSERT, UPDATE, DELETE基礎 DML
CREATE, DROP, ALTER, INDEXDDL
EXECUTE執行存儲過程/函數
RELOAD, SHUTDOWN, PROCESS管理權限
REPLICATION SLAVE/CLIENT複寫相關
GRANT OPTION允許將自身權限授予他人

授權範例

-- 庫級唯讀
GRANT SELECT ON app_db.* TO 'readonly'@'192.168.1.%';

-- 表級讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.orders TO 'order_svc'@'10.0.0.%';

-- 欄位級敏感資料保護
GRANT SELECT (id, name, email) ON app_db.users TO 'api_user'@'%';

-- 開發帳號:特定庫全權限,含建表、索引、視圖
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW 
  ON dev_db.* TO 'dev'@'192.168.1.%';

-- 超級管理員(謹慎授予)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.1.100' WITH GRANT OPTION;

FLUSH PRIVILEGES;

修改密碼

MySQL 5.7.6+ / 8.0

ALTER USER 'user'@'host' IDENTIFIED BY 'NewPass123!';
-- 或
SET PASSWORD FOR 'user'@'host' = 'NewPass123!';

MySQL 5.7.5 及以下

SET PASSWORD FOR 'user'@'host' = PASSWORD('NewPass123!');
-- 或
UPDATE mysql.user SET authentication_string=PASSWORD('NewPass123!') WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;

mysqladmin 命令列

mysqladmin -u user -p'oldpass' password 'newpass'

撤銷權限 (REVOKE)

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

⚠️ REVOKE 需精確匹配原 GRANT 的層級(庫/表/欄位),否則不生效。用 SHOW GRANTS FOR 'user'@'host'; 查看當前權限再精確撤銷。

刪除用戶

DROP USER 'user'@'host';
-- 或舊版
DELETE FROM mysql.user WHERE User='user' AND Host='host';
FLUSH PRIVILEGES;

查看權限

SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS;  -- 當前用戶

最小權限實踐原則

  1. 業務帳號只授 DML(SELECT/INSERT/UPDATE/DELETE),無 DDL、無管理權限。
  2. 讀寫分離帳號分離:寫帳號有 INSERT/UPDATE/DELETE,讀帳號僅 SELECT。
  3. 敏感欄位欄位級授權:GRANT SELECT (id, name) ON db.users TO 'api'@'%'; 避免密碼雜湊、身份證等洩露。
  4. 禁止 % 主機:生產環境僅授具體 IP/網段。
  5. 定期審計:SELECT User, Host FROM mysql.user WHERE Host='%'; 掃描通配主機帳號。
  6. 密碼輪換:配合 validate_password 強制複雜度,定期 ALTER USER ... PASSWORD EXPIRE。

MySQL 8.0 新特性:角色

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

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

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

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

角色簡化大量用戶權限管理,變更權限只需改角色,所有關聯用戶自動生效。

總結

MySQL 用戶管理核心:**CREATE USER 建立身份 → GRANT 最小權限 → 定期審計撤銷 → 角色簡化批量管理**。牢記 FLUSH PRIVILEGES 讓修改即時生效,生產環境嚴禁 root@%、嚴禁弱密碼、嚴禁過度授權。

copyright © 萬盛學電腦網 all rights reserved