MariaDB 원격 접속 허용하기

MariaDB 서버 : 192.168.60.15

Clinet IP : 192.168.60.5

 

1. MariaDB 접속

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

MariaDB [(none)]>

2. 접속 계정 생성 및 DB 권한 할당

# 계정 생성
MariaDB [(none)]> create user test@'%' identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.004 sec)

#데이터 베이스 생성
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.000 sec)

# test계정에 testdb 권한 할당
MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> GRANT ALL privileges ON testdb.* TO test@'%';
Query OK, 0 rows affected (0.002 sec)

MariaDB [testdb]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

3. test 계정으로 MariaDB 원격 접속 후 DB 접근 및 테이블 생성

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| testdb             |
+--------------------+
3 rows in set (0.00 sec)

# tetsdb 선택 후 테이블 생성 확인
MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> CREATE TABLE testTable(a int, b char(10));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> INSERT INTO testTable (a,b) values (2,'aaa');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO testTable (a,b) values (3,'bbb');
Query OK, 1 row affected (0.00 sec)

4. 클라이언트 IP (192.168.60.5)에서 접속 허용, 그외 차단 설정하기

# 기존 test 계정 삭제
MariaDB [mysql]> drop user test@'%';
Query OK, 0 rows affected (0.002 sec)

# test 계정 생성 - host 192.168.60.5
MariaDB [mysql]> create user test@'192.168.60.5' identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.002 sec)

# test DB에 권한 할당
MariaDB [mysql]> GRANT ALL PRIVILEGES ON testdb.* to test@'192.168.60.5';
Query OK, 0 rows affected (0.002 sec)

# 반영
flush privileges;

5. 클라이언트 IP (192.168.60.5)에서 접속 - 성공

[root@localhost /]# ifconfig
enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.60.5  netmask 255.255.255.0  broadcast 192.168.60.255
        inet6 fe80::a00:27ff:fe1a:ba54  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:1a:ba:54  txqueuelen 1000  (Ethernet)
        RX packets 579828  bytes 46279758 (44.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 804199  bytes 208174945 (198.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

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

MariaDB [(none)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.00 sec)

MariaDB [testdb]> select * from testtable;
+------+------+
| a    | b    |
+------+------+
|    2 | aaa  |
|    3 | bbb  |
+------+------+
2 rows in set (0.00 sec)

6. 다른 클라이언트 IP에서 접속 - 차단

[root@lottelab ~]# ifconfig
enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.60.10  netmask 255.255.255.0  broadcast 192.168.60.255
        inet6 fe80::a00:27ff:fe30:8f1f  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:30:8f:1f  txqueuelen 1000  (Ethernet)
        RX packets 49506  bytes 4766465 (4.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 11526  bytes 2468347 (2.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@lottelab ~]# mysql -h 192.168.60.15 -u test -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'192.168.60.10' (using password: YES)

7. test 계정에 testdb에 읽기(SELECT) 권한만 부여하기

# 기존 권한 확인
MariaDB [mysql]> show GRANTS for test@'192.168.60.5';
+----------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.60.5                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`192.168.60.5` IDENTIFIED BY PASSWORD '*8232A1298A49F710DBEE0B330C42EEC825D4190A' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `test`@`192.168.60.5`                                                    |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

# 기존 권한 제거
MariaDB [mysql]> REVOKE ALL ON testdb.* FROM test@'192.168.60.5';
Query OK, 0 rows affected (0.002 sec)

# 기존 권한 제거 확인
MariaDB [mysql]> show GRANTS for test@'192.168.60.5';
+----------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.60.5                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`192.168.60.5` IDENTIFIED BY PASSWORD '*8232A1298A49F710DBEE0B330C42EEC825D4190A' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

# SELECT 권한 부여
MariaDB [mysql]> GRANT SELECT ON testdb.* TO test@'192.168.60.5';
Query OK, 0 rows affected (0.002 sec)

# SELECT 권한 부여 확인
MariaDB [mysql]> show GRANTS for test@'192.168.60.5';
+----------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.60.5                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`192.168.60.5` IDENTIFIED BY PASSWORD '*8232A1298A49F710DBEE0B330C42EEC825D4190A' |
| GRANT SELECT ON `testdb`.* TO `test`@`192.168.60.5`                                                            |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

# 반영
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

8. 테스트

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

MariaDB [(none)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtable        |
+------------------+
1 row in set (0.00 sec)

# select 성공
MariaDB [testdb]> select * from testtable;
+------+------+
| a    | b    |
+------+------+
|    2 | aaa  |
|    3 | bbb  |
+------+------+
2 rows in set (0.00 sec)

# 인서트 command 실패  확인
MariaDB [testdb]> INSERT INTO testdb (a,b) values (2,'x');
ERROR 1142 (42000): INSERT command denied to user 'test'@'192.168.60.5' for table 'testdb'