[MySQL] DB 생성, 사용자 등록 및 권한 부여

개발에서 H2 다음으로 자주 접하게 되는 DBMS가 MySQL인데 한 번 설정하면 다시 쳐다보지 않아서, 자꾸 까먹게 되는 것이 사용자 생성 및 권한 부여 방법입니다. 계속 미루다가 이번에 상세히 정리했습니다.

---

1. DB 접속 방법

MySQL에 DB를 생성하고 사용자를 등록하는 방법 중 가장 기본적인 방법이 MySQL에 접속해서 SQL로 작업하는 방법입니다. 미리 설정된 DBMS Tool 이 있다면 이를 이용하는 방법도 있지만, 항상 환경이 갖춰져 있지 않은 경우가 많으니 SQL을 사용하는 방법을 꼭 익혀두는 것이 좋습니다.

 

MySQL을 설치했다면 콘솔(윈도우에서는 cmd 창)에서 명령을 사용해 접속할 수 있습니다.

# 비밀번호 입력
> mysql -u root - p
Enter password:

mysql >

 

# 공백 없이 바로 입력
> mysql -uroot -p[비밀번호]

mysql>

 

2. DB 생성 및 확인

2.1 DB 생성

MySQL에서 DB를 생성하려면 DB 접속 후 CREATE DATABASE 명령을 사용합니다.

# CREATE DATABASE [DB명]  CHARACTER SET [character-set] COLLATE [collate-set]
#
# UTF8 설정
# - CREATE DATABASE [DB명] CHARACTER SET utf8 COLLATE utf8_bin;

# 간략히 아래와 같이 생성할 수도 있지만 Character Set을 명시하는 것이 좋다.
mysql> CREATE DATABASE mydb;

mysql> CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;

2.2 DB 확인 및 조회

DBMS에서 관리하고 있는 DB 목록 조회

# show databases : 생성되어 있는 DB 확인
#
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

DB 사용 설정 및 테이블 목록 조회 

# use [DB명] : 조회할 DB 설정
#
mysql> use mysql;
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

# show tables : 조회중인 DB의 table 목록 조회
#
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
                            ...
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
38 rows in set (0.00 sec)

mysql>

 

3. 사용자 추가 및 권한 부여

3.1 사용자 추가 및 확인

# create user : 사용자 추가
# 
# CREATE USER '[사용자id]'@'[호스트]' IDENTIFIED BY '[비밀번호]';
# - 사용자 ID : myuser
# - 비밀번호  : 1234
# - 호스트    : localhost
#
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| myuser           | localhost |  # 추가한 사용자 
| root             | localhost |
+------------------+-----------+
9 rows in set (0.00 sec)

 

MySQL 은 DB접속 권한과 Network 접속 권한이 다릅니다. 

접속이 가능한 host를 명시적으로 등록해야만 다른 네트워크에서 접속할 수 있습니다. 네트워크는 localhost 즉 콘솔에서만 접속을 허용하거나, 다른 네트워크에서 접속이 가능하도록 호스트 대역을 지정할 수 있습니다.

 

# localhost 만 접속 가능
mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY '1234';

# 모든 외부 네트워크에서 접속 가능
mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY '1234';

# 특정 IP 만 접속 가능
mysql> CREATE USER 'myuser'@'172.23.0.100' IDENTIFIED BY '1234';

# 특정 IP 대역에서만 접속 가능
mysql> CREATE USER 'myuser'@'172.23.%' IDENTIFIED BY '1234';

 

# 특정 IP 대역에서만 접속 가능한 사용자 추가 후 user 목록 확인
#
mysql> CREATE USER 'myuser'@'172.23.%' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user, host FROM mysql.user;
                           # [mysql.] 과 같이 DB명을 적어주면 
                           # DB를 사용하도록 설정하지 않고 바로 접근할 수 있다.
                           # - use [DB명] : use mysql;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| myuser           | 172.23.%  | # myuser 외부 접속
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| myuser           | localhost | # myuser localhost 만 접속 
| root             | localhost |
+------------------+-----------+

 

3.2 권한 부여

사용자를 생성했더라도 권한을 부여하지 않으면 DBMS 내부의 DB의 접근이 차단됩니다. 따라서 아래 명령을 사용하여 DB 접근 권한을 주어야만 사용이 가능합니다. 권한을 부여하지 않으면 접속하더라도 대부분의 DB 정보를 조회할 수 없습니다.

 

# mydb 생성 후, root 로 database 목록 조회
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# 권한을 부여하지 않은 myuser로 database 목록 조회
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

# 권한 부여
mysql> grant all privileges on mydb.* to 'myuser'@'localhost';

# 권한 부여 후 myuser로 database 목록 조회
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

 

한 가지 주의할 점은 mysql의 user는 host와 함께 연동되므로 추가로 외부에서 접속이 가능하도록 사용자를 추가하였다면 그 사용자에게도 권한을 부여해야 합니다. 

 

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| myuser           | 172.23.%  | # 외부 접속을 위한 계정
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| myuser           | localhost | # 내부 접속을 위한 계정
| root             | localhost |
+------------------+-----------+

# 간단하게 'root'@'%' 로 등록하면 어디서든 사용할 수 있지만,
# 보안에 대한 인식을 위해 localhost와 외부 접속은 
# 분리하여 설정하는 습관을 들이는 것이 안전하다.

 

bash-4.4# mysql -h172.23.0.1 -umyuser -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.

# 'myuser'@'172.23.%' 로 등록한 사용자는 권한 부족으로 DB가 조회되지 않음.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)

 

자세한 접근 권한은 MySQL의 문서에서 확인할 수 있습니다. 

https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-privileges

 

여기서는 간단히 개발 및 테스트를 위한 모든 권한을 부여하는 방법과 특정 DB와 테이블에 대해 접근 권한을 부여하는 방법만 설명하겠습니다. 

 

# GRANT [권한 목록] on [DB명.테이블명] TO '[사용자ID]'@'[호스트]'
#
# myuser@localhost 에 모든 권한 부여
GRANT all privileges ON *.* TO 'myuser'@'localhost';

# myuser@localhost 에 특정 DB의 대한 모든 권한 부여
GRANT all privileges ON [DB명].* TO 'myuser'@'localhost';

# myuser@localhost 에 특정 DB의 특정 테이블에 대한 SELECT 권한 부여
# - DB명    : mydb
# - 테이블명 : mytable
# - 권한     : select
#            : select, insert, update, delete 와 같이 ','로 구분하여 추가할 수 있다.
GRANT select ON mydb.mytable TO 'myuser'@'localhost';

 

4. 사용자 계정 삭제

# DROP USER '[사용자ID]'@'[호스트]'
#
mysql> DROP USER 'myuser'@'localhost';
Query OK, 0 rows affected (0.02 sec)

 

5. 권한 적용

변경한 권한을 즉시 적용하려면 Flush 명령을 사용해야 합니다.

# 변경한 권한 부여, 즉시 반영
#
mysql> flush privileges;