Öeldakse, et parim parool on see, mida ei pea meeles pidama. MySQL-i puhul on see võimalik tänu pistikprogrammile ja selle versioon MariaDB jaoks - .
Mõlemad pluginad pole üldse uued, nendest on siinsamas ajaveebis palju räägitud, näiteks artiklis . MariaDB 10.4 uut uudist uurides avastasin aga, et unix_socket on nüüd vaikimisi installitud ja see on üks autentimismeetoditest (“üks neist”, sest versioonis MariaDB 10.4 on ühele kasutajale autentimiseks saadaval rohkem kui üks pistikprogramm, mis on dokumendis selgitatud ).
Nagu ma ütlesin, pole see uudis ja MySQL-i installimisel Debiani meeskonna toetatud .deb-pakettide abil luuakse sokli autentimiseks juurkasutaja. See kehtib nii MySQL-i kui ka MariaDB kohta.
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>>Debiani MySQL-i pakettide puhul autentitakse juurkasutaja järgmiselt:
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)Sama kehtib MariaDB paketi .deb kohta:
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)Ametliku Percona hoidla .deb paketid konfigureerivad ka juurkasutaja autentimise autentimispesa all ja Percona serveri jaoks. Toome näite koos ja 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)Mis on maagia? Pistikprogramm kontrollib, et Linuxi kasutaja ühtib MySQL-i kasutajaga, kasutades suvandit SO_PEERCRED, et koguda teavet klientprogrammi käitava kasutaja kohta. Seega saab pistikprogrammi kasutada ainult süsteemides, mis toetavad valikut SO_PEERCRED, näiteks Linux. Pistikupesa SO_PEERCRED valik võimaldab teil teada saada pesaga seotud protsessi uid-i. Ja siis saab ta juba selle uid-ga seotud kasutajanime.
Siin on näide kasutajaga "vagrant":
vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'Kuna MySQL-is pole "rändavat" kasutajat, on meil juurdepääs keelatud. Loome sellise kasutaja ja proovime uuesti:
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)Juhtus!
Kuidas on lood mitte-Debiani distributsiooniga, kus seda vaikimisi ei pakuta? Proovime CentOS 8-le installitud Percona Serverit MySQL 7 jaoks:
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 loadedJama. Mis jäi puudu? Pluginat pole laaditud:
mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)Lisame protsessile pistikprogrammi:
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)Nüüd on meil kõik, mida vajame. Proovime uuesti:
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)Nüüd saate sisse logida kasutajanimega "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)Ja see töötas jälle!
Küsimus: kas süsteemi saab sisse logida sama percona loginiga, kuid erineva kasutajana?
[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'Ei, see ei toimi.
Väljund
MySQL on mitmes aspektis üsna paindlik, millest üks on autentimismeetod. Nagu sellest postitusest näha, on OS-i kasutajate põhjal juurdepääs ilma paroolideta. See võib olla kasulik teatud stsenaariumide korral ja üks neist on RDS/Aurorast tavalisele MySQL-ile üleminekul, kasutades selleks juurdepääsu saamiseks, kuid ilma paroolideta.
Allikas: www.habr.com