๊ฐ์ฅ ์ข์ ๋น๋ฐ๋ฒํธ๋ ๊ธฐ์ตํ ํ์๊ฐ ์๋ ๋น๋ฐ๋ฒํธ๋ผ๊ณ ํฉ๋๋ค. MySQL์ ๊ฒฝ์ฐ ํ๋ฌ๊ทธ์ธ ๋๋ถ์ ๊ฐ๋ฅํฉ๋๋ค.
์ด ๋ ํ๋ฌ๊ทธ์ธ์ ๋ชจ๋ ์ ํ ์๋ก์ด ๊ฒ์ด ์๋๋๋ค. ๋์ผํ ๋ธ๋ก๊ทธ์์ ์ด์ ๋ํด ๋ง์ ์ด์ผ๊ธฐ๊ฐ ์์์ต๋๋ค.
๋ด๊ฐ ๋งํ๋ฏ์ด ์ด๊ฒ์ ๋ด์ค๊ฐ ์๋๋ฉฐ Debian ํ์์ ์ง์ํ๋ .deb ํจํค์ง๋ฅผ ์ฌ์ฉํ์ฌ MySQL์ ์ค์นํ ๋ ์์ผ ์ธ์ฆ์ ์ํด ๋ฃจํธ ์ฌ์ฉ์๊ฐ ์์ฑ๋ฉ๋๋ค. ์ด๋ MySQL๊ณผ 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>>
MySQL์ฉ Debian ํจํค์ง๋ฅผ ์ฌ์ฉํ๋ฉด ๋ฃจํธ ์ฌ์ฉ์๊ฐ ๋ค์๊ณผ ๊ฐ์ด ์ธ์ฆ๋ฉ๋๋ค.
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)
MariaDB์ฉ .deb ํจํค์ง์ ๊ฒฝ์ฐ๋ ๋ง์ฐฌ๊ฐ์ง์ ๋๋ค.
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)
๊ณต์ Percona ์ ์ฅ์์ .deb ํจํค์ง๋ auth-socket ๋ฐ Percona Server์ ๋ํ ๋ฃจํธ ์ฌ์ฉ์ ์ธ์ฆ์ ๊ตฌ์ฑํฉ๋๋ค. ์๋ฅผ ๋ค์ด ๋ณด๊ฒ ์ต๋๋ค.
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)
๊ทธ๋ ๋ค๋ฉด ๋ง๋ฒ์ ๋ฌด์์ ๋๊น? ํ๋ฌ๊ทธ์ธ์ ํด๋ผ์ด์ธํธ ํ๋ก๊ทธ๋จ์ ์คํํ๋ ์ฌ์ฉ์์ ๋ํ ์ ๋ณด๋ฅผ ์์งํ๊ธฐ ์ํด SO_PEERCRED ์์ผ ์ต์ ์ ์ฌ์ฉํ์ฌ Linux ์ฌ์ฉ์๊ฐ MySQL ์ฌ์ฉ์์ ์ผ์นํ๋์ง ํ์ธํฉ๋๋ค. ๋ฐ๋ผ์ ํ๋ฌ๊ทธ์ธ์ Linux์ ๊ฐ์ด SO_PEERCRED ์ต์ ์ ์ง์ํ๋ ์์คํ ์์๋ง ์ฌ์ฉํ ์ ์์ต๋๋ค. SO_PEERCRED ์์ผ ์ต์ ์ ์ฌ์ฉํ๋ฉด ์์ผ๊ณผ ๊ด๋ จ๋ ํ๋ก์ธ์ค์ uid๋ฅผ ์ฐพ์ ์ ์์ต๋๋ค. ๊ทธ๋ฆฌ๊ณ ๊ทธ๋ ์ด๋ฏธ ์ด uid์ ๊ด๋ จ๋ ์ฌ์ฉ์ ์ด๋ฆ์ ๋ฐ์์ต๋๋ค.
๋ค์์ "vagrant" ์ฌ์ฉ์์ ์์ ๋๋ค.
vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'
MySQL์๋ "๋ฐฉ๋์" ์ฌ์ฉ์๊ฐ ์์ผ๋ฏ๋ก ์ก์ธ์ค๊ฐ ๊ฑฐ๋ถ๋ฉ๋๋ค. ์ด๋ฌํ ์ฌ์ฉ์๋ฅผ ์์ฑํ๊ณ ๋ค์ ์๋ํด ๋ณด๊ฒ ์ต๋๋ค.
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)
๊ทธ๊ฒ์ ๋์๋ค!
์, ์ด๊ฒ์ด ๊ธฐ๋ณธ์ ์ผ๋ก ์ ๊ณต๋์ง ์๋ ๋น๋ฐ๋น์ ๋ฐฐํฌํ์ ์ด๋ป์ต๋๊น? CentOS 8์ ์ค์น๋ MySQL 7์ฉ Percona Server๋ฅผ ์ฌ์ฉํด ๋ณด๊ฒ ์ต๋๋ค.
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 loaded
๋ฒ๋จธ. ๋ฌด์์ด ๋น ์ก๋์? ํ๋ฌ๊ทธ์ธ์ด ๋ก๋๋์ง ์์์ต๋๋ค:
mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)
ํ๋ก์ธ์ค์ ํ๋ฌ๊ทธ์ธ์ ์ถ๊ฐํด ๋ณด๊ฒ ์ต๋๋ค.
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)
์ด์ ์ฐ๋ฆฌ๋ ํ์ํ ๋ชจ๋ ๊ฒ์ ๊ฐ์ท์ต๋๋ค. ๋ค์ ํด๋ณด์:
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)
์ด์ ์ฌ์ฉ์ ์ด๋ฆ โ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)
๊ทธ๋ฆฌ๊ณ ๊ทธ๊ฒ์ ๋ค์ ์๋ํ์ต๋๋ค!
์ง๋ฌธ: ๋์ผํ percona ๋ก๊ทธ์ธ์ผ๋ก ๋ค๋ฅธ ์ฌ์ฉ์๋ก ์์คํ ์ ๋ก๊ทธ์ธํ ์ ์์ต๋๊น?
[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'
์๋จ, ์๋์.
์ถ๋ ฅ
MySQL์ ์ฌ๋ฌ ์ธก๋ฉด์์ ๋งค์ฐ ์ ์ฐํ๋ฉฐ ๊ทธ ์ค ํ๋๋ ์ธ์ฆ ๋ฐฉ๋ฒ์
๋๋ค. ์ด ๊ฒ์๋ฌผ์์ ๋ณผ ์ ์๋ฏ์ด OS ์ฌ์ฉ์๋ฅผ ๊ธฐ์ค์ผ๋ก ๋น๋ฐ๋ฒํธ ์์ด ์ก์ธ์คํ ์ ์์ต๋๋ค. ์ด๋ ํน์ ์๋๋ฆฌ์ค์์ ์ ์ฉํ ์ ์์ผ๋ฉฐ ๊ทธ ์ค ํ๋๋ ๋ค์์ ์ฌ์ฉํ์ฌ RDS/Aurora์์ ์ผ๋ฐ MySQL๋ก ๋ง์ด๊ทธ๋ ์ด์
ํ ๋์
๋๋ค.
์ถ์ฒ : habr.com