MariaDB 資料庫 – 使用者密碼

測試環境為 CentOS 8 x86_64

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-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.

建立使用者

建立使用者 user1@localhost .

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

CREATE USER 使用參數:

• ‘user1’@’localhost’ , user1 – 新增的使用者, localhost – 限制可從哪裡來存取, 可用 ‘%’ (代表全部),localhost 只限於本地端.
• IDENTIFIED BY ‘test1234’ – test1234 使用者密碼 (”代表不使用密碼,登入時就不需要加入 -p 參數)

可以看到使用者密碼是加密的

MariaDB [(none)]> SELECT host,user,password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| 127.0.0.1 | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| ::1       | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| localhost | user1 | *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A |
+-----------+-------+-------------------------------------------+
5 rows in set (0.001 sec)

更新使用者密碼

要如何更新使用者密碼,我們無法直接透過 Update 的方式.

MariaDB [(none)]> UPDATE mysql.user SET Password = 'test1234' WHERE User = 'user1';
Query OK, 1 row affected (0.006 sec)
Rows matched: 1  Changed: 1  Warnings: 0

透過 Update 的方式,密碼會直接以明碼儲存.

MariaDB [(none)]> SELECT host,user,password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| 127.0.0.1 | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| ::1       | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| localhost | user1 | test1234                                  |
+-----------+-------+-------------------------------------------+
5 rows in set (0.001 sec)

user1 使用者也無法登入.

[root@localhost ~]# mysql -u user1 -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

需使用 Alter 的方式.

MariaDB [(none)]> ALTER USER user1@localhost IDENTIFIED BY 'test1234';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT host,user,password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| 127.0.0.1 | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| ::1       | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| localhost | user1 | *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A |
+-----------+-------+-------------------------------------------+
5 rows in set (0.001 sec)

移除 user1 的密碼.

MariaDB [(none)]> SET PASSWORD FOR user1@localhost=PASSWORD('');
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT host,user,password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| 127.0.0.1 | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| ::1       | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| localhost | user1 |                                           |
+-----------+-------+-------------------------------------------+
5 rows in set (0.000 sec)

加密方式

剛剛 Alter 修改密碼是透過 PASSWORD() 函數來加密的.

MariaDB [(none)]> SELECT PASSWORD('test1234');
+-------------------------------------------+
| PASSWORD('test1234')                      |
+-------------------------------------------+
| *3D3B92F242033365AE5BC6A8E6FC3E1679F4140A |
+-------------------------------------------+
1 row in set (0.000 sec)

PASSWORD() 函數 (預設 old_passwords=0 )使用的加密方式是基於 SHA-1 (Secure Hash Algorithm 1) 雜湊方式.我們也可以把 old_passwords=1 (設定為舊的方式),使用 OLD_PASSWORD() 函數來加密.

MariaDB [(none)]> SET old_passwords=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> ALTER USER user1@localhost IDENTIFIED BY 'test1234';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT host,user,password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| 127.0.0.1 | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| ::1       | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| localhost | user1 | 38fb843c55682a6f                          |
+-----------+-------+-------------------------------------------+
5 rows in set (0.002 sec)

MariaDB [(none)]> SELECT OLD_PASSWORD('test1234');
+--------------------------+
| OLD_PASSWORD('test1234') |
+--------------------------+
| 38fb843c55682a6f         |
+--------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SET old_passwords=0;
Query OK, 0 rows affected (0.000 sec)

要注意這時候的 user1 是無法登入的.

[root@localhost ~]# mysql -u user1 -p
Enter password: 
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'user1'@'localhost' has a password in the old format; please change the password to the new format

DROP USER

如果要移除 user1 使用者.

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

MariaDB [(none)]> SELECT host,user,password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| 127.0.0.1 | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| ::1       | root  | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+-----------+-------+-------------------------------------------+
5 rows in set (0.000 sec)

錯誤訊息

我嘗試從 MariaDB 10.x 的 Client 指令連線到 MySql 8.x Server 出現以下的錯誤訊息.

plugin caching_sha2_password could not be loaded

官網解釋 https://mariadb.com/kb/en/authentication-plugin-sha-256/ , 目前 MySQL 5.6 與 MySQL 8.0 使用 sha256_password 認證方式,但是 MariaDB 並不支援,需先安裝 libmysqlclient library ,並在登入時指定 plugin 的加密 .so 檔案路徑.

[root@localhost ~]# yum whatprovides libmysqlclient*
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:13:46 ago on Wed Oct 21 23:16:51 2020.
mysql-libs-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64 : The shared libraries required for MySQL clients
Repo        : AppStream
Matched from:
Provide    : libmysqlclient.so.21()(64bit)
Provide    : libmysqlclient.so.21(libmysqlclient_21.0)(64bit)

mysql-libs-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64 : The shared libraries required for MySQL clients
Repo        : Stream-AppStream
Matched from:
Provide    : libmysqlclient.so.21()(64bit)
Provide    : libmysqlclient.so.21(libmysqlclient_21.0)(64bit)

[root@localhost ~]# yum install mysql-libs
[root@localhost ~]# mysql --plugin-dir=/usr/lib64/mariadb/plugin --user=user1 -p