SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'app_user'@'192.168.1.%';
SHOW GRANTS;
SELECT CONCAT('User: ''', User, '''@''', Host, ''';') AS query FROM mysql.user;
SELECT * FROM mysql.user WHERE User='app_user'\G
輸出含 Select_priv, Insert_priv, Update_priv... 等 Y/N 欄位。
| 類別 | 權限關鍵字 | 說明 |
|---|---|---|
| 資料操作 | SELECT, INSERT, UPDATE, DELETE | DML 基礎權限 |
| 結構變更 | CREATE, ALTER, DROP, INDEX | DDL 權限 |
| 表進階 | CREATE TEMPORARY TABLES, LOCK TABLES, REFERENCES | 臨時表、鎖表、外鍵 |
| 視圖 | CREATE VIEW, SHOW VIEW | 視圖建立/查看定義 |
| 存儲程式 | CREATE ROUTINE, ALTER ROUTINE, EXECUTE | Stored Procedure/Function |
| 管理 | PROCESS, SUPER, RELOAD, SHUTDOWN, REPLICATION SLAVE/CLIENT | 高危,僅 DBA |
| 全部 | ALL PRIVILEGES | 除 GRANT OPTION 外所有 |
-- 庫級唯讀
GRANT SELECT ON mydb.* TO 'readonly'@'192.168.1.%';
-- 表級讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.orders TO 'app'@'10.0.0.%';
-- 開發者結構權限
GRANT CREATE, ALTER, DROP, INDEX, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON devdb.* TO 'dev'@'192.168.0.%';
-- 外鍵權限
GRANT REFERENCES ON mydb.* TO 'dev'@'192.168.0.%';
-- 所有權限(慎用)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
-- 生效
FLUSH PRIVILEGES;
REVOKE INSERT, UPDATE ON mydb.* FROM 'readonly'@'192.168.1.%';
REVOKE ALL PRIVILEGES ON *.* FROM 'old_dev'@'%';
FLUSH PRIVILEGES;
CREATE USER 'newapp'@'10.0.0.%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'newapp'@'10.0.0.%';
%,使用具體 IP/網段pt-show-grants (Percona Toolkit) 導出所有權限審計