비밀번호 관리
고수준 비밀번호
MySQL 서버의 비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능 뿐만 아니라, 비밀번호를 쉽게 유추?할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 있다.
비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 사용하면 된다. 그러기 위해선 validate_password 컴포넌트를 설치해야하고, 이 컴포넌트는 MySQL 서버 프로그램에 내장되어 있기 때문에 바로 INSTALL 명령어로 설치하면 된다.(INSTALL COMPONENT 명령어는 대부분 file:// 경로 내부에 있다)
이렇게 validate_password 컴포넌트를 설치하고 나서, 해당 컴포넌트에서 제공하는 시스템 변수를 한번 살펴보자.
위의 스크린샷을 보면 validate_password.policy 시스템 변수에 MEDIUM 라는 값이 들어가 있다.
- LOW : 비밀번호의 길이만 검증
- MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자, 특수문자의 배합을 검증
- STRONG : MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함되어 있는지 여부까지 검증
비밀번호의 길이는 validate_password.length 시스템 변수에 설정된 길이 이상의(위의 스크린샷 같은 경우는 8) 비밀번호가 되었는지 검증한다.
숫자, 대소문자, 특수문자는 validate_password.mixed_case_count와 validate_password.number_count, validate_password.special_char_count 시스템 변수에 설정된 글자 이상을 포함하고 있는지 검증한다.
그리고 금칙어는 validate_password.dictionary_file 시스템 변수에 있는 값을 포함하고 있는지를 검증한다. 위의 스크린샷에는 아직 값이 없다.
그러면 어떻게 해당 변수에 금칙어로 사용하고 싶은 값을 설정할까? 간단하다. 금칙어로 사용하고 싶은 단어들을 모은 파일을 준비하면 된다. 하지만 내가 금칙어로 사용하고 싶은 단어들을 모은 파일을 직접 준비하는 것은, 만약 내가 금칙어로 사용하고 싶은 값이 많다면 상당히 귀찮을 것이다. 그래서 이미 만들어진 금칙어 파일을 내려받아, 필요한 걸 사용하면 된다.
해당 경로에 들어가면 아래와 같은 파일을 볼 수 있다.
그리고 예를 들어 /etc/mysql/restricted_words.txt에 저장했다고 가정한다면, 아래와 같이 해당 파일에 들어있는 금칙어들을 적용할 수 있다.
mysql> SET GLOBAL validate_password.dictionary_file = '/etc/mysql/restricted_words.txt';
mysql> SET GLOBAL validate_password.policy='STRONG';
mysql> SHOW VARIABLES LIKE 'validate_password.dictionary_file';
//설정이 적용되었는지 확인
그리고 중요한 것은 비밀번호 금칙어는 아까 위에서 봤던 스크린샷 중에서 valdiate_password.policy 시스템 변수(위에는 MEDIUM)가 STRONG으로 설정된 경우에만 작동을 하기 때문에 위의 명령어의 두번째 명령어처럼 해당 시스템 변수의 값을 STRONG으로 변경해야 한다.
이중 비밀번호
많은 응용 프로그램 서버들이 공용으로 데이터베이스 서버를 사용할 수 있다. 이러한 구현 특성으로 인해 데이터베이스 서버의 계정 정보는 쉽게 변경하기 어려운데, 그중에서도 데이터베이스의 계정의 비밀번호는 서비스가 실행 중인 상태에서 변경이 불가능 했다. 하지만 이와 같은 문제점을 해결하기 위해서 MySQL 8.0 버전부터는 계정의 비밀번호를 2개의 값을 동시에 사용할 수 있는 기능이 추가되었다. 이걸 바로 ‘이중 비밀번호(Dual Password)’라고 한다.
MySQL 서버의 이중 비밀번호 기능은 하나의 계정에 2개의 비밀번호를 설정할 수 있다고 하였는데, 이때 이 2개의 비밀번호는 프라이머리(Primary)와 세컨더리(Secondary)로 구분이 된다. 내가 최근에 설정한 비밀번호는 프라이머리 비밀번호이고, 이전 비밀번호는 세컨더리 비밀번호가 된다.
이중 비밀번호를 사용하고 싶다면 아래와 같이 기존 비밀번호 변경 명령어에 RETAIN CURRENT PASSWORD 옵션만 추가하면 된다.
//비밀번호를 'adlfk'로 설정
mysql> ALTER USER 'user'@'localhost' IDENTIFIED BY 'old_password';
//비밀번호를 'qwerty'로 변경하면서 기존 비밀번호를 세컨더리 비밀번호로 설정
mysql> ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;첫번째 명령어를 실행하면 user 계정의 프라이머리 비밀번호는 old_password로 변경되고 세컨더리 비밀번호는 비어있는 상태가 된다.
두번째 명령어를 실행하면 이전 비밀번호였던 old_password는 세컨더리 비밀번호가 되고, 새롭게 설정한 new_password가 프라미어리 비밀번호가 된다.
즉, 이 이후로는 프라이머리, 세컨더리 비밀번호 모두로 로그인이 가능하다.
이 상태에서 데이터베이스에 연결하는 응용 프로그램의 소스코드나 설정 파일의 비밀번호를 새로운 비밀번호인 new_password로 변경하고 배포 및 재시작을 순차적으로 실행한다.
MySQL 서버에 접속하는 모든 응용 프로그램의 재시작이 되면 아래 명령어로 세컨더리 비밀번호를 삭제한다. 꼭 이걸 삭제해야할 필요는 없지만, 보안을 위해서 삭제하는 것이 좋다고 한다.
mysql> ALTER USER 'user'@'localhost' DISCARD OLD PASSWORD;이렇게 해서 세컨더리 비밀번호를 삭제할 수 있고, 새로운 비밀번호로만 로그인을 할 수가 있는 것이다.
권한
5.7버전까지는 글로벌 권한과 객체 단위의 권한을 구분을 했었다.
- 글로벌 권한
- 데이터베이스나 테이블 이외의 객체에 적용되는 권한
- GRANT 명령으로 권한을 부여할 때, 특정 객체를 명시하지 말아야 한다.
- 객체 권한
- 데이터베이스나 테이블을 제어하는 데 필요한 권한
- GRANT 명령으로 권한을 부여할 때, 반드시 특정 객체를 명시해야 한다.
- ALL(ALL PREVILEGES)
- 글로벌과 객체 권한 두 가지 용도로 사용이 된다.
- 특정 객체에 ALL 권한이 부여가 된다면, 해당 객체에 적용될 수 있는 모든 객체 권한을 부여한다는 뜻이다.
- 글로벌로 ALL 권한이 부여가 된다면, 글로벌 수준에서 가능한 모든 권한을 부여한다는 뜻이다.
MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한을 ‘정적 권한’ 이라고 하며, MySQL 서버가 시작되면서 동적으로 생성하는 권한을 ‘동적 권한’ 이라고 한다. 예를 들어서, MySQL 서버의 컴포넌트나 플러그인이 설치되면 그때 등록되는 권한들을 동적 권한이라고 하는 것이다.
책에 정적 권한 표와 동적 권한 표, 그리고 이것들을 객체 권한과 글로벌 권한, 객체&글로벌 권한으로 나눈 표들이 있는데 이 부분은 생략하겠다.
사용자에게 권한을 부여할 때는 GRANT 명령을 사용한다. GRANT 명령은 각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.
mysql> GRANT privilege_list ON db.table TO 'user'@'host;MySQL 8.0 버전부터는 존재하지 않는 사용자에게 GRANT 명령이 실행되면 에러가 발생한다. 그래서 무조건 사용자를 먼저 생성하고 권한을 부여해야 한다. 위의 명령어에서 ON은 어떤 DB의 어떤 오브젝트에 권한을 부여할지를 결정하기 위함이다.
글로벌 권한
mysql> GRANT SUPER ON *.* TO 'user'@'localhost';글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에, 글로벌 권한을 부여할 때 GRANT 명령의 ON 절에는 항상 . 를 사용하게 된다. . 은 모든 DB의 모든 오브젝트(테이블과 스토어드 프로시저나 함수 등)를 포함해서 MySQL 서버 전체를 의미한다. CREATE USER, CREATE ROLE과 같은 글로벌 권한은 DB 단위나 오브젝트 단위로 부여할 수 있는 권한이 아니므로, 항상 . 로만을 대상으로 사용할 수 있다.
DB 권한
mysql> GRANT EVENT ON *.* TO 'user'@'localhost';
mysql> GRANT EVENT ON employees.* TO 'user'@'localhost';DB 권한은 특정 DB에 대해서만 권한을 부여하거나, 서버에 존재하는 모든 DB(스토더어 프로그램 포함)에 대해 권한을 부여할 수 있기 때문에 위의 명령어와 같이 ON 절에 . 혹은 employees.* 이렇게 사용할 수 있다.
하지만 DB 권한만 부여하는 경우에는(DB 권한은 테이블에 대해서 부여할 수 없기 때문에) employees.department와 같이 테이블까지 명시할 수는 없다.
DB 권한은 서버의 모든 DB에 적용할 수 있으므로 대상에 . 를 사용할 수 있다.
또한 특정 DB에 대해서만 권한을 부여하는 것도 가능하기 때문에, db.* 로 대상을 설정하는 것도 가능하다.
하지만 오브젝트 권한처럼 db.table로 오브젝트(테이블)까지 명시할 수는 없다.
테이블 권한
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.* TO 'user'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON employees.department TO 'user'@'localhost';- 첫번째 명령어 : 서버의 모든 DB에 대해 권한을 부여
- 두번째 명령어 : 특정 DB의 오브젝트에 대해서만 권한을 부여
- 세번째 명령어 : 특정 DB의 특정 테이블에 대해서만 권한을 부여
컬럼 권한
컬럼에 부여할 수 있는 권한은 DELETE를 제외한 나머지(INSERT, UPDATE, SELECT) 3가지이다. 각 권한 뒤에 컬럼을 명시하는 형태로 권한을 부여하며, employees DB의 department 테이블에서 dept_name 컬럼만 업데이트할 수 있게 권한을 부여하려면 아래와 같이 명령어를 사용하면 된다.
mysql> GRANT SELECT,INSERT,UPDATE(dept_name) ON employees.department TO 'user'@'localhost';위와 같은 명령어는 dept_name 컬럼에만 UPDATE를 수행하고, 모든 컬럼에 대해서는 SELECT, INSERT를 수행할 수 있다는 뜻이다.
하지만! 사실 테이블이나 컬럼 단위의 권한 부여는 잘 사용하지 않는다고 한다. 컬럼 단위의 권한이 하나라도 설정되면, 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 하기 때문에, 성능에 영향이 갈 수 있다.
컬럼 단위의 권한 접근은 정말 필요한게 아니라면, GRANT를 사용하기 보다는 테이블에서 허용하고자 하는 컬럼에 대해서만 별도의 View를 만들어서 사용하는 방법도 있다.
뷰(View)도 하나의 테이블로 인식이 된다. 그래서 뷰를 만들면 뷰의 컬럼에 대해 권한 체크를 하지 않고, 뷰 자체에 대해서만 권한 체크를 하기 때문에 더 이점이 있을 수 있다.
| 저장소 테이블 | 설명 | |
|---|---|---|
| 정적권한 | mysql.user | 계정 정보 & 계정이나 역할에 부여된 글로벌 권한 |
| 정적권한 | mysql.db | 계정이나 역할에 DB 단위로 부여된 권한 |
| 정적권한 | mysql.tables_priv | 계정이나 역할에 테이블 단위로 부여된 권한 |
| 정적권한 | mysql.columns_priv | 계정이나 역할에 컬럼 단위로 부여된 권한 |
| 정적권한 | mysql.procs_priv | 계정이나 역할에 스토어드 프로그램 단위로 부여된 권한 |
| 동적권한 | mysql.global_grants | 계정이나 역할에 부여되는 동적 글로벌 권한 |
역할
8.0버전부터 권한을 묶어서 역할을 사용할 수 있게 됐다.
mysql> CREATE ROLE
role_emp_read,
role_emp_write;우선 CREATE ROLE 명령어을 통해 껍데기 역할만을 정의한 것이고, 그리고 이제 GRANT 명령을 통해 각 역할에 대해서 실질적인 권한을 부여하면 된다.
역할의 이름을 보면 현재, role_emp_read 역할에는 employees DB의 모든 객체에 대해서 SELECT(읽기) 권한만 부여했고, role_emp_write 역할에는 employees DB의 모든 객체에 대해 데이터 변경(INSERT, UPDATE, DELETE) 권한을 부여했다.
mysql> GRANT SELECT ON employees.* TO role_emp_read;
mysql> GRANT INSERT,UPDATE,DELETE ON employees.* TO role_emp_write;역할 자체로만 사용은 할 수 없으며, 계정에 부여해야 하기에 CREATE USER 명령으로 reader와 writer 라는 계정을 생성한다.
mysql> CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'asdf';
mysql> CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'asdf';CREATE USER 명령으로 계정을 생성하고, 아직 이 계정들에 권한이 부여되지 않았다.
그래서 employees DB에 어떤 쿼리도 실행할 수가 없다. 이제 GRANT 명령으로 reader와 writer 계정에 역할을 부여하면 된다.
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 계정에는 role_emp_read 역할만 부여했고, writer 계정에는 role_emp_read 와 role_emp_write
역할을 부여했다.
이렇게 reader 계정으로 로그인한 후, SHOW GRANTS 명령어를 통해 계정이 가진 권한을 확인해보니, role_emp_read 역할이 잘 부여가 된 것을 확인할 수 있었다.
reader로 로그인을 한 상태에서, employees DB의 데이터를 조회하거나 변경하려고 하면 위와 같이 에러가 뜬다.
그런데 아까 역할이 잘 부여된 것을 확인했지만, 아래와 같이 계정의 활성화된 역할을 조회했을 때, NONE이 뜨며 역할이 없음을 확인했다.
그렇다면 어떻게 reader 계정이 role_emp_read 역할을 사용하게 할 수 있을까?
SET ROLE 명령어를 통해 역할을 활성화하면 된다.
하지만 이 reader 계정에 로그아웃을 하고 다시 로그인을 해보았다.
로그아웃을 하고 다시 로그인을 해보니 아까 SET ROLE 명령어를 통해 활성화시켰던 역할이 다시 NONE으로 뜨면서 활성화되지 않은 것을 확인할 수 있었다.
그 이유는 일단 역할이 활성화되면, 그 역할이 가진 권한은 사용할 수 있는 상태가 되지만, 계정이 로그아웃되었다가 다시 로그인하면 역할이 활성화되지 않은 상태로 초기화가 되기 때문이다.
사용자가 MySQL 서버로 로그인을 할 때, 역할을 자동으로 활성화할지 여부를 activate_all_roles_on_login 시스템 변수를 통해서 설정을 할 수가 있다. 이 변수를 ON으로 설정하면 매번 SET ROLE 명령으로 역할을 활성화하지 않아도 로그인과 동시에 부여된 역할이 자동으로 활성화된다.
mysql> SET GLOBAL activate_all_roles_on_login=ON;MySQL에서 서버의 역할(Privilege)은 사용자 계정은 동일한 객체로 여겨진다. 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어가 가능해졌을 뿐이고, 아래와 같이 실제 권한과 사용자 계정이 구분 없이 저장된 것을 볼 수 있다.
그렇다면 MySQL 서버는 계정과 권한을 어떻게 구분을 할까? 하지만 하나의 계정에 다른 계정의 권한을 병합하기만 하면 되기 때문에, 역할과 계정을 구분할 필요가 없다.
