[MySQL] 3. user(계정) 생성, 로그인, 변경, 삭제

이전 시간에서 MySQL 8.0 Server 설치와 database 생성/삭제/변경 에 관해 알아봤습니다.

계정을 추가적으로 생성하지 않았기 때문에 로그인 가능한 계정은 root 뿐입니다.
root 계정은 관리자 계정으로, 모든 권한을 보유하고 있는 계정이기 때문에, DB 관리자 이외의 계정들은 root계정이 아닌 최소한의 권한만을 부여받은 계정 계정을 이용해야 보안 상 유리합니다.

이번 시간에는 사용자 계정을 만드는 방법을 알아보도록 하겠습니다.

계정을 만들기 앞서, 먼저 root 계정으로 로그인 해봅니다.

mysql -u root -p

사용자 계정에 관한 정보는 mysql database에 들어있기 때문에 mysql 데이터베이스에 들어가 아래 과정들을 진행합니다.

use mysql;

SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

MySQL을 최초 설치한 후 user테이블에 등록된 계정 정보는 위와 같습니다.

※ MySQL 8.0의 user 비밀번호의 기본 auth_plugin은 caching_sha2_password 입니다.

1. 계정 생성
2. 생성한 계정으로 로그인
   2-1) 로컬 서버에서 접속
   2-2) 외부 서버에서 접속
3. 계정 변경
   3-1) 비밀번호 변경
   3-2) 2번째 비밀번호 설정
   3-3) 오래된 비밀번호 제거
4. 계정 삭제
5. 비밀번호 정책

---

1. 계정 생성

CREATE USER '계정이름'@'호스트'
IDENTIFIED [WITH auth_plugin] BY '비밀번호';

MySQL 서버 설치 후, 별도의 계정을 생성하지 않았기 때문에 현재에는 root 계정만 존재합니다.
위의 과정에 이어서 진행합니다.

CREATE USER 'jini'@'localhost' IDENTIFIED BY 'testTest111!';

CREATE USER 'jini'@'59.xx.xxx.31' IDENTIFIED BY 'testTest111!';

username jini의 localhost와 59.xx.xxx.31 호스트에 대한 접속 계정을 생성하였습니다.
※ ip 정보는 개인정보로 마스킹 처리하였습니다

인증 플러그인을 설정하지 않을 경우, 기본 플러그인인 caching_sha2_password로 비밀번호를 생성됩니다.
(만일 기본 인증 플러그인은 변경하고 싶다면 my.cnf에 설정하면 됩니다.)

CREATE USER 'jini'@'localhost' IDENTIFIED WITH mysql_native_password BY 'testTest111!';

identified 뒤에 직접 비밀번호 인증 플러그인을 설정할수도 있습니다.

'jini'@'localhost' 계정도 만들어보고 나서 다시 등록된 계정정보를 확인해봅시다.

mysql> SELECT user, host, plugin FROM user;
+------------------+--------------+-----------------------+
| user             | host         | plugin                |
+------------------+--------------+-----------------------+
| jini             | 59.xx.xxx.31 | caching_sha2_password |
| jini             | localhost    | caching_sha2_password |
| mysql.infoschema | localhost    | caching_sha2_password |
| mysql.session    | localhost    | caching_sha2_password |
| mysql.sys        | localhost    | caching_sha2_password |
| root             | localhost    | caching_sha2_password |
+------------------+--------------+-----------------------+

'jini'@'59.xx.xxx.31', 'jini'@'localhost' 가 정상적으로 추가되었음을 확인할 수 있습니다.

2. 생성한 계정으로 로그인

조금전에 생성했던 jini 계정으로 로그인해봅시다.

2-1) 로컬 서버에서 접속

[jini@apple ~]$ mysql -u jini -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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>

mysql이 설치된 서버에서의 로컬접속이 정상적으로 이뤄졌습니다.

2-2) 외부 서버에서 접속

그럼 이번엔 외부 서버에서 접속해보도록 할까요?

C:\Users\jini>mysql -u jini -p -h 150.xx.xxx.189
Enter password: *********
ERROR 2003 (HY000): Can\'t connect to MySQL server on '150.xx.xxx.189' (10060)

MySQL 서버에 접속할 수 없다는 에러메시지가 뜨네요? 왜일까요?

username, MySQL이 설치된 host주소 그리고 비밀번호를 제대로 입력했음에도 에러메시지가 뜬다면, MySQL이 설치된 서버에 MySQL 서비스가 개방되었는지 확인해야 합니다.
아래부터는 MySQL이 설치된 서버를 통칭 서버라고 간단히 부르겠습니다.

서버의 방화벽 설정을 확인해봅시다.

[jini@apple ~]$ sudo firewall-cmd --list-all
apple (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0
  sources:
  services: ssh dhcpv6-client
  ports: 8989/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

방화벽 설정을 확인해보니, 서버에는 mysql 서비스나 MySQL 의 포트인 3306이 개방되어있지 않았습니다.

[jini@apple ~]$ sudo firewall-cmd --permanent --add-service=mysql
success
[jini@apple ~]$ sudo firewall-cmd --reload
success

mysql 서비스를 영구적으로 방화벽을 개방하고, 방화벽을 reload합니다.
(mysql 서비스 개방 대신 sudo firewall-cmd --permanent --add-port=3306/tcp와 같이 포트를 개방해도 됩니다.)

[jini@apple ~]$ sudo firewall-cmd --list-all
apple (active)
  target: default
  icmp-block-inversion: no
  interfaces: eth0
  sources:
  services: ssh dhcpv6-client mysql
  ports: 8989/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

다시 방화벽 정보를 출력해봅니다. services에 mysql이 추가되었습니다.

다시 외부에서 mysql에 접속을 시도해봅니다.

C:\Users\jini>mysql -u jini -p -h 150.xx.xxx.189
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.19 MySQL Community Server - GPL

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>

이번에는 잘 접속이 되네요!

MySQL Workbench 툴에서도 정상적으로 접속이 되는 것을 확인할 수 있습니다.

3. 계정 변경

3-1) 비밀번호 변경

ALTER USER 'jini'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'testTest111@';

with 플러그인명을 생략할 경우, 계정에 설정된 플러그인을 자동으로 인식합니다.

3-2) 2번째 비밀번호 설정

ALTER USER 'jini'@'localhost' IDENTIFIED BY 'testTest222@'
RETAIN CURRENT PASSWORD;

기존의 비밀번호를 유지하면서 2번째 비밀번호를 추가할 수도 있습니다.

3-3) 오래된 비밀번호 제거

ALTER USER 'jini'@'localhost' DISCARD OLD PASSWORD;

비밀번호가 2개 설정되어있을 경우, 오래된 비밀번호를 제거합니다.

4. 계정 삭제

DROP user 'jini'@'localhost';

drop 문으로 계정을 제거할 수 있습니다.

---

5. 비밀번호 정책

CREATE USER 'test'@'localhost' IDENTIFIED BY '1234';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

간소한 비밀번호로 계정을 생성하려 할 때, 위와 같은 에러가 발생됩니다.
비밀번호가 현재 정책을 만족하지 못한다고 나와있는데, 비밀번호 관련 정책을 알고 싶다면, 아래의 쿼리를 이용하면 됩니다.

mysql> SHOW VARIABLES LIKE '%validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

database 서버 접속 계정 비밀번호는 보안을 위해 기본적으로 MIDIUM 정책을 갖고 있습니다.

• LOW
  • 총 길이(validate_password.length) 만 체크함
• MIDIUM
  • LOW 정책 및 아래의 조건 체크
  • validate_password.number_count
  • validate_password.mixed_case_count
  • validate_password.special_char_count
• STRONG
  • MEDIUM 정책 및 아래의 조건 체크
  • validate_password.dictionary_file에 지정한 단어와의 불일치

기본값에 의하면 아래의 조건을 만족해야만 계정을 만들 수 있습니다.

• 총 길이 8자 이상
• 숫자 1자 이상
• 대소문자 각각 1자 이상
• 특수문자 1자 이상

특정 계정만은 간소한 비밀번호로 생성하고 싶다면, 임시로 validate_password.policy를 LOW로 변경하고, 길이를 변경한 후 계정을 생성하면 됩니다.

validate_password 관련 설정을 임시로 변경하여 계정을 만들어봅시다.

mysql> SET GLOBAL validate_password.policy = 'LOW';
mysql> SET GLOBAL validate_password.length = 4;

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.01 sec)

이제는 간단한 비밀번호를 이용하여 계정을 생성할 수 있네요!
계정을 생성했다면 다시 이전의 정책으로 원복시킵니다.

mysql> SET GLOBAL validate_password.policy = 'MEDIUM';
mysql> SET GLOBAL validate_password.length = 8;

---

++

• MySQL 방화벽 개방 in CentOS 7
• MySQL 외부 접속