使用者與權限｜SQL 資料庫

Learning Objectives

說明 MySQL 帳號由「使用者名稱＋連線來源主機」組成，寫成 'fred'@'localhost'
用 CREATE USER、ALTER USER、RENAME USER、DROP USER 管理帳號，並說出 MySQL 8.0 移除的舊寫法
用 GRANT 在全域、資料庫、表格、欄位四個層級授權，知道各層級記在 mysql 資料庫的哪張表格
用 REVOKE 收回權限、用 SHOW GRANTS 檢查帳號目前的權限，說明 WITH GRANT OPTION 的作用
看懂權限不足時的錯誤：1142、1143、1044、1227

每個程式和使用者各用自己的帳號

到目前為止，我們都用 root 操作資料庫。root 擁有所有權限：可以刪掉任何資料庫、建立帳號、關閉伺服器。實際的系統不會讓每個人、每支程式都用 root，原因很實際：

網站程式只需要讀寫自己的資料庫。萬一程式有漏洞被攻擊，攻擊者能做的事以這個帳號的權限為限
會計同仁只需要查詢報表，不應該能修改員工資料
每個人用自己的帳號，事後才查得出某個操作是誰做的

所以資料庫管理員的日常工作之一，就是建立帳號，並且只給每個帳號完成工作所需的權限。

MySQL 的帳號由兩部分組成：使用者名稱與可以從哪台主機連線，寫成 '使用者'@'主機'：

寫法	意思
`'fred'@'localhost'`	fred，只能從資料庫伺服器本機連線
`'fred'@'192.168.1.%'`	fred，可以從 192.168.1.0～192.168.1.255 這個網段連線（`%` 是萬用字元）
`'fred'@'%'`	fred，可以從任何主機連線
`'fred'`	省略主機，等於 `'fred'@'%'`

'fred'@'localhost' 和 'fred'@'%' 是兩個不同的帳號，可以有不同的密碼與權限。名稱和主機各自用引號括起來；寫成 'fred@localhost' 會變成一個名叫 fred@localhost、可以從任何主機連線的帳號。

帳號與權限都存在 mysql 這個系統資料庫裡，本課後面會看到它們實際記在哪些表格。

建立、修改與刪除帳號

CREATE USER 'fred'@'localhost' IDENTIFIED BY 'Fr3d!2026';    -- 建立帳號並設定密碼
ALTER USER 'fred'@'localhost' IDENTIFIED BY 'N3w!2026';      -- 修改密碼
RENAME USER 'fred'@'localhost' TO 'frederick'@'localhost';  -- 改名（權限跟著帳號走）
DROP USER 'frederick'@'localhost';                          -- 刪除帳號（連同它的權限）
SHOW GRANTS FOR 'fred'@'localhost';                         -- 列出這個帳號的權限

剛建立的帳號只有 USAGE，意思是「可以登入，但什麼都不能做」，要再用 GRANT 授權
帳號已經存在時 CREATE USER 會出現 ERROR 1396；可以寫 CREATE USER IF NOT EXISTS、DROP USER IF EXISTS 避開
MySQL 8.0 預設用 caching_sha2_password 驗證密碼，資料庫只存密碼雜湊後的結果，看不到原本的密碼

本課的投影片是 MySQL 5 時代的寫法，其中兩種在 MySQL 8.0 已經移除：

舊寫法（MySQL 5.x）	MySQL 8.0 的寫法
`GRANT … TO fred@localhost IDENTIFIED BY 'password';`（授權時順便建立帳號）	先 `CREATE USER … IDENTIFIED BY '…';`，再 `GRANT …;`
`SET PASSWORD FOR fred@localhost = PASSWORD('newpassword');`	`ALTER USER 'fred'@'localhost' IDENTIFIED BY '…';` 或 `SET PASSWORD FOR 'fred'@'localhost' = '…';`

PASSWORD() 函式已經移除，GRANT 也不能再設定密碼，兩種舊寫法都會出現 ERROR 1064 語法錯誤。對不存在的帳號 GRANT，MySQL 8.0 也不會自動建立帳號，而是出現 ERROR 1410 (42000): You are not allowed to create a user with GRANT。

帳號的一生：建立、改密碼、改名、刪除✓ Watched

這個範例以 root 身分管理 fred 這個帳號。注意第 9 行的舊寫法得到 ERROR 1064，最後一行刪除已經不存在的帳號得到 ERROR 1396。查詢 mysql.user 時，mysql. 開頭的三個帳號是 MySQL 內部使用的系統帳號，已經鎖定、無法登入。（模擬環境的 root 用密碼驗證；Ubuntu 套件安裝的 root 預設用 auth_socket，只能在伺服器上以 sudo mysql 登入，第 15 課會再說明。）

GRANT 的四個層級

GRANT 的基本格式是「把哪些權限、在什麼範圍上，給哪個帳號」：

GRANT 權限1, 權限2 ON 範圍 TO '使用者'@'主機';

ON 後面的範圍決定層級，每個層級記在 mysql 資料庫的不同表格：

層級	範圍寫法	例子	記錄在
全域	`.`	`GRANT SELECT ON . TO …`：所有資料庫的所有表格	`mysql.user`
資料庫	`資料庫.*`	`GRANT SELECT, INSERT ON employee.* TO …`	`mysql.db`
表格	`資料庫.表格`	`GRANT SELECT ON employee.department TO …`	`mysql.tables_priv`
欄位	`權限 (欄位, …)`	`GRANT SELECT (employeeID, name) ON employee.employee TO …`	`mysql.columns_priv`

常用的權限：

權限	可以做的事	可用的層級
`SELECT`、`INSERT`、`UPDATE`	查詢、新增、修改資料列	全域、資料庫、表格、欄位
`DELETE`	刪除資料列	全域、資料庫、表格
`CREATE`、`DROP`、`ALTER`、`INDEX`	建立、刪除、修改表格與索引	全域、資料庫、表格
`CREATE VIEW`、`EXECUTE`、`TRIGGER`	建立檢視表、執行預存程序、建立觸發程序	全域、資料庫（部分可到表格）
`CREATE USER`	建立、改名、刪除帳號	只有全域
`FILE`	讀寫伺服器上的檔案（`INTO OUTFILE`、`LOAD DATA INFILE`）	只有全域
`PROCESS`	查看所有連線正在執行的查詢	只有全域
`RELOAD`、`SHUTDOWN`	`FLUSH` 系列指令、關閉伺服器	只有全域
`ALL PRIVILEGES`（可簡寫 `ALL`）	該層級的全部權限，不含 `GRANT OPTION`	全域、資料庫、表格
`USAGE`	沒有任何權限，只能登入

只能在全域層級授權的權限（FILE、PROCESS、CREATE USER 等），寫成 ON employee.* 會出現 ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES。舊版的 SUPER 在 8.0 仍可使用但已不建議，改拆成 SYSTEM_VARIABLES_ADMIN、CONNECTION_ADMIN 等較細的「動態權限」。

GRANT、REVOKE、CREATE USER 執行後立即生效，不需要 FLUSH PRIVILEGES。 FLUSH PRIVILEGES 只在你直接用 INSERT／UPDATE／DELETE 修改 mysql.user 這些表格時才需要，而 8.0 不建議這樣做。

表格層級與欄位層級的授權記在哪裡✓ Watched

這個範例只讓 fred 查詢 department 整張表格，以及 employee 的 employeeID、name 兩個欄位（看不到 job 與 departmentID）。最後兩個查詢直接讀 mysql.tables_priv 與 mysql.columns_priv，看授權實際寫進哪裡：欄位層級的授權在 tables_priv 也有一列，Column_priv 標記這張表格有欄位權限，詳細欄位記在 columns_priv。

換成 fred 登入試試看

授權有沒有設對，最可靠的方法是用那個帳號實際登入試試看。真實環境要先離開目前的連線，再用新帳號登入：

mysql> exit
$ mysql -u fred -p
Enter password: ********

-p 後面不寫密碼，mysql 會提示你輸入，密碼就不會留在終端機的指令紀錄裡。

在本課的範例與練習裡，獨立一行的 -- @user fred@localhost 代表「改用 fred 登入」，-- @user root@localhost 代表回到 root 原本的連線。就像真的重新登入一樣，fred 一開始沒有選擇資料庫，要自己 USE。

權限不足時常見的錯誤：

錯誤	情況
`ERROR 1142 (42000): SELECT command denied to user … for table '…'`	對這張表格沒有這個權限
`ERROR 1143 (42000): SELECT command denied to user … for column '…' in table '…'`	有這張表格的部分欄位權限，但用到了沒有權限的欄位
`ERROR 1044 (42000): Access denied for user … to database '…'`	對整個資料庫沒有權限，例如 `USE mysql`
`ERROR 1227 (42000): Access denied; you need (at least one of) the … privilege(s) for this operation`	缺少全域層級的管理權限，例如 `CREATE USER`、`FILE`

fred 能做什麼、不能做什麼✓ Watched

執行前，root 已經建好 fred，並且授權 SELECT ON employee.department 與 SELECT (employeeID, name) ON employee.employee（就是上一個範例）。第 1 行起改用 fred 登入。逐步觀察哪些敘述成功、哪些被擋下，以及錯誤代碼的差別。

想一想：授權的層級✓ 已答對

1.

fred 只有 GRANT SELECT (employeeID, name) ON employee.employee 這項權限。fred 執行下面的查詢，結果是什麼？

SELECT COUNT(*) FROM employee.employee;

4

ERROR 1142 (42000): SELECT command denied to user 'fred'@'localhost' for table 'employee'

ERROR 1143 (42000): SELECT command denied to user 'fred'@'localhost' for column 'job' in table 'employee'

0

2.

想讓 fred 可以用 SELECT … INTO OUTFILE 匯出 employee 資料庫，root 執行下面的授權，結果是什麼？

GRANT FILE ON employee.* TO 'fred'@'localhost';

Query OK, 0 rows affected (0.00 sec)

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'employee'

ERROR 1410 (42000): You are not allowed to create a user with GRANT

3.

GRANT SELECT, INSERT ON employee.* TO 'fred'@'localhost'; 這項授權記錄在 mysql 資料庫的哪張表格？

mysql.user

mysql.db

mysql.tables_priv

mysql.columns_priv

第 1 次作答

REVOKE 與 WITH GRANT OPTION

REVOKE 收回權限，寫法和 GRANT 對稱，TO 換成 FROM：

REVOKE SELECT ON employee.department FROM 'fred'@'localhost';
REVOKE ALL ON employee.* FROM 'fred'@'localhost';

REVOKE 要對準授權時的層級。 授權時寫 ON employee.department（表格層級），收回時寫 ON employee.*（資料庫層級）不會收回它，而是出現 ERROR 1141 (42000): There is no such grant defined for user 'fred' on host 'localhost'，因為資料庫層級本來就沒有授權。不確定時先 SHOW GRANTS FOR，照著列出來的範圍收回。

WITH GRANT OPTION 讓對方可以把自己擁有的權限再轉授給別人：

GRANT SELECT ON employee.* TO 'fred'@'localhost' WITH GRANT OPTION;

fred 之後可以把 employee.* 的 SELECT 授權給其他帳號，但不能授權他自己沒有的權限（例如 INSERT）。權限一旦可以轉授，就很難追蹤最後落到誰手上，所以只給真正負責管理的人。

ALL PRIVILEGES 不包含 GRANT OPTION，所以 REVOKE ALL ON employee.* 之後，fred 仍然保有轉授的能力，要另外 REVOKE GRANT OPTION；帳號不再使用時，直接 DROP USER 最乾淨。

轉授權限與收回✓ Watched

執行前已經建立 fred 與 amy 兩個沒有權限的帳號。root 給 fred 可以轉授的 SELECT，fred 轉給 amy；接著 root 收回 fred 的權限，觀察 REVOKE ALL 之後還剩下什麼。mysql.db 那一步可以看到兩個帳號在資料庫層級的 Y／N 欄位。

想一想：建立帳號與收回權限✓ 已答對

1.

資料庫裡沒有 amy 這個帳號。root 執行下面的敘述，結果是什麼？

GRANT SELECT ON employee.department TO 'amy'@'localhost';

Query OK, 0 rows affected (0.00 sec)，同時建立了 amy

ERROR 1410 (42000): You are not allowed to create a user with GRANT

ERROR 1396 (HY000): Operation GRANT failed for 'amy'@'localhost'

ERROR 1064 (42000) 語法錯誤

2.

fred 的權限只有 GRANT SELECT ON employee.department。root 執行下面的敘述，結果是什麼？

REVOKE SELECT ON employee.* FROM 'fred'@'localhost';

Query OK, 0 rows affected (0.00 sec)，fred 不能再查詢 department

Query OK, 0 rows affected (0.00 sec)，但 fred 仍然可以查詢 department

ERROR 1141 (42000): There is no such grant defined for user 'fred' on host 'localhost'

ERROR 1142 (42000): REVOKE command denied to user 'root'@'localhost' for table 'department'

3.

fred 擁有 GRANT SELECT ON employee.* … WITH GRANT OPTION。fred 登入後執行下面的敘述（amy 已經存在），結果是什麼？

GRANT INSERT ON employee.* TO 'amy'@'localhost';

Query OK, 0 rows affected (0.00 sec)

ERROR 1044 (42000): Access denied for user 'fred'@'localhost' to database 'employee'

ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation

ERROR 1410 (42000): You are not allowed to create a user with GRANT

第 1 次作答

動手寫：替報表程式建立帳號✓ 已通過

公司的報表程式要從本機連線，讀取部門資料，以及員工的編號、姓名與職稱，但不能看到其他欄位、也不能修改。請以 root 身分：

建立帳號 'report'@'localhost'，密碼 R3port!2026
授權查詢 employee.department 整張表格
授權查詢 employee.employee 的 employeeID、job、name 三個欄位（請照這個順序寫；真實 MySQL 的 SHOW GRANTS 會依欄位名稱排序）
用 SHOW GRANTS 確認結果

你的輸出

預期輸出

重點整理

指令	作用
`CREATE USER 'u'@'h' IDENTIFIED BY '…'`	建立帳號（只有 USAGE）
`ALTER USER 'u'@'h' IDENTIFIED BY '…'`	修改密碼
`RENAME USER 'u'@'h' TO 'v'@'h'`	改名，權限跟著走
`DROP USER 'u'@'h'`	刪除帳號與它的權限
`GRANT 權限 ON 範圍 TO 'u'@'h' [WITH GRANT OPTION]`	授權
`REVOKE 權限 ON 範圍 FROM 'u'@'h'`	收回（範圍要和授權時相同）
`SHOW GRANTS FOR 'u'@'h'`	列出帳號的權限

層級	範圍	記錄在
全域	`.`	`mysql.user`
資料庫	`db.*`	`mysql.db`
表格	`db.tbl`	`mysql.tables_priv`
欄位	`SELECT (欄位, …) ON db.tbl`	`mysql.columns_priv`

帳號＝使用者名稱＋主機，'fred'@'localhost' 和 'fred'@'%' 是不同的帳號
MySQL 8.0：GRANT 不能建立帳號、不能設定密碼；PASSWORD() 函式已移除
GRANT／REVOKE 立即生效，不需要 FLUSH PRIVILEGES
錯誤代碼：1142 表格、1143 欄位、1044 資料庫、1227 全域管理權限

下一課看 MySQL 伺服器本身的設定：設定檔與系統變數。

--

1

--

--

32.3%

140.05

82.02%

62,201

AI Reply Desktop Notifications

Chat Message Notifications

Sound notification

More settings

使用者與權限

Learning Objectives

每個程式和使用者各用自己的帳號

建立、修改與刪除帳號

帳號的一生：建立、改密碼、改名、刪除✓ Watched

GRANT 的四個層級

表格層級與欄位層級的授權記在哪裡✓ Watched

fred 能做什麼、不能做什麼✓ Watched

想一想：授權的層級✓ 已答對

REVOKE 與 WITH GRANT OPTION

轉授權限與收回✓ Watched

想一想：建立帳號與收回權限✓ 已答對

動手寫：替報表程式建立帳號✓ 已通過

重點整理

uedu.tw

uedu.tw/fit