[Website] 管理 MySQL 權限

Posted on by
Post Views: 1,823

在管理 MySQL 的時候需要慎重地管理權限，一般來說管理與查看 MySQL 資料庫有三個辦法：

  1. 使用 root 從 localhost 也就是從伺服器端登入
  2. 使用 phpmyadmin 登入管理
  3. 使用其他資料庫軟體 (例如 SQLDeveloper) 登入

這三個管理辦法卻需要相對應的權限，本篇想要總結一些有用的 MySQL 指令充分管理 MySQL 的權限，方法一通常在資料庫生成的時候就會啟動。

查看資料庫使用者列表

首先我們先利用以下指令查看所有的在 mysql 裡面的使用者他們與他們的 host.

SELECT user,host FROM mysql.user;

從得到的資訊我們知道目前，root 只能從 localhost 的 host 登入

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| postfix          | localhost |
| root             | localhost |
| user             | localhost |
+------------------+-----------+
2 rows in set (0.00 sec)

但是此時如果使用 phpmyadmin 與 root 登入的時候卻會發生以下的情況，其中有錯誤訊息 #1045 – Access denied for user ‘root’@’localhost’ (using password: YES) 的錯誤訊息，但是使用 user 帳號卻可以登入，目前為止還不知道要怎麼給予 root 從 phpmyadmin 登入的權限。

新增使用者

為了啟動遠端登入，我們需要輸入以下指令。

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| user             | %         |
| postfix          | localhost |
| root             | localhost |
| user             | localhost |
+------------------+-----------+
3 rows in set (0.00 sec)
查看資料庫使用者權限

在利用 mysql 指令登入之後，我們可以利用以下的指令查看權限：

SHOW GRANTS for user@localhost;
SHOW GRANTS for user@'%';

結果顯示從 localhost 端使用 root 登入可以享有 ALL PRIVILEGES 的權限

+---------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost                                                                                     |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*****************************************'   |
| GRANT ALL PRIVILEGES ON `example\_%`.* TO 'user'@'localhost'                                                  |
+---------------------------------------------------------------------------------------------------------------+

+-----------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
+-----------------------------------------------------------------------------------------------------+
新增使用者權限

下列的指令可以給予 user 使用者從任何地方登入 (%) 查看或是修改 example_ 開頭的所有資料庫。

GRANT ALL PRIVILEGES ON `example\_%`.* TO 'user'@'%';

新增完此一個權限之後，我們再重新查看使用者權限會發現以下資訊：

+-------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT ALL PRIVILEGES ON `example\_%`.* TO 'user'@'%'                                               |
+-------------------------------------------------------------------------------------------------------+
刪除使用者權限

如果想要刪除權限的時候則是使用以下指令，執行之後 user 的權限回復到最初的樣貌！

REVOKE ALL ON `example\_%`.* from 'user'@'%';