萬盛學電腦網

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

MySQL 用戶管理全攻略:創建、授權、修改密碼、刪除、權限查看

生產環境嚴禁使用 root 直連業務。本文系統整理 MySQL 用戶全生命周期管理命令:創建用戶(兩種方式)、精細授權(庫/表/列/存儲過程層級)、修改密碼、刪除用戶、權限查看兩大方法,並列舉開發、DBA、只讀等典型角色授權模板,助你構建最小權限原則的安全體系。

一、用戶創建

方式 1:CREATE USER(推薦,8.0+ 必須)

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

參數:

  • host:localhost(本地)、%(任意遠程)、192.168.1.%(特定網段)、10.0.0.5(單 IP)
  • password:為空則免密登錄(極不推薦)

示例:

CREATE USER 'lxh'@'localhost' IDENTIFIED BY '123456';  -- 僅本地
CREATE USER 'lxh'@'%' IDENTIFIED BY '123456';          -- 允許遠程

方式 2:直接操作 mysql.user 表(舊版兼容,不推薦)

USE mysql;
INSERT INTO user(Host, User, Password) VALUES('localhost', 'lxh', PASSWORD('123456'));
FLUSH PRIVILEGES;

二、用戶授權(GRANT)

語法:

GRANT privileges ON db.table TO 'user'@'host' [WITH GRANT OPTION];

常用權限關鍵字

權限說明
ALL PRIVILEGES除 GRANT OPTION 外所有權限
SELECT / INSERT / UPDATE / DELETE增刪改查基礎權限
CREATE / ALTER / DROP表結構變更
INDEX索引操作
REFERENCES外鍵
CREATE VIEW / SHOW VIEW視圖
CREATE ROUTINE / ALTER ROUTINE / EXECUTE存儲過程/函數
REPLICATION SLAVE / CLIENT主從復制

典型角色授權模板

1. 只讀業務賬號
GRANT SELECT ON testdb.* TO 'readonly'@'%';
2. 普通寫入賬號(增刪改查)
GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'app_user'@'%';
3. 開發人員(DDL + DML + 存儲過程)
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES,
      CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE
  ON testdb.* TO 'dev'@'192.168.0.%';
4. 庫級 DBA
GRANT ALL PRIVILEGES ON testdb.* TO 'dba'@'localhost';
5. 超級 DBA(全實例)
GRANT ALL ON *.* TO 'dba'@'localhost';
6. 列級權限(敏感字段隔離)
GRANT SELECT(id, name, email) ON testdb.users TO 'report'@'%';
7. 存儲過程/函數單獨授權
GRANT EXECUTE ON PROCEDURE testdb.proc_name TO 'user'@'%';
GRANT EXECUTE ON FUNCTION testdb.func_name TO 'user'@'%';

⚠️ 必須執行

FLUSH PRIVILEGES;

三、修改密碼

-- 方式 1:GRANT 重新授權時指定
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost' IDENTIFIED BY 'newpwd';

-- 方式 2:直接更新 user 表(5.7+ authentication_string 欄位)
UPDATE mysql.user SET authentication_string=PASSWORD('newpwd') 
  WHERE User='lxh' AND Host='localhost';
FLUSH PRIVILEGES;

-- 方式 3:5.7+ 官方推薦
ALTER USER 'lxh'@'localhost' IDENTIFIED BY 'newpwd';

四、刪除用戶

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

五、權限查看

方法 1:SHOW GRANTS(推薦,直觀)

SHOW GRANTS FOR 'root'@'localhost';
-- 輸出示例:
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

方法 2:查 mysql.user 表(詳細矩陣)

SELECT * FROM mysql.user WHERE User='test' AND Host='127.0.0.1'\G;
-- 可見 Select_priv=Y/N, Insert_priv=Y/N ... 完整權限矩陣

六、最佳實踐建議

  • 嚴格遵循最小權限原則:業務賬號僅授必要庫/表/列
  • 按環境隔離賬號:dev/test/prod 互不相通
  • 定期審計 SHOW GRANTS,清理僵尸賬號
  • 生產庫禁止 % 主機通配符,限定具體應用服務器 IP
  • 啟用 validate_password 插件強制密碼複雜度
copyright © 萬盛學電腦網 all rights reserved