325 瀏覽數

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)/資料欄(column)權限.請參考 http://benjr.tw/98033

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

[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 參數)

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 代表了登入權限而已,並沒有操作任何資料庫的權限.

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

發表迴響