MySQL 用戶管理與權限分配實操指南

MySQL 用戶增刪改、密碼修改、權限授權標準流程

1. 創建用戶

INSERT INTO mysql.user (Host, User, Password) 
VALUES ('localhost', 'lionbule', PASSWORD('hello1234'));
FLUSH PRIVILEGES;

MySQL 5.7+ 推薦：CREATE USER 'lionbule'@'localhost' IDENTIFIED BY 'hello1234';

2. 修改密碼

UPDATE mysql.user SET Password = PASSWORD('newpass') 
WHERE User = 'lionbule' AND Host = 'localhost';
FLUSH PRIVILEGES;

5.7+：ALTER USER 'lionbule'@'localhost' IDENTIFIED BY 'newpass';

3. 刪除用戶

DELETE FROM mysql.user WHERE User = 'lionbule' AND Host = 'localhost';
FLUSH PRIVILEGES;

5.7+：DROP USER 'lionbule'@'localhost';

4. GRANT 權限授權語法

GRANT 權限 ON 數據庫.表 TO '用戶'@'主機' IDENTIFIED BY '密碼';

權限關鍵字

對象範圍

• *.*：所有庫所有表
• test.*：test 庫所有表
• test.t1：單表

主機匹配

• %：任意 IP
• localhost：本機 socket
• 192.168.10.2：指定 IP

實例

-- lionbule 對 test 庫全權限，不限 IP，密碼 hello1234
GRANT ALL ON test.* TO 'lionbule'@'%' IDENTIFIED BY 'hello1234';
FLUSH PRIVILEGES;

注意事項

• GRANT 會隱式創建用戶（舊版），5.7+ 需先 CREATE USER
• FLUSH PRIVILEGES 僅在直接操作 mysql.user 表時需要
• 權限修改後，**用戶需重新連接生效**