Viņi saka, ka labākā parole ir tā, kura jums nav jāatceras. MySQL gadījumā tas ir iespējams, pateicoties spraudnim un tā versija MariaDB - .
Abi šie spraudņi nemaz nav jauni, par tiem ir daudz runāts šajā pašā emuārā, piemēram, rakstā par . Tomēr, meklējot MariaDB 10.4 jaunumus, es atklāju, ka unix_socket tagad ir instalēts pēc noklusējuma un ir viena no autentifikācijas metodēm (“viena no”, jo MariaDB 10.4 versijā vienam lietotājam autentifikācijai ir pieejams vairāk nekā viens spraudnis, kas ir paskaidrots dokumentā ).
Kā jau teicu, tas nav jaunums, un, instalējot MySQL, izmantojot Debian komandas atbalstītās .deb pakotnes, tiek izveidots root lietotājs ligzdas autentifikācijai. Tas attiecas gan uz MySQL, gan uz 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>>Izmantojot Debian pakotnes MySQL, saknes lietotājs tiek autentificēts šādi:
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)Tas pats attiecas uz .deb pakotni 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).deb pakotnes no oficiālā Percona repozitorija arī konfigurē root lietotāja autentifikāciju, izmantojot auth-socket un Percona Server. Sniegsim piemēru ar un 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)Tātad, kāda ir maģija? Spraudnis pārbauda, vai Linux lietotājs atbilst MySQL lietotājam, izmantojot ligzdas opciju SO_PEERCRED, lai apkopotu informāciju par lietotāju, kas darbojas klienta programmā. Tādējādi spraudni var izmantot tikai sistēmās, kas atbalsta opciju SO_PEERCRED, piemēram, Linux. SO_PEERCRED ligzdas opcija ļauj uzzināt ar ligzdu saistītā procesa uid. Un tad viņš jau saņem ar šo uid saistīto lietotājvārdu.
Šeit ir piemērs ar lietotāju "vagrant":
vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'Tā kā MySQL nav neviena "klaidoņa" lietotāja, mums ir liegta piekļuve. Izveidosim šādu lietotāju un mēģināsim vēlreiz:
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)Notika!
Nu, kā ar izplatīšanu, kas nav Debian, kur tas netiek nodrošināts pēc noklusējuma? Izmēģināsim Percona Server for MySQL 8, kas instalēts 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 loadedSūds. Kas pietrūka? Spraudnis nav ielādēts:
mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)Pievienosim procesam spraudni:
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)Tagad mums ir viss nepieciešamais. Pamēģināsim vēlreiz:
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)Tagad varat pieteikties, izmantojot lietotājvārdu “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)Un atkal izdevās!
Jautājums: vai sistēmā varēs pieteikties ar to pašu percona login, bet kā citam lietotājam?
[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'Nē, tas neizdosies.
secinājums
MySQL ir diezgan elastīgs vairākos aspektos, no kuriem viens ir autentifikācijas metode. Kā redzat no šī ieraksta, piekļuvi var iegūt bez parolēm, pamatojoties uz OS lietotājiem. Tas var būt noderīgi noteiktos scenārijos, un viens no tiem ir migrēšana no RDS/Aurora uz parasto MySQL, izmantojot lai joprojām piekļūtu, bet bez parolēm.
Avots: www.habr.com