[DB] MySQL 계정 생성 및 권한 부여 (Real MySQL 8.0)

안녕하세요. 회사와 함께 성장하고 싶은 KOSE입니다.

이번 포스팅은 MySQL의 계정 생성 및 권한 부여에 관한 내용을 정리하고자 합니다.

내용 및 예제 소스는 백은빈 님, 이성욱 님의 Real MySQL 8.0를 기반으로 구성하였습니다.

 

제 운영체제 환경은 ubuntu 20.04이고, MySQL 버전은 8.0이므로 맥 OS, Windows 사용자분들이나, MySQL이 8.0대 버전이 아니신 분들은 명령어가 다를 수 있습니다.

 

1. MySQL 계정 생성하기

 

계정을 생성하기 전에 먼저 MySQL이 활성화되어 있지 않다면 아래 명령어를 통해 MySQL을 실행시켜주어야 합니다.

sudo systemctl start mysql

 

MySQL이 설치되어 있다고 가정하고, root로 로그인을 하기 위한 명령어는 다음과 같습니다.

mysql -u root -p

 

root 계정으로 이동 후, db에 접근 가능한 권한을 분리하여 적용하기 위해 계정을 생성합니다.

create user 'kose1'@'%'
identified with 'mysql_native_password' by 'password'
require none
password expire interval 30 day
account unlock
password history default
password reuse interval default
password require current default;

이때, 저는 다음과 같은 에러가 발생하였습니다.

--> ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

에러를 분석하면, 현재 작성한 'password'라는 패스워드가 mysql에 설정되어 있는 비밀번호 정책에 맞지 않다는 것입니다.

 

MySQL 서버의 비밀번호는 유효기간이나 이력관리를 통한 재사용 금지 기능뿐만 아니라, 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않도록 금칙어를 설정할 수 있습니다.

MySQL 서버에서 비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 사용해야 합니다.

 

비밀번호 정책은 크게 3가지가 있으며, 기본은 MEDIUM입니다.

 

- LOW: 비밀번호의 길이만 검증
- MEDIUM: 비밀번호의 길이를 검증, 숫자와 대소문자, 특수문자의 배합을 검증
- STRONG: MEDIUM 레벨의 검증을 모두 수행, 금칙어의 포함 여부 검증 

 

현재, Mysql에서 medium으로 설정되어 있기 때문에 low로 바꾸고 다시 유저를 생성하면, 에러를 해결할 수 있습니다.

 

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      |
| 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      |
+--------------------------------------+--------+

//mysql8 기준
set global validate_password.policy=LOW;
set 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             | LOW    |
| validate_password.special_char_count | 1      |
| 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      |
+--------------------------------------+--------+

이후 다시 root 계정으로 계정 등록을 하면, 계정이 정상적으로 등록됩니다.

mysql> create user 'kose1'@'%'
    -> identified with 'mysql_native_password' by 'password'
    -> require none
    -> password expire interval 30 day
    -> account unlock
    -> password history default
    -> password reuse interval default
    -> password require current default;
Query OK, 0 rows affected (0.04 sec)

 

--> 계정 생성에 사용한 설정 정보는 다음과 같은 역할을 수행합니다.

 

● IDENTIFIED WITH

 

- 사용자 인증 방식과 비밀번호를 설정

- mysql 서버의 기존 인증 방식을 사용하고자 한다면, identified by 'password'형식으로 명시

 

 

● REQUIRE

 

- mysql 서버에 접속할 때, 암호화된 SSL/TLS 채널을 사용할지 여부를 설정

- 만약 require 옵션을 ssl로 설정하지 않았더라도, identified with에서 사용하는 사용자 인증방식을 Caching SHA-2 Authentication(mysql8 default) 인증 방식을 사용하면 암호화된 채널만으로 MySql 접속이 가능

 

 

● PASSWORD EXPIRE

 

- 비밀번호의 유효 기간을 설정하는 옵션

- 별도로 명시하지 않으면 defaulr_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정

 

 

● PASSWORD HISTORY

 

- 한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션

- password history default: password_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장

- password history n: 비밀번호의 이력을 최근 n개까지만 저장

 

● PASSWORD REUSE INTERVAL

 

- 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정

- 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정

- password reuse interval default: 시스템 변수에 기입된 기간으로 설정

- password reuse interval n day: n일자 이후에 비밀번호를 재사용할 수 있도록 설정

 

● PASSWORD REQUIRE

 

- 비밀번호가 만료되어 새로운 비밀번호로 변경할 때, 현재 비밀번호를 필요로 할지 말지를 결정하는 옵션

- 별도로 명시되지 않으면 password_require_current 시스템 변수값으로 설정

- password require current: 비밀번호를 변경할 때, 현재 비밀번호를 먼저 입력하도록 설정

- password require optional: 비밀번호를 변경할 때, 현재 비밀번호를 입력하지 않아도 되도록 설정

- passowrd require default: password_require_current: 시스템 변수의 값으로 사용

 

● ACCOUNT ROCK / UNLOCK

 

- 계정 새성 시 또는 alter user 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부를 결정

- account rock: 계정을 사용하지 못하도록 잠금

- account unlock: 잠긴 계정을 다시 사용 가능한 상태로 잠금 해제

 

 

2. DB 권한 부여하기

현재, 저의 MySQL에는 Employees라는 테이블이 구성되어 있습니다. 새로 생성한 계정에 select, insert, update 권한을 부여하기 위해  다음과 같은 명령어를 수행할 수 있습니다.

mysql> grant select, insert, update on employees.* to 'kose1'@'%';
Query OK, 0 rows affected (0.02 sec)

각 계정에 따라 다른 권한을 부여할 수 있는 것을 확인하기 위해 새로운 계정을 생성하고 권한을 부여하면 다음과 같습니다.

mysql> create user 'kose2'@'%'
    -> identified with 'mysql_native_password' by 'password'
    -> require none
    -> password expire interval 30 day
    -> account unlock
    -> password history default
    -> password reuse interval default
    -> password require current default;

mysql> grant select on employees.* to 'kose2'@'%';
Query OK, 0 rows affected (0.02 sec)

 

 

--> 생성한 계정으로 로그인하여 권한 적용 확인하기

mysql -u kose1 -p
Enter password:
//(아까 생성할 때 사용한 password 입력)

root 계정에서, kose1에 접근 가능한 권한을 select, insert, update만 부여했으므로,

delete 명령어는 실행되지 않는 것을 확인할 수 있습니다.

 

 

3. 역할(ROLE) 부여하기

MySQL에서는 '역할'을 담당하는 계정을 생성할 수 있는데, 이는 사용자 계정에 권한 부여의 목적으로 사용이 됩니다.

mysql> create ROLE
    -> role_emp_read,
    -> role_emp_write;
Query OK, 0 rows affected (0.04 sec)

mysql> grant select on employees.* to role_emp_read;
Query OK, 0 rows affected (0.04 sec)

mysql> grant insert, update, delete on employees.* to role_emp_write;
Query OK, 0 rows affected (0.02 sec)

 

생성한 kose1, kose2의 계정에 각각 다른 권한을 부여하면 다음과 같습니다.

mysql> grant role_emp_read, role_emp_write to 'kose1'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> grant role_emp_read to 'kose2'@'%';
Query OK, 0 rows affected (0.04 sec)

이때, 특정 계정을 로그아웃하면, root 계정에서 설정한 권한이 수행되지 않는 상황이 발생하였습니다.

이는 MySQL에서 설정한 권한 정보가 로그인 상태에서만 유효하도록 설정되어 있기 때문입니다.

이를 로그아웃되더라도 유지하도록 설정하려면 다음과 같은 명령어를 작성하면 됩니다.

-> root 계정
mysql> set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.00 sec)

 

--> 권한 적용 확인하기

mysql> delete from departments where dept_no = 'd001';
ERROR 1142 (42000): DELETE command denied to user 'kose2'@'localhost' for table 'departments'

-> kose1 계정
mysql> delete from departments where dept_no = 'd001';
Query OK, 1 row affected (0.02 sec)

 

 

read (select) 권한만 부여 받은 kose2 계정은 delete 명령어를 수행하면 명령이 거부된 것을 확인할 수 있습니다.

반면, kose1 계정은 CRUD가 모두 적용된 기능을 수행할 수 있는 것을 확인할 수 있습니다.

 

역할 테이블을 생성하는 이유는 무엇일까?
역할과 계정은 내외부적으로 동일한 객체인데, CREATE ROLE 명령과 CREATE USER 명령을 구분하는 이유는
계정 생성과 권한 부여가 모두 가능한 사용자와 권한 부여만 가능한 사용저를 분리하여, 보안을 높이기 위함입니다.

 

이상으로, MySQL에서 계정을 생성하고 권한을 부여하는 과정을 정리하는 글을 마치도록 하겠습니다.

자세한 사항은 Real MySQL에서 확인하실 수 있습니다.

감사합니다.!

 

출처: Real MySQL (백은빈님, 이성욱 님 지음)