生產環境嚴禁使用 root 直連業務。本文系統整理 MySQL 用戶全生命周期管理命令:創建用戶(兩種方式)、精細授權(庫/表/列/存儲過程層級)、修改密碼、刪除用戶、權限查看兩大方法,並列舉開發、DBA、只讀等典型角色授權模板,助你構建最小權限原則的安全體系。
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'; -- 允許遠程
USE mysql;
INSERT INTO user(Host, User, Password) VALUES('localhost', 'lxh', PASSWORD('123456'));
FLUSH PRIVILEGES;
語法:
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 | 主從復制 |
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;
SHOW GRANTS FOR 'root'@'localhost';
-- 輸出示例:
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
SELECT * FROM mysql.user WHERE User='test' AND Host='127.0.0.1'\G;
-- 可見 Select_priv=Y/N, Insert_priv=Y/N ... 完整權限矩陣
SHOW GRANTS,清理僵尸賬號% 主機通配符,限定具體應用服務器 IPvalidate_password 插件強制密碼複雜度