您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 新增用戶權限完整指南：GRANT 語句與授權表直操兩種方式詳解

編輯：MySQL 資料庫相關知識

推薦方式：GRANT 語句

語法簡潔、錯誤較少。前提：在 mysqld 運行的主機上，以 root 身分連線，且 root 擁有 mysql 資料庫的 INSERT 權限與 RELOAD 管理權限。

建立三類示範用戶

-- monty：全權限超級用戶，需密碼，可從任意主機連線
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;

-- admin：本機免密連線，僅具 RELOAD、PROCESS 管理權限
GRANT RELOAD,PROCESS ON *.* TO admin@localhost;

-- dummy：本機免密連線，無任何權限（USAGE 僅佔位）
GRANT USAGE ON *.* TO dummy@localhost;

關鍵點： 必須同時建立 monty@localhost 和 monty@%，否則匿名用戶 @localhost 優先匹配導致無法連線。

替代方式：直接操作授權表

INSERT INTO user VALUES('localhost','monty',PASSWORD('something'), 'Y','Y',...);
INSERT INTO user VALUES('%','monty',PASSWORD('something'), 'Y','Y',...);
INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y';
INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');
FLUSH PRIVILEGES;

注意：MySQL 版本不同 user 表權限欄位數量不同；3.22.11+ 支援 INSERT ... SET 語法。超級用戶僅需 user 表權限全 Y，無需 db/host 表條目。

複雜權限案例：custom 用戶分庫分主機授權

-- GRANT 方式
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO custom@localhost IDENTIFIED BY 'stupid';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO custom@whitehouse.gov IDENTIFIED BY 'stupid';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO custom@'%' IDENTIFIED BY 'stupid';

-- 授權表直操方式
INSERT INTO user (Host,User,Password) VALUES('localhost','custom',PASSWORD('stupid'));
INSERT INTO user (Host,User,Password) VALUES('server.domain','custom',PASSWORD('stupid'));
INSERT INTO user (Host,User,Password) VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
    VALUES ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
    VALUES ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
    VALUES ('%','customer','custom','Y','Y','Y','Y','Y','Y');
FLUSH PRIVILEGES;

通配符主機授權

GRANT ... ON *.* TO myusername@"%.mydomainname.com" IDENTIFIED BY 'mypassword';
-- 或
INSERT INTO user VALUES ('%.mydomainname.com', 'myusername', PASSWORD('mypassword'), ...);
FLUSH PRIVILEGES;