[Real MySQL 8.0]MySQL 8.0과 Docker - 비밀번호 관리와 권한 그리고 역할

비밀번호 관리

고수준 비밀번호

MySQL은 보안에 취약한 패스워드를 설정할 수 없게 글자의 조합을 강제하거나 금칙어를 설정할 수 있다. MySQL 서버에서 비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 된다. validate_password 컴포넌트는 사용하기 위해선 우선적으로 설치해야 한다.

https://dev.mysql.com/doc/refman/8.4/en/validate-password.html

MySQL :: MySQL 8.4 Reference Manual :: 8.4.3 The Password Validation Component

MySQL 서버 프로그램에 내장되어 있기 때문에 INSTALL COMPONENT 명령 중 file:// 부분에서 별도로 디렉토리를 설정할 필요는 없다.

 

validate_password 컴포넌트 설치

mysql> INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.06 sec)

 

validate_password 컴포넌트 삭제

mysql> UNINSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.11 sec)

 

설치된 컴포넌트 확인

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|            1 |                  1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)

 

validate_password 컴포넌트에서 제공하는 시스템 변수 확인

mysql> SHOW GLOBAL VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| 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      |
+-------------------------------------------------+--------+
8 rows in set (0.00 sec)

 

비밀번호 정책은 3가지로 나뉘며 기본은 MEDIUM이다.

• LOW: 비밀번호의 길이만 적중
• MEDIUM: 비밀번호의 길이를 검증 및 숫자와 대소문자 그리고 특수문자의 배합을 검증
• STRONG: MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함됐는지 여부까지 검증

validate_password.length: 비밀번호의 길이

validate_password.mixed_case_count, validate_password.number_count: 숫자와 대소문자, 특수문자

validate_password.special_char_count: 설정한 글자 수 이상을 포함하고 있는지 검증

validate_password.dictionary_file: 시스템 변수에 설정된 사전 파일에 명시된 단어를 포함하고 있는지 검증

 

금칙어 파일은 금칙어들이 한 줄씩 기록된 텍스트 파일이다. 일반적으로 이미 존재하는 파일을 다운로드하여 커스텀한다.

예시 SecLists: https://github.com/danielmiessler/SecLists/blob/master/Passwords/Common-Credentials/10k-most-common.txt

SecLists/Passwords/Common-Credentials/10k-most-common.txt at master · danielmiessler/SecLists

password
123456
12345678
1234
qwerty
...
eighty
epson
evangeli
eeeee1
eyphed

 

파일을 등록하려면 MySQL 서버에 파일을 등록하면 된다. 비밀번호 금칙어는 validate_password.policy 시스템 변수가 'STRONG'으로 설정된 경우에만 가능하다.

SET GLOBAL validate_password.dictionary_file='prohibitive_word.data';
SET GLOBAL validate_password.policy='STRONG';

 

 

MySQL 5.7 버전까지는 validate_password가 플러그인 형태로 제공됐지만 MySQL 8.0부터는 컴포넌트로 제공된다. 사용자 측면에서 플러그인이나 컴포넌트 모두 거의 동일하지만, 시스템 변수의 이름에 차이가 있다. 플러그인 단점을 보완하기 위해 도입됐다.

-> 컴포넌트의 경우 서버가 실행 중일 때 로드 및 언로드가 가능하다. MySQL 서버에서 더 일관되게 작동한다.

https://dev.mysql.com/doc/refman/8.4/en/validate-password-options-variables.html

MySQL :: MySQL 8.4 Reference Manual :: 8.4.3.2 Password Validation Options and Variables

 

이중 비밀번호

일반적으로 응용 프로그램 서버들의 경우 공용으로 데이터베이스 서버를 사용한다. 데이터베이스 서버의 계정 정보는 응용 프로그램 서버에서 공유하여 사용되는 경우가 많다. 데이터베이스 계정의 비밀번호는 서버가 기동중일 시 변경이 불가능하여 처음 설정한 상태로 몇 년 동안 사용되는 경우가 많다. 8.0 버전부터는 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능을 추가하였다.(2개 중 하나만 일치하면 로그인이 통과되는 것)
https://dev.mysql.com/doc/refman/8.4/en/password-management.html#dual-passwords

MySQL :: MySQL 8.4 Reference Manual :: 8.2.15 Password Management

 

2개의 비밀번호는 다음과 같이 구분된다.

 

• Primary
  최근에 설정된 비밀번호
• Secondary
  이전에 설정된 비밀번호

이중 비밀번호를 사용하기 위해서는 다음과 같이 설정하면 된다.

mysql> ALTER USER 'root'@'localhsot' IDENTIFIED BY 'old_password';
mysql> ALTER USER 'root'@'localhsot' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

첫 번째 쿼리문의 경우 프라이머리 비밀번호는 'old_password', 세컨더리 비밀번호는 공란이다.
두 번째 쿼리문의 경우 프라이머리 비밀번호는 'new_password', 세컨더리 비밀번호는 이전에 설정한 'old_password'가 된다.

데이터베이스를 재기동할 필요 없이 응용 프로그램만 배포한 뒤, 재시작하면 된다. 재시작 후, 계정의 보안을 위해 세컨더리 비밀번호는 삭제하는 것이 좋다. 

 

세컨더리 비밀번호 삭제

mysql> ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.03 sec)

 

권한

~MySQL 5.7

권한은 다음 2개로 구분된다.

• 글로벌 권한
  데이터베이스나 테이블 이외의 객체에 적용되는 권한
• 객체 권한
  데이터베이스나 테이블을 제어하는 데 필요한 권한

객체 권한은 GRANT 명령으로 권한을 부여할 때 특정 객체를 명시해야 한다. 글로벌 권한은 GRANT 명령으로 권한을 부여할 때 특정 객체를 명시하지 않는다.

 

ALL(또는 PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용될 수 있다. 
특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여하며, 글로벌로 ALL이 사용되면 글로벌 수준(모든 데이터베이스와 객체)에서 가능한 모든 권한을 부여하게 된다.

 

MySQL 8.0~
기존 버전에 동적 권한이 추가되었다. 5.7부터 제공되었던 권한은 정적 권한이라 한다.

정적 권한과 동적 권한 범위는 다음과 같다.
https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html

MySQL :: MySQL 8.4 Reference Manual :: 8.2.2 Privileges Provided by MySQL

 

• 정적 권한
  MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한을 의미한다.
  
  
• 동적 권한
  MySQL 서버가 시작되면서 동적으로 생성하는 권한을 의미한다. (ex. MySQL 서버의 컴포넌트나 플러그인이 설치되면 그때 등록되는 권한을 동적 권한이라고 한다.)
  5.7 버전까지는 SUPER라는 권한이 데이터베이스 관리를 위해 꼭 필요한 권한이었지만, SUPER 권한은 잘게 쪼개어져 동적 권한으로 분산됐다. 그 결과로 8.0 버전부터는 백업 관리자와 복제 관리자 각각 개별로 꼭 필요한 권한만 부여할 수 있게 된 것이다.

 

 

GRANT

https://dev.mysql.com/doc/refman/8.4/en/grant.html

MySQL :: MySQL 8.4 Reference Manual :: 15.7.1.6 GRANT Statement

사용자에게 권한을 부여할 때는 GRANT 명령을 사용한다. GRANT 명령은 각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀐다.

 

기본 쿼리문

mysql> GRANT privilege_list ON db.table TO 'user'@'host';

 

MySQL 8.0~

GRANT 시 생성하지 않은 사용자에 GRANT 명령을 실행하면 에러가 발생한다. 사용자를 먼저 생성한 후, GRANT 명령으로 권한을 부여한다. 

-- 생성한 적 없는 사용자에게 GRANT 명령을 실행 한 결과
mysql> GRANT SELECT ON employees.* TO 'test'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

 

GRANT OPTION 권한(사용자가 부여받은 권한을 다른 사용자에게도 할당할 수 있도록 하는 권한)은 GRANT 명령의 마지막에 WITH GRANT OPTION을 명시해서 부여한다.

mysql > GRANT SELECT, INSERT, ... , ON mydb.* TO 'user'@'host' WITH GRANT OPTION;

 

TO 키워드 뒤에는 권한을 부여할 대상 사용자를 명시, ON키워드 뒤에는 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정할 수 있다. 권한의 범위에 따라 사용하는 방법이 달라진다.

 

그전에 임시로 계정을 생성한다.

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

mysql> SELECT user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| test             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

 

 

글로벌 권한

글로벌 권한은 특정 DB나 테이블에 부여될 수 없고 전체에 부여된다. 글로벌 권한을 부여할 때 GRANT 명령의 ON절에는 항상 *.*를 사용하게 된다. (ex. CREATE USER나 CREATE ROLE과 같은 글로벌 권한은 DB 단위나 오브젝트 단위로 부여할 수 있는 권한이 아니므로 항상 *.*로만 대상을 사용할 수 있다.)

mysql> GRANT SUPER ON *.* TO 'test'@'localhost';

 

 

DB 권한

DB 권한은 특정 DB(db.*)나 서버에 존재하는 모든 DB에 권한(*.* 가능)을 부여할 수 있다. 여기서 DB는 스토어드 프로그램들도 포함된다. 하지만, DB 권한만 부여하는 경우 테이블에 대해 부여할 수 없다.(ex. employees.department 불가능)
-> 데이터베이스 단위로 권한을 부여하면, 개별 테이블에 대한 추가적인 권한을 따로 설정하는 건 권장되지 않음

mysql > GRANT EVENT ON *.* TO 'test'@'localhost'; -- 전체 권한
mysql > GRANT EVENT ON employees.* TO 'test'@'localhost'; -- employees 데이터베이서 권한

 

 

테이블 권한

서버에 존재하는 모든 DB에 대한 권한, 특정 DB에 대한 권한, 특정 DB의 특정 테이블에 대한 권한이 가능하다. 하지만, 특정 테이블의 특정 칼럼에 대한 권한 부여는 불가능하다.

-- 서버의 모든 DB에 대한 권한을 부여 가능
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'test'@'localhost';

-- 특정 DB의 오브젝트에 대해서만 권한 부여 가능
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'test'@'localhost';

-- 특정 DB의 특정 테이블에 대해서만 권한 부여 가능
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON employees.departments TO 'test'@'localhost';

 

 

칼럼 권한

INSERT, UPDATE, SELECT만 가능하다. 테이블이나 칼럼 단위의 권한을 잘 사용되지 않음 칼럼 단위의 권한이 하나만 설정되어도, 모든 테이블의 모든 칼럼의 권한을 체크하기 때문이다. 성능적으로 영향을 미칠 수 있다.

mysql> GRANT SELECT, INSERT, UPDATE(dept_name) ON employees.department TO 'user'@'localhost';

 

 

칼럼 단위의 접근 권한이 필요할 경우, GRANT 명령보다는 테이블에서 권한을 허용하고자 하는 칼럼만으로 별도의 뷰(VIEW)를 만들어보는 것도 생각하면 좋다.

뷰도 하나의 테이블로 인식되기 때문에 뷰를 만들어 두면 뷰의 칼럼에 대해 권한을 체크하지 않고 뷰 자체에 대한 권한만 체크하게 된다. -> VIEW를 만들어 필요한 칼럼만 포함 후, VIEW에 대한 권한 부여한다.

 

SHOW GRANTS을 이용해서도 권한을 확인할 수 있지만, mysql DB 권한 관련 테이블을 참조하면 된다.
https://dev.mysql.com/doc/refman/8.4/en/grant-tables.html

MySQL :: MySQL 8.4 Reference Manual :: 8.2.3 Grant Tables

 

• mysql.user
  (정적 권한) 계정 정보 & 계정이나 역할에 부여된 글로벌 권한
• mysql.db
  (정적 권한) 계정이나 역할에 DB 단위로 부여된 권한
• mysql.tables_priv
  (정적 권한) 계정이나 역할에 테이블 단위로 부여된 권한
• mysql.columns_priv
  (정적 권한) 계정이나 역할에 칼럼 단위로 부여된 권한
• mysql.procs_priv
  (정적 권한) 계정이나 역할에 스토어드 프로그램 단위로 부여된 권한
• mysql.global_grants
  (동적 권한) 계정이나 역할에 부여되는 동적 글로벌 권한

 

ROLE

MySQL8.0~
여러 개의 권한을 역할(Role)로 사용할 수 있다.

 

현재 사용자는 다음과 같다.

mysql> SELECT user, host password FROM mysql.user;
+------------------+-----------+
| user             | password  |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

 

role_emp_read, role_emp_write 롤을 만든다.

mysql> CREATE ROLE role_emp_read, role_emp_write;
Query OK, 0 rows affected (0.02 sec)

 

role_emp_read에게는 SELECT 권한을 role_emp_write에게는 INSERT, UPDATE, DELETE 권한을 부여한다.

mysql> GRANT SELECT ON employees.* TO role_emp_read;
mysql> GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

 

reader와 writer라는 사용자를 생성한다.

mysql> CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
mysql> CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'qwerty';

 

reader에게는 role_emp_read의 권한을 writer에게는 role_emp_read와 role_emp_write의 권한을 부여한다.

mysql> GRANT role_emp_read TO reader@'127.0.0.1';
mysql> GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

 

실제로 reader유저로 로그인 후 권한을 확인해 보면 부여된 권한이 잘 나와있다.

root@9d60e77808ad:/# mysql -ureader -h 127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

 

권한 확인

mysql> SHOW GRANTS;
+---------------------------------------------------+
| Grants for reader@127.0.0.1                       |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `reader`@`127.0.0.1`        |
| GRANT `role_emp_read`@`%` TO `reader`@`127.0.0.1` |
+---------------------------------------------------+
2 rows in set (0.00 sec)

 

권한이 부여되었지만 실제 조회 시 에러가 나타난다.

mysql> SELECT * FROM employees.employees LIMIT 100;
ERROR 1142 (42000): SELECT command denied to user 'reader'@'127.0.0.1' for table 'employees'

 

계정의 활성화된 역할을 조회해 보면 NONE이라고 뜬다.(role_emp_read 역할도 나타나지 않는다.)

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

 

reader 계정에서 role_emp_read 역할을 사용하려면 우선 활성화해야 한다.(SET ROLE 명령어)

mysql> SET ROLE 'role_emp_read';
Query OK, 0 rows affected (0.00 sec)

 

이후 SELECT시 결과가 나오는 것을 확인할 수 있다.

mysql> SELECT COUNT(*) FROM employees.employees;
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.17 sec)

 

MySQL은 자동으로 역할을 활성화하지 않게 설정 돼있다.
https://stackoverflow.com/questions/71702455/why-wouldnt-you-set-activate-all-roles-on-login
SQL3 표준에서는 사용자가 한 번에 하나의 역할만 가질 수 있게 설계되었다고 한다. 그래서 모든 역할의 권한을 동시에 가지는 것이 아닌, SET ROLE을 사용하여 필요한 역할을 하나씩 선택하는 것이 일반적이라고 한다.
ex) 백업 role은 데이터 백업이 목적이기 때문에 read의 권한만 허용하고 write의 권한을 허용하는 것은 바람직하지 않다.
한 명의 사용자가 모든 권한을 갖지 않도록 분리하는 것은 보안적으로 중요하다는 것이 가장 큰 이유다. SOX(Sarbanes-Oxley Act) 준수의 보안 원칙과 유사하다고 한다.

 

activate_all_roles_on_login 시스템 변수로 로그인 시 역할 활성화 여부를 설정할 수 있다.(SUPER나 SYSTEM_VARIABLE_ADMIN 권한을 가져야 한다.)

root@9d60e77808ad:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> SET GLOBAL activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)

 

실제로 MySQL 내부적으로는 권한과 계정은 동일한 취급을 받는다. mysql DB의 user 테이블에서 권한과 사용자 계정이 구분 없이 저장되어 있다.

mysql> SELECT user, host, account_locked FROM mysql.user;
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| role_emp_read    | %         | Y              |
| role_emp_write   | %         | Y              |
| root             | %         | N              |
| reader           | 127.0.0.1 | N              |
| writer           | 127.0.0.1 | N              |
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
| root             | localhost | N              |
+------------------+-----------+----------------+
9 rows in set (0.01 sec)

 

역할과 계정의 차이는 단지 account_locked 칼럼의 값이 다르다 뿐이다.
CREATE USER 명령으로 계정을 생성할 때는 reader@'127.0.0.1'과 같이 계정 이름과 호스트 부분을 명시하지만 CREATE ROLE 명령으로 역할을 생성할 경우 호스트 부분을 별도로 지정하지 않다. 사실 별도로 지정을 하지 않을 시 자동으로 '%' 모든 호스트가 추가된다. 계정 역시 이름만 명시할 경우 자동으로 '%' 모든 호스트가 추가된다.

 

기존에 만들어둔 계정 reader에 역할 role_emp_local_read을 부여한다.

-- role_emp_local ROLE 생성
mysql> CREATE ROLE role_emp_local_read@localhost;
Query OK, 0 rows affected (0.03 sec)

-- role_emp_local 권한 부여
mysql> GRANT SELECT ON employees.* TO role_emp_local_read@'localhost';
Query OK, 0 rows affected, 1 warning (0.02 sec)

-- 사용자에게 권한 부여
mysql> GRANT role_emp_local_read@'localhost' TO reader@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

 

사실 역할의 호스트 부분은 직접 로그인해서 사용하는 용도가 아니라면 중요한 부분이 아니다.
계정 reader와 역할 role_emp_local_read는 각각 다른 호스트(127.0.0.1과 localhost)를 갖고 있지만 사실 이때 부여될 때에는 역할만 부여되므로 호스트 부분에는 영향을 끼치지 않는다.

 

CREATE USER와 CREATE ROLE 명령을 분리한 이유는, 데이터베이스 관리 직무를 분리하여 보안을 강화하는 용도이기 때문이다. CREATE USER 명령에는 권한이 없지만, CREATE ROLE 명령에는 권한을 가진 사용자가 된다. 실제 로그인은 불가능하게 역할에는 account_locked 칼럼이 'Y'로 설정되어있어 있다.

 

계정에 부여된 역할은 SHOW GRANTS 또는 mysql DB의 권한 관련 테이블을 확인하면 된다.

• mysql.default_roles
  계정별 기본 역할
• mysql.role_edges
  역할에 부여된 역할 관계 그래프