Dheweke ujar manawa tembung sandhi sing paling apik yaiku sing ora kudu sampeyan eling. Ing kasus MySQL iki bisa uga amarga plugin kasebut lan versi kanggo MariaDB - .
Loro-lorone plugin iki ora anyar; akeh sing wis dicritakake ing blog sing padha, contone ing artikel babagan . Nanging, nalika ndeleng apa sing anyar ing MariaDB 10.4, aku nemokake manawa unix_socket saiki wis diinstal kanthi standar lan minangka salah sawijining metode otentikasi ("siji", amarga ing MariaDB 10.4 luwih saka siji plugin kasedhiya kanggo siji pangguna kanggo otentikasi, sing diterangake ing dokumen ).
Kaya sing dakkandhakake, iki dudu warta, lan nalika nginstal MySQL nggunakake paket .deb sing didhukung dening tim Debian, pangguna root digawe kanggo otentikasi soket. Iki bener kanggo MySQL lan MariaDB.
root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers
Original-Maintainer: Debian MySQL Maintainers <[email protected]>
Original-Maintainer: Debian MySQL Maintainers <<a href="mailto:[email protected]">[email protected]</a>>Kanthi paket Debian kanggo MySQL, pangguna root diotentikasi kaya ing ngisor iki:
root@app:~# whoami
root=
root@app:~# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root';
+------+-----------+-------------+-----------------------+
| user | host | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket | |
+------+-----------+-------------+-----------------------+
1 row in set (0.01 sec)Semono uga karo paket .deb kanggo MariaDB:
10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
MariaDB [(none)]> show grants;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)Paket .deb saka gudang resmi Percona uga ngatur otentikasi pangguna root ing soket auth lan kanggo Percona Server. Ayo menehi conto karo lan Ubuntu 16.04:
root@app:~# whoami
root
root@app:~# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312'
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> select user, host, plugin, authentication_string from mysql.user where user ='root';
+------+-----------+-------------+-----------------------+
| user | host | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket | |
+------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)Dadi apa sihir? Plugin mriksa manawa pangguna Linux cocog karo pangguna MySQL nggunakake pilihan soket SO_PEERCRED kanggo ngumpulake informasi babagan pangguna sing mbukak program klien. Mangkono, plugin mung bisa digunakake ing sistem sing ndhukung pilihan SO_PEERCRED, kayata Linux. Opsi soket SO_PEERCRED ngidini sampeyan ngerteni proses sing ana gandhengane karo soket kasebut. Banjur dheweke wis nampa jeneng panganggo sing ana gandhengane karo uid iki.
Iki conto pangguna "vagrant":
vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'Amarga ora ana pangguna "vagrant" ing MySQL, kita ora bisa ngakses. Ayo nggawe pangguna kaya ngono lan coba maneh:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)
vagrant@mysql1:~$ mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 45
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
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)]> show grants;
+---------------------------------------------------------------------------------+
| Grants for vagrant@localhost |
+---------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)Kedadeyan!
Ya, kepiye distribusi non-Debian sing ora diwenehake kanthi standar? Coba Percona Server kanggo MySQL 8 diinstal ing CentOS 7:
mysql> show variables like '%version%comment';
+-----------------+---------------------------------------------------+
| Variable_name | Value |
+-----------------+---------------------------------------------------+
| version_comment | Percona Server (GPL), Release 7, Revision 613e312 |
+-----------------+---------------------------------------------------+
1 row in set (0.01 sec)
mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
ERROR 1524 (HY000): Plugin 'auth_socket' is not loadedBummer. Apa sing ilang? Plugin ora dimuat:
mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)Ayo nambahake plugin menyang proses kasebut:
mysql> nopager
PAGER set to stdout
mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)
mysql> pager grep socket; show plugins;
PAGER set to 'grep socket'
| auth_socket | ACTIVE | AUTHENTICATION | auth_socket.so | GPL |
48 rows in set (0.00 sec)Saiki kita duwe kabeh sing dibutuhake. Ayo coba maneh:
mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost';
Query OK, 0 rows affected (0.01 sec)Sampeyan saiki bisa mlebu nggunakake jeneng panganggo "percona".
[percona@ip-192-168-1-111 ~]$ whoami
percona
[percona@ip-192-168-1-111 ~]$ mysql -upercona
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 19
Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona';
+---------+-----------+-------------+-----------------------+
| user | host | plugin | authentication_string |
+---------+-----------+-------------+-----------------------+
| percona | localhost | auth_socket | |
+---------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)Lan makarya maneh!
Pitakonan: apa bisa mlebu menyang sistem ing login percona sing padha, nanging minangka pangguna sing beda?
[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'Ora, ora bakal bisa
kesimpulan
MySQL cukup fleksibel ing sawetara aspek, salah sijine yaiku metode otentikasi. Kaya sing sampeyan ngerteni saka kiriman iki, akses bisa diduweni tanpa sandhi, adhedhasar pangguna OS. Iki bisa migunani ing skenario tartamtu, lan salah sijine yaiku nalika pindhah saka RDS / Aurora menyang MySQL biasa nggunakake kanggo isih entuk akses, nanging tanpa sandhi.
Source: www.habr.com