책너두 6기 5일차
백은빈, 이성욱의 Real MySQL8.0 1권 p.52 ~ p.64
5일차
03 사용자 및 권한
MySQL에서 사용자 계정을 생성하는 방법이나 각 계정의 권한을 설정하는 방법은 다른 DBMS와 차이 가 있음. MySQL은 ID 뿐 아니라 접속 IP도 확인함. Role로 권한을 묶는 개념이 있음
3.1 사용자 식별
MySQL의 사용자는 계정뿐 아니라 접속 지점(호스트명이나 도메인 또는 IP 주소)도 계정의 일부가 됌. 따라서 계정을 언급할 때 아이디와 호스트를 함께 명시 백슬래시(`)는 식별자를 감싸는 따옴표 역할을 하는데 홑따옴표(‘)로 바뀌어서 사용되기도 함
'svc_id'@'127.0.0.1' : 이 계쩡은 서버가 기동 중인 로컬 호스트에서 svc_id라는 아이디로 접속할 때만 사용될 수 있는 계정
모든 외부에서 접속 가능하도록 하려면 호스트 부분을 % 문자로 대체하면 됌. %는 모든 IP 또는 호스트 명을 의미
'svc_id'@'192.168.0.10' : 이 계정의 비밀번호는 123
'svc_id'@'%' : 이 계정의 비밀번호는 abc
같은 아이디가 두 개 있는 위 상황에서 MySQL은 범위가 가장 작은 것을 항상 먼저 선택한다. 따라서 위의 계정을 먼저 선택하게 되고, 비밀번호를 abc 입력하면 접속이 거절된다.
3.2 사용자 계정 관리
3.2.1 시스템 계정과 일반 계정
이 장에서 사용자의 의미 : MySQL 서버를 사용하는 주체(사람 또는 응용 프로그램)
이 장에서 계정의 의미 : MySQL 서버에 로그인 하기 위한 식별자(로그인 아이디)
system_user권한을 가지느냐에 따라 시스템 계정(System Account)과 일반 계정(Regular Account)으로 나뉜다. 시스템 계정은 데이터베이스 서버 관리자를 위한 계정이며 일반 계정은 응용 프로그램이나 개발자를 위한 계정
관리(생성, 삭제, 변경)
시스템 계정(System_user 권한 O) => 시스템 계정 & 일반 계정 O
일반 계정(System_user 권한 X) => 시스템 계정 X
시스템 계정만 수행할 수 있는 중요 작업
- 계정 관리(계정 생성 및 삭제, 그리고 계정의 권한 부여 및 제거)
- 다른 세션(Connection) 또는 그 세션에서 실행 중인 쿼리를 강제 종료
- 스토어드 프로그램 생성 시 Definer를 타 사용자로 설정
MySQL 서버 내 내장된 계정들 => 삭제 X(어차피 lock이라 하기도 힘듬)
'mysql.sys'@'localhost': sys 스키마의 객체(뷰나 함수, 그리고 프리시저)들의 Definer로 사용되는 계정'mysql.session'@'localhost': MySQL 플러그인이 서버로 접근할 때 사용되는 계정mysql.infoschema'@'localhost':information_schema에 정의된 뷰의 Definer로 사용되는 계정
내 계정을 보려고 mysql 8.0 command line client 를 접속하여 비밀번호를 입력했다.
코드를 입력했다.
select user, host, account_locked from mysql.user where user like 'mysql.%';
+——————+———–+—————-+ | user | host | account_locked | +——————+———–+—————-+ | mysql.infoschema | localhost | Y | | mysql.session | localhost | Y | | mysql.sys | localhost | Y | +——————+———–+—————-+ 3 rows in set (0.01 sec)
책과 똑같이 나온다. 굿.
3.2.2 계정 생성
계정 생성은 create user로, 권한 부여는 grant로 한다. 계정 생성시 다양한 옵션을 설정할 수 있다.
- 계정의 인증 방식과 비밀번호
- 비밀번호 관련 옵션(유효 기간, 이력 개수, 재사용 불가 기간)
- 기본 역할(Role)
- SSL 옵션
- 계정 잠금 여부
일반적으론 아래와 같이 작성한다.
create user 'user'@'%'
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;
3.2.2.1 identified with
사용자의 인증 방식고 비밀번호를 설정. 뒤에는 반드시 인증 방식(인증 플러그인의 이름)을 명시해야 한다. Native Pluggable Authenication, Caching SHA-2 Pluggable Authenication, PAM Pluggable Authenication, LDAP Pluggable authentication 등이 있다. 8.0 에선 Caching이 기본 인증. 이것은 SSL/TLS 또는 RSA 키페어를 필요로 해서 MySQL 5.7 까지의 방식과 다른 방식으로 접속해야 한다. 그래서 기존 버전과 호환성 때문에 Native Authentication으로 생성할 일도 종종 있다. 나도 API 만들때 겪었고.. 이럴땐 다음과 같이 변경하자
set global default_authentication_plugin='mysql_native_password'
혹은 my.ini를 수정하거나..
참고로 Caching 방식은 무차별 대입 공격(brute-force attack)을 어렵게 만들지만 악의 없는 정상 유저나 응용 프로그램의 연결도 느리게 만든다. 정확히 비밀번호를 알떈 괜찮지만 한번에 많ㄹ은 커넥션을 연결하는 경우에는 서버의 CPU 자원을 많이 소모한다.
3.2.2.2 require
서버 접속할때 암호화된 SSL/TLS 채널 사용할지 여부. Caching 인증 방식을 사용하면 설정안해도 자동으로 암호화된 채널로 접속.
3.2.2.3 password expire
비밀번호의 유효기간 설정.
password expire : 계정 생성과 동시에 만료처리
password expire never : 만료 기간 없음
password expire default: default_password_lifetime 이라는 시스템 변수에 저장된 기간으로 만료 설정
password expire interval n day: 오늘부터 n일자 까지 유효 기간으로 설정
3.2.2.4 password history
한 번 사용한 비밀번호 재사용 금지 옵션.
password history default: 시스템 변수 password_history에 저장된 갯수만큼 비밀번호 이력 저장. 이력에 저장된 비밀번호는 사용 불가
password history n: n개 까지 저장
select * from mysql.password_history; 를 입력하면 mysql DB의 password_history 테이블을 볼 수 있다.
3.2.2.5 password reuse interval
한번 사용한 비밀번호의 재사용 금지 기간 설정. 미설정시 시스템 변수 password_reuse_interval에 저장된 기간으로 설정된다.
password reuse interval default 시스템 변수에 저장된 기간으로 설정
password reuse interval n day : n 일자 이후에 비밀번호 재사용 가능
3.2.2.6 password require
비밀번호 만료로 새 비밀번호 변경 시 기존 비밀번호가 필요한 지에 대한 옵션.
password require current : 먼저 현재 비밀번호부터 입력하도록 설정
password require optional : 현재 비밀번호 입력안하도록 설정
password require default: 시스템 변수 password_require_current 대로 설정
3.2.2.7 account lock/unlock
계정 생성때 혹은 alter user 사용하여 계정 정보를 변경할 때 계정을 잠글지 여부를 결정
account lock: 계정을 잠가서 사용하지 못하게함
account unlock: 잠긴 계정을 다시 사용할 수 있도록 해제
3.3 비밀번호 관리
3.3.1 고수준 비밀번호
위 기능들 뿐 아니라 슬자 조합 강제하거나 금칙어 설정할 수도 있다. validate_password컴포넌트를 설치하여 이용하면 된다. 내장되어있어서 별도 파일경로 지정이 불필요하다.
mysql> install component 'file://component_validate_password';
Query OK, 0 rows affected (0.06 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)
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, 1 warning (0.02 sec)
나중에 my.ini가서 low로 바꿔주었다.. 어차피 내 컴퓨터인데 뭐
low : 비밀번호 길이만 검증
medium : 길이 검증과 숫자와 대소문자, 특수문자의 배합을 검증
strong : medium 레베래 검증 후 금칙어 포함 여부 검증
금칙어 파일은 한줄에 하나씩 기록한 텍스트파일로 저장. 인터넷에서 작성된 것 찾으면 편하다.
validate_password.policy가 strong 일때만 이 파일이 적용된다.
3.3.2 이중 비밀번호(Dual password)
계정의 비밀번호로 2개의 값을 동시에 사용할 수 있다. 2개 다 일치해야 로그인되는 것이 아니라 2개 중 하나만 일치해도 로그인 할 수 있다.
Primary : 최근에 설정된 비밀번호
Secondary : 이전 비밀번호
alter user 'root'@localhost' identified by 'old_password';
이 명령은 root 계정의 primary 비밀번호를 old_password로 만들고 secondary 비밀번호는 비어있게 된다.
alter user 'root'@'localhost' identified by 'new_password' retain current password;
이 명령은 이전 비밀번호가 secondary 비밀번호가 되고 새롭게 설정한 new_password가 primary 비밀번호가 된다.
이 후 서버를 재시작한 후 secondary 비밀번호를 삭제하면 기존 비밀번호는 로그인이 불가능하니 password가 변경된 효과가 되는 것이다.
읽고 나서
기능이 다양해서 그렇지 어려운 것은 없다!
