MariaDB 資料庫使用者登入權限

測試環境為 CentOS 7 x86_64 .

如果是使用 Ubuntu 需要將 /etc/mysql/my.cnf (Ubuntu 14.04) , /etc/mysql/mariadb.conf.d/50-server.cnf (Ubuntu16.04) 裡面的 bind-address = 127.0.0.1 註記起來 (#後面文字表示為說明),才能讓使用者遠端登入.

使用者的權限可以區分為

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

設定了三個使用者並給予不同的登入權限.

[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 USER user1@localhost IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> CREATE USER user2@'172.16.15.%' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

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

MariaDB [(none)]> select host,user from mysql.user; 
+-------------+-------+
| host        | user  |
+-------------+-------+
| %           | user3 |
| 127.0.0.1   | root  |
| 172.16.15.% | user2 |
| ::1         | root  |
| localhost   | pma   |
| localhost   | root  |
| localhost   | user1 |
+-------------+-------+
7 rows in set (0.00 sec)

CREATE USER 使用參數:
username@localhost , username – 新增的使用者, localhost – 限制可從哪裡來存取, 可用 ‘%’ (代表全部)
identified by ‘your.password’ – password: 使用者密碼 (”代表不使用密碼,登入時就不需要加入 -p 參數)

• user1 只能透過本機 localhost 登入.
• user2 可以從 172.16.15.* 網段登入.
• user3 可以從任意處登入.

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)

MariaDB [(none)]> show grants for user2@'172.16.15.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for user2@172.16.15.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'172.16.15.%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show grants for user3@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for user3@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user3'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit;
Bye

新增加的使用者都有 GRANT USAGE ON *.* 權限, USAGE 代表沒權限 ( no privileges ) 沒有操作任何資料庫的權限.

• User1 只有本地端 127.0.0.1 的登入權限.

  [root@localhost ~]$ mysql -u user1 -p
  Enter password: 
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 88
  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)]> exit;
  Bye
  [root@localhost ~]$ mysql -u user1 -p -h 172.16.15.129
  Enter password: 
  ERROR 1045 (28000): Access denied for user 'user1'@'172.16.15.129' (using password: YES)
  

• User2 只有遠端 172.16.0.%(*) 的登入權限.

  [root@localhost ~]$ mysql -u user2 -p 
  Enter password: 
  ERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: YES)
  [root@localhost ~]$ mysql -u user2 -p -h 172.16.15.129
  Enter password: 
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 102
  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)]> exit;
  Bye
  

• User3 有本地端 127.0.0.1 的登入權限,也有遠端 %(*) 的登入權限.

  [root@localhost ~]$ mysql -u user3 -p 
  Enter password: 
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 104
  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)]> exit;
  Bye
  [root@localhost ~]$ mysql -u user3 -p -h 172.16.15.129
  Enter password: 
  Welcome to the MariaDB monitor.  Commands end with ; or \g.
  Your MariaDB connection id is 105
  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)]> exit;
  Bye