MariaDB 使用者存取 DateBase (Table) 權限

Loading

測試環境為 CentOS 7 x86_64

使用者的權限可以區分為

  1. 登入權限,請參考 https://benjr.tw/97973
  2. 使用者存取 資料庫(DateBase)/資料表(Table) 權限,下面來討論.
  3. 使用者自訂,使用 SQL 函數的權限,請參考 https://benjr.tw/99060

權限有分為 管理權限 結構權限 與 資料權限

  • 管理權限
    GRANT , SUPER , PROCESS , RELOAD , SHUTDOWN , SHOW DATABASES , LOCK TABLES , REFERENCES , REPLICATION CLIENT , REPLICATION SLAVE , CREATE USER
  • 結構權限
    CREATE , ALTER , INDEX , DROP , CREATE TEMPORARY TABLES , SHOW VIEW , CREATE ROUTINE , ALTER ROUTINE , EXECUTE , CREATE VIEW , EVENT , TIGGER
  • 資料權限
    SELECT , INSERT , UPDATE , DELETE , FILE

這邊先來看一下資料庫 SELECT , INSERT , UPDATE , DELETE 與 FILE 的權限,先來建立要測試用的資料庫與不同的使用者.

[root@localhost ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 67
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> CREATE DATABASE user1Data;
Query OK, 1 row affected (0.00 sec)

user1 只能透過本機 localhost 登入,user2 可以從任意處登入 (% 表示任意).

MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER user2@'%' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT host,user FROM mysql.user;
+-----------------------+-------+
| host                  | user  |
+-----------------------+-------+
| %                     | user2 |
| 127.0.0.1             | root  |
| ::1                   | root  |
| localhost             | root  |
| localhost             | user1 |
| localhost.localdomain | root  |
+-----------------------+-------+
7 rows in set (0.000 sec)

GRANT

user1 給予 資料庫 (user1Date ) 的全部權限 (ALL PRIVILEGES) , user2 只有讀取 (SELECT) 資料庫 (user1Data) 與 FILE(全域) 的權限.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON user1Data.* TO user1@localhost;
Query OK, 0 rows affected (0.01 sec)

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

MariaDB [(none)]> SHOW GRANTS FOR user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT ALL PRIVILEGES ON `user1Data`.* TO 'user1'@'localhost'                                                     |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> GRANT SELECT ON user1Data.* TO user2@'%';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT FILE ON *.* TO user2@'%';
Query OK, 0 rows affected (0.001 sec)

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

MariaDB [(none)]> SHOW GRANTS FOR user2@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for user2@%                                                                                  |
+-----------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT ON `user1Data`.* TO 'user2'@'%'                                                        |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> EXIT;
Bye

指令說明 :
剛建立建立帳號與給予權限的指令是分開下的,其實是可以同的,如下所示:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON user1Data.* TO user1@localhost IDENTIFIED BY ‘your.password’ ;
MariaDB [(none)]> SHOW GRANTS FOR user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT ALL PRIVILEGES ON `user1Data`.* TO 'user1'@'localhost'                                                     |
+--------------------------------------------------------------------------------------------------------------+
  • GRANT USAGE
    第一行代表 Global Level 的權限,user1 的 USAGE 代表沒權限 ( no privileges ),而剛剛的 user2 則是有 FILE 權限.
  • GRANT ALL PRIVILEGES
    包含 資料 ( SELECT , INSERT , UPDATE , DELETE ) , 結構 ( CREATE , ALTER , INDEX , DROP , CREATE TEMPORARY TABLES , SHOW VIEW , CREATE ROUTINE , ALTER ROUTINE , EXECUTE , CREATE VIEW , EVENT , TRIGGER ) ,管理 ( LOCK TABLES , REFERENCES ) 的權限.

    可以設定的權限包含 ALL [PRIVILEGES] , ALTER, ALTER ROUTINE , CREATE , CREATE ROUTINE , CREATE TABLESPACE , CREATE TEMPORARY TABLES , CREATE USER , CREATE VIEW , DELETE , DROP , EVENT , EXECUTE , FILE , GRANT OPTION , INDEX , INSERT , LOCK TABLES , PROCESS , PROXY , REFERENCES , RELOAD , REPLICATION CLIENT , REPLICATION SLAVE , SELECT , SHOW VIEW , SHUTDOWN , SUPER , TRIGGER, UPDATE , USAGE

    下面會針對 SELECT , INSERT , UPDATE , DELETE 與 FILE 的權限來說明,其他權限請參考 https://dev.mysql.com/doc/refman/5.7/en/grant.html 說明

  • user1.*
    指定可以存取哪些 Db_name/Table
  • user1@localhost
    格式 : username@localhost
    username – 新增的使用者名稱
    localhost 限制可從哪裡來存取, localhost 代表只能從本機登入,172.16.15.% 代表可以從 172.16.15.* 網段登入.如果要任意處登入可以使用 user@’%’ (% 表示任意).
  • identified by ‘your.password’
    設定使用者密碼 ( password ) .

下面我們就登入個別的使用者來測試權限.
User1
測試 user1 對於 user1Data 資料庫存取權限.

[root@localhost ~]$ mysql -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

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

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| user1Data          |
+--------------------+
2 rows in set (0.00 sec)

測試一下 User1 等權限.

MariaDB [(none)]> USE user1Data;
Database changed
MariaDB [user1Data]> CREATE TABLE employee (Name CHAR(20),Dept CHAR(20),jobTitle CHAR(20));
Query OK, 0 rows affected (0.03 sec)

MariaDB [user1Data]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| jobTitle | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • INSERT
    MariaDB [user1Data]> INSERT INTO employee VALUES ('Ben','Testing','Engineer'), ('Afa','Power','Engineer');
    Query OK, 2 rows affected (0.003 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
  • SELECT
    MariaDB [user1Data]> SELECT * FROM employee;
    +------+---------+----------+
    | Name | Dept    | jobTitle |
    +------+---------+----------+
    | Ben  | Testing | Engineer |
    | Afa  | Power   | Engineer |
    +------+---------+----------+
    2 rows in set (0.00 sec)
    
  • DELETE
    MariaDB [user1Data]> DELETE FROM employee WHERE Name LIKE 'Ben';
    Query OK, 1 row affected (0.003 sec)
    
    MariaDB [user1Data]> SELECT * FROM employee;
    +------+-------+----------+
    | Name | Dept  | jobTitle |
    +------+-------+----------+
    | Afa  | Power | Engineer |
    +------+-------+----------+
    1 row in set (0.001 sec)
    
  • FILE
    當我們使用 LOAD_FILE 函數時,需要有 FILE 的權限,透過指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案)來讀檔案.

    檔案讀取時需要注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.

    很可惜當初設定 user1 時,沒有 FILE 權限,無法讀取檔案,顯示為 NULL.

    [root@localhost ~]# cat /var/ftp/file.txt
    TEST
    
    MariaDB [user1Data]> SET @txt = LOAD_FILE("/var/ftp/file.txt"); 
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [user1Data]> SELECT @txt;
    +------+
    | @txt |
    +------+
    | NULL |
    +------+
    1 row in set (0.000 sec)
    
    MariaDB [user1Data]> EXIT;
    Bye
    

USer2
測試一下 User2 的權限,可以發現只有 SELECT 與 FILE 權限.

[root@localhost ~]$ mysql -u user2 -p 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

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

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

MariaDB [(none)]> USE user1Data;
Database changed
  • INSERT
    MariaDB [user1Data]> INSERT INTO employee VALUES ('Ben','Testing','Engineer'), ('Afa','Power','Engineer');
    ERROR 1142 (42000): INSERT command denied to user 'user2'@'localhost' for table 'employee'
    
  • SELECT
    MariaDB [user1Data]> SELECT * FROM employee;
    +------+-------+----------+
    | Name | Dept  | jobTitle |
    +------+-------+----------+
    | Afa  | Power | Engineer |
    +------+-------+----------+
    1 row in set (0.001 sec)
    
  • DELETE
    MariaDB [user1Data]> DELETE FROM employee WHERE Name LIKE 'Afa';
    ERROR 1142 (42000): DELETE command denied to user 'user2'@'localhost' for table 'employee'
    
    MariaDB [user1Data]> SELECT * FROM employee;
    +------+-------+----------+
    | Name | Dept  | jobTitle |
    +------+-------+----------+
    | Afa  | Power | Engineer |
    +------+-------+----------+
    1 row in set (0.001 sec)
    
  • FILE
    當我們使用 LOAD_FILE 函數時,需要有 FILE 的權限,透過指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案)來讀檔案.

    檔案讀取時需要注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.

    [root@localhost ~]# cat /var/ftp/file.txt
    TEST
    
    MariaDB [user1Data]> SET @txt = LOAD_FILE("/var/ftp/file.txt"); 
    Query OK, 0 rows affected (0.000 sec)
    
    MariaDB [user1Data]> SELECT @txt;
    +-------+
    | @txt  |
    +-------+
    | TEST
     |
    +-------+
    1 row in set (0.000 sec)
    
    MariaDB [user1Data]> EXIT;
    Bye
    

REVOKE

如果要移除 user1 其 user1Date 資料庫的全部權限:

[root@localhost ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 67
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> REVOKE ALL PRIVILEGES ON user1Data.* FROM user1@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

user2 的 FILE 全域權限要怎麼移除.

MariaDB [(none)]> REVOKE FILE ON *.* FROM user2@'%'; 
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR user2@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for user2@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT ON `user1Data`.* TO 'user2'@'%'                                                         |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

DROP USER

如果要移除 user1 使用者.

MariaDB [(none)]> DROP USER user1@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT host,user FROM mysql.user;
+-----------------------+-------+
| host                  | user  |
+-----------------------+-------+
| %                     | user2 |
| 127.0.0.1             | root  |
| ::1                   | root  |
| localhost             | root  |
| localhost.localdomain | root  |
+-----------------------+-------+
6 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料