Database - 管理 MariaDB 用户和存取權限

2023-04-05 2100 words 5 minutes

Contents

權限管理是非常重要的一環，對於資料庫來說都是儲存較敏感且重要的資料，藉由不同的權限授權跟使用者限制存取能更有效且安全的管理。

練習環境

可以使用 HowHow 的創建 Lab 練習環境，來實作 MariaDB 安裝及操作，使用方式請參閱 HowHow 網站 - Linux 練習 Lab 第二版。

1. MariaDB 簡介

MariaDB 是一個開源的關聯式資料庫管理系統，它是 MySQL 的分支版本，提供了更多的功能和性能優化。它支援多種作業系統，包括 Linux、Windows 和 macOS 等，並且能夠與許多不同的程式語言進行互動，例如 PHP、Java 和 Python 等。它還支援多種儲存引擎，包括 InnoDB、MyISAM、Memory 等，使用者可以根據需要進行選擇。 MariaDB 還提供了許多高級功能，例如支援 JSON、GIS 和虛擬列等，這些功能能夠讓使用者更加方便地進行資料管理和查詢。

2. 使用者帳號

創建新使用者須具備下列條件之一 :

是 MariaDB root 帳號
具有 CREATE USER 的特權帳號。
具有 INSERT 特權的帳號。

CREATE USER 語法在 mysql 資料庫中 user 表中創建一個新記錄。該 user 創建時僅有最小的權限。

帳號指定為 user_name@host_name。這樣便可以根據指令來源主機來創建多個名稱相同但特權不同的使用者帳戶。

2.1. 創建使用者帳號

1
2


MariaDB [(none)]> CREATE USER student@localhost IDENTIFIED BY 'redhat';
Query OK, 0 rows affected (0.002 sec)

創建帳號名稱@主機來源。
帳號的密碼。

1
2
3
4
5
6
7


MariaDB [(none)]> SELECT host,user,password FROM mysql.user WHERE user = 'student';
+-----------+---------+-------------------------------------------+
| host      | user    | password                                  |
+-----------+---------+-------------------------------------------+
| localhost | student | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+---------+-------------------------------------------+
1 row in set (0.002 sec)

帳號定義方式

定義	說明
`student`	使用者 `student` 可以從任何主機進行連線。
`student@'%'`	使用者 `student` 可以從任何主機進行連線。
`student@'localhost'`	使用者 `student` 可以從 `localhost` 進行連線。
`student@'192.168.1.5'`	使用者 `student` 只能從來源 IP `192.168.1.5` 進行連線。
`student@'192.168.1.%'`	使用者 `student` 可以從任何屬於 `192.168.1.0/24` 的網段進行連線。
`student@'2001:db8:18:b51:c32:a21'`	使用者 `student` 可以從來源 IP `2001:db8:18:b51:c32:a21` 進行連線。

2.2. 刪除使用者帳號

如果不需要其使用者可以透過 DROP 刪除。

1
2


MariaDB [(none)]> DROP user student@'localhost';
Query OK, 0 rows affected (0.001 sec)

3. 控制使用者特權

預設下，新使用者被授予最小特權。在不授予額外特權的情況下，student 使用者可以查詢最少的資料，大多數其他操作都被拒絕。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


[root@servera ~]# mysql -u student -p
Enter password: redhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.35-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER();
+-------------------+
| USER()            |
+-------------------+
| student@localhost |
+-------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)

權限最小，導致多數操作被拒絕。

1
2
3
4
5


MariaDB [(none)]> USE mysql;
ERROR 1044 (42000): Access denied for user 'student'@'localhost' to database 'mysql'
MariaDB [(none)]> CREATE DATABASE inventory;
ERROR 1044 (42000): Access denied for user 'student'@'localhost' to database 'inventory'
MariaDB [(none)]>

SQL 大小寫

SQL 語法不區分大小寫，通常大寫會表示其為關鍵字。

3.1. 授予使用者權限

創建 inventory 資料庫與 product 資料表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


[root@servera ~]#  mysql -u root -p
Enter password: redhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.35-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE inventory;
Database changed
MariaDB [inventory]> CREATE TABLE product
    -> ( id INT(11) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(100) NOT NULL,
    -> price DOUBLE NOT NULL,
    -> stock INT(11) NOT NULL,
    -> id_category INT(11) NOT NULL,
    -> id_manufacturer INT(11) NOT NULL,
    -> CONSTRAINT id_pk PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected, 1 warning (0.177 sec)

MariaDB [inventory]> INSERT INTO
    -> product (name,price,stock,id_category,id_manufacturer)
    -> VALUES ('RHEL8',100,86,1,1);
Query OK, 1 row affected (0.041 sec)

授權 student 能執行 CRUD 權限在 inventory 資料庫的 product 資料表，並且限制只有來源於 localhost。

1
2
3
4
5
6
7


MariaDB [inventory]> GRANT SELECT, UPDATE, DELETE, INSERT
    -> ON inventory.product
    -> TO student@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [inventory]> exit
Bye

驗證 student 權限。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


[root@servera ~]# mysql -u student -p
Enter password: redhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.35-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE inventory;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [inventory]> SELECT * FROM product;
+----+-------+-------+-------+-------------+-----------------+
| id | name  | price | stock | id_category | id_manufacturer |
+----+-------+-------+-------+-------------+-----------------+
|  1 | RHEL8 |   100 |    86 |           1 |               1 |
+----+-------+-------+-------+-------------+-----------------+
1 row in set (0.001 sec)

GRANT 授權操作

授權	描述
`GRANT SELECT ON database.table TO username@hostname`	向特定使用者授予對特定資料庫中特定表的 SELECT 權限。
`GRANT SELECT ON database.* TO username@hostname`	向特定使用者授予對特定資料庫中所有表的 SELECT 權限。
`GRANT SELECT ON . TO username@hostname`	向特定使用者授予對所有資料庫中所有表的 SELECT 權限。
`GRANT CREATE, ALTER, DROP ON database.* to username@hostname`	向特定使用者授予在特定資料庫中 CREATE、ALTER 和 DROP TABLES 權限。
`GRANT ALL PRIVILEGES ON . to username@hostname`	向特定使用者授予對所有資料庫的所有可用權限，也是創建一個超級使用者（類似於 `root` 使用者）。

3.2. 撤銷使用者權限

1
2
3
4


MariaDB [(none)]> REVOKE SELECT, UPDATE, DELETE, INSERT
    -> ON inventory.product
    -> FROM student@localhost;
Query OK, 0 rows affected (0.001 sec)

刷新權限

建議在所有權限更新後執行 FLUSH PRIVILEGES 指令，雖然授權會立即生效，不過在撤銷權限時大多數需重新載入刷新才會生效。

1
2


MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

3.3. 顯示使用者權限

1
2
3
4
5
6
7
8
9



MariaDB [(none)]> SHOW GRANTS FOR root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

4. 常見故障排除問題

常見問題	排除方式
使用者已被授予從任何主機進行連線的訪問權限，但只能通過從資料庫服務器上的 shell 使用 `mysql` 指令進行連線。	如果在 `/etc/my.cnf.d/mariadb-server.cnf` 中設定了 `skip-networking`，請刪除設定並重新啟動服務。
使用者在 `localhost` 上可以使用任何程序進行連線，但是無法遠程進行連線。	確保 `/etc/my.cnf.d/mariadb-server.cnf` 中的 `bind-address` 配置正確無誤以確保資料庫可以訪問；確保 `user` 表包含使用者嘗試從中進行連線的來源主機中使用者的項目。
使用者可以連線，但是看不到 `information_schema` 以外的任何其他資料庫。	確保已授予使用者存取其資料庫的權限。這是剛創建使用者時遇到的常見問題，因為預設情況下，創建使用者帳戶時提供的是最小權限。
使用者可以連線，但是不能創建任何資料庫。	為使用者授予全域的 `CREATE` 權限。
使用者可以連線，但是不能讀寫任何資料。	為使用者授予要使用的資料庫的 CRUD 權限。

4. 小結

SQL 授權是使得 DB 更安全的管理不二法門，避免任意使用者在任何來源都都能存取或是修改其資料內容。

如果你還沒有註冊 Like Coin，你可以在文章最下方看到 Like 的按鈕，點下去後即可申請帳號，透過申請帳號後可以幫我的文章按下 Like，而 Like 最多可以點五次，而你不用付出任何一塊錢，就能給我寫這篇文章的最大的回饋！