GRANT priv_type [(column_list)]
ON [object_type] priv_level
TO user_or_role [, user_or_role] ...
[WITH GRANT OPTION]
[AS user [WITH ROLE]]
-- 1. 讀寫權限(應用常用)
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';
-- 2. 唯讀權限(報表/備份)
GRANT SELECT ON mydb.* TO 'readonly'@'%';
-- 3. 開發權限(含建表/索引)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON mydb.* TO 'dev'@'%';
-- 4. 管理權限(DBA)
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;
-- 5. 複制權限(從庫同步)
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 6. 監控權限
GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'monitor'@'localhost';
GRANT SELECT (id, name, email), UPDATE (email) ON mydb.users TO 'hr'@'%';
-- 修改他人密碼(需 SUPER 權限)
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass123!';
-- 或
SET PASSWORD FOR 'app'@'%' = 'NewPass123!';
-- 修改自身密碼
SET PASSWORD = 'NewPass123!';
ALTER USER 'app'@'%'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
-- 撤銷特定權限
REVOKE INSERT, UPDATE ON mydb.* FROM 'app'@'%';
-- 撤銷所有權限(含 GRANT OPTION)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';
-- 撤銷角色(8.0+)
REVOKE 'read_only_role' FROM 'app'@'%';
user@host 必須與原 GRANT 完全一致FLUSH PRIVILEGES(8.0 自動刷新)-- 1. 撤銷所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';
-- 2. 刪除用戶
DROP USER 'app'@'%';
-- 3. 刷新(8.0 通常自動)
FLUSH PRIVILEGES;
-- 顯示特定用戶權限
SHOW GRANTS FOR 'app'@'%';
-- 顯示當前用戶權限
SHOW GRANTS;
-- 查看所有用戶
SELECT user, host, plugin, authentication_string FROM mysql.user;
DROP、ALTER、GRANTapp_prod@%、app_test@%、app_dev@%DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1'); FLUSH PRIVILEGES;SHOW GRANTS 檢查權限蔓延CREATE ROLE 'app_read'; GRANT SELECT ON mydb.* TO 'app_read'; GRANT 'app_read' TO 'user1'@'%';MySQL 權限系統採用「用戶@主機」雙主鍵,掌握 GRANT/REVOKE/DROP USER 三大核心指令,配合 8.0 新特性(角色、密碼策略、caching_sha2_password),即可構建安全、可審計的資料庫存取控制體系。記住:授權謹慎、撤銷精確、刷新及時、審計定期。