GRANT 通用語法

GRANT priv_type [(column_list)] ON [object_type] priv_level TO user_specification [REQUIRE tls_options] [WITH with_options]

五大角色授權範本

1. 普通數據用戶 (唯讀/讀寫)

-- 唯讀
GRANT SELECT ON biz_db.* TO 'report_user'@'10.0.1.%' IDENTIFIED BY 'pass';

-- 讀寫 (CRUD)
GRANT SELECT, INSERT, UPDATE, DELETE ON biz_db.* TO 'app_user'@'10.0.2.%' IDENTIFIED BY 'pass';

-- 單表欄位級權限 (敏感欄位遮罩)
GRANT SELECT (id, name, email) ON biz_db.users TO 'masked_user'@'%';

2. 開發人員 (DDL + 程式碼物件)

GRANT CREATE, ALTER, DROP, INDEX, CREATE VIEW, SHOW VIEW,
      CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON dev_db.* TO 'dev_user'@'10.0.3.%' IDENTIFIED BY 'pass';

• CREATE ROUTINE：建立 SP/Function
• ALTER ROUTINE：修改/刪除 SP/Function
• EXECUTE：執行 SP/Function

3. 單庫 DBA

GRANT ALL PRIVILEGES ON biz_db.* TO 'dba_biz'@'10.0.0.10' IDENTIFIED BY 'pass';

PRIVILEGES 關鍵字可省略。

4. 超級 DBA (全域)

GRANT ALL ON *.* TO 'dba_root'@'10.0.0.10' IDENTIFIED BY 'pass' WITH GRANT OPTION;

5. 複制專用帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%' IDENTIFIED BY 'pass';

權限作用層次 (5 級)

層次	語法範例	影響範圍
全域	GRANT SELECT ON *.* TO 'u'@'%';	所有資料庫、所有表
資料庫	GRANT SELECT ON db.* TO 'u'@'%';	單一資料庫所有表
資料表	GRANT SELECT,INSERT ON db.tbl TO 'u'@'%';	單一表
欄位	GRANT SELECT (id,name) ON db.tbl TO 'u'@'%';	單一表指定欄位
程序/函數	GRANT EXECUTE ON PROCEDURE db.proc TO 'u'@'%';	單一存儲過程/函數

查看權限

-- 當前用戶
SHOW GRANTS;

-- 指定用戶
SHOW GRANTS FOR 'app_user'@'10.0.2.%';

撤銷權限 (REVOKE)

-- 撤銷特定權限
REVOKE DELETE ON biz_db.* FROM 'app_user'@'%';

-- 撤銷所有權限 (含 GRANT OPTION)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'%';

-- 刪除用戶
DROP USER 'old_user'@'%';

**語法記憶**：GRANT ... TO ... → REVOKE ... FROM ... (TO 換 FROM)

關鍵注意事項

1. 生效時機：權限變更後，**用戶必須重新連線** 才生效 (或執行 FLUSH PRIVILEGES)
2. GRANT OPTION：允許受權者將自己擁有的權限再授予他人，**僅限 DBA 使用**
3. 密碼加密：MySQL 5.7+ 使用 caching_sha2_password，舊版 mysql_native_password，手動操作 mysql.user 表時務必用 PASSWORD() 或 ALTER USER
4. 主機匹配：'user'@'192.168.1.%' (IP 前綴)、'user'@'%.example.com' (域名後綴)、'user'@'localhost' (Unix socket)、'user'@'127.0.0.1' (TCP 本地) — **四者互不相通**
5. 預設資料庫：GRANT ... ON db.* 不設定預設 DB，連線時仍需 USE db 或連線字串指定

MySQL 8.0 新增權限 (動態權限)

• BACKUP_ADMIN、CLONE_ADMIN、CONNECTION_ADMIN、ENCRYPTION_KEY_ADMIN、GROUP_REPLICATION_ADMIN、PERSIST_RO_VARIABLES_ADMIN、RESOURCE_GROUP_ADMIN、ROLE_ADMIN、SERVICE_CONNECTION_ADMIN、SESSION_VARIABLES_ADMIN、SET_USER_ID、SYSTEM_USER、SYSTEM_VARIABLES_ADMIN、TABLE_ENCRYPTION_ADMIN、XA_RECOVER_ADMIN
• 授予：GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'%';