[MYSQL] Real MySQL 8.0 1,2,3장 정리

 

MYSQL은 하나의 설정 파일 사용 = my.ini(유닉스 계열: my.cnf)

• 하나의 설정 파일만 사용하지만 설정 파일이 위치한 디렉터리는 여러 곳일 수 있음
• 서버가 어디서 해당 파일 읽는지 궁금할 때

#클라이언트 프로그램
mysql --help

#서버 프로그램(권장 x)
mysqld --verbose --help

mysqld: 서버 실행 프로그램

mysql: 클라이언트 프로그램

 

시스템 변수(System Variables): 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위한 값

SHOW GLOBAL VARIABLES; 

속성 5가지

1. Cmd-Line: 명령행 인자로 설정 가능 여부
2. Option file: 설정파일 my.ini 로 제어 가능 여부
3. System Var: 시스템 변수 여부
4. Var Scope: 시스템 변수의 적용 범위
5. Dynamic: 동적 변수인지 정적 변수인지 구분

시스템 변수의 적용 범위

1. 글로벌(Global): 서버 전체
   1. 서버 자체에 관련된 설정
2. 세션(Session): 서버와 클라이언트 간의 커넥션
   1. 클라이언트가 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어

정적 변수와 동적 변수

서버가 가동 중인 상태에서의 변경 가능 유무

SET GLOBAL: 동적 변수를 변경하는 명령이지만 변경한 후 설정 파일에서 내용을 적용하지 않는다면 서버를 재시작 했을 때 이전 상황으로 돌아감

SET PERSIST: SET GLOBAL를 보완, 변경된 값을 즉시 적용하고 별도의 설정 파일에 변경 내용 추가로 기록

 

사용자 식별

MYSQL의 계정: 사용자의 계정+사용자의 접속 지점 이 계정의 일부가 됨(→ 다른 DBMS와 다른점)

ex) ‘scv_id’@’127.0.0.1’ = 서버 기동 중인 로컬 호스트에서 svc_id로 접속할 때만 사용될 수 있는 계정

       ‘scv_id’@’%’ = 모든 외부 컴퓨터에서 접속 가능한 사용자 계정

→ 그럼 위와 같이 동일한 아이디가 있을때는?

⇒ 범위가 가장 작은 것을 먼저 선택(위의 예시에서는 첫번째)

 

사용자 계정 관리

💡 사용자: MYSQL 서버를 사용하는 주체(사람이나 응용프로그램)
      계정: MYSQL 서버에 로그인 하기 위한 식별자(로그인 아이디)

 

시스템 계정: SYSTEM_USER 권한 유, 데이터베이스 서버 관리자 위한 계정

• 계정 관리
• 다른 세션(커넥션) 또는 그 세션에서 실행 중인 쿼리를 강제 종료
• 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

일반 계정: SYSTEM_USER 권한 무, 응용 프로그램이나 개발자를 위한 계정

! 삭제하면 안되는 계정(잠겨 있음) !

1. ‘mysql.sys’@’localhost’: 기본으로 내장된 sys 스키마 객체들의 DEFINER로 사용되는 계정
2. ‘mysql.session’@’localhost’: MYSQL 플러그인이 서버로 접근할 때 사용되는 계정
3. ‘mysql.infoschema’@’localhost’: information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

 

MYSQL 8.0의 계정 생성

계정 생성: CREATE USER 명령

• 옵션
  • 계정 인증 방식, 비밀번호
  • 비밀번호 관련 옵션
  • 기본 역할(Role)
  • SSL 옵션
  • 계정 잠금 여부

권한 부여: GRANT 명령

CREATE USER 'user'@'%'
	IDENTIFIED WITH 'mysql_password' BY 'password'
	REQUIRE NONE
	PASSWORD EXPIRE INTERVAL 30 DAY
	ACCOUNT UNLOCK
	PASSWORD HISTORY DEFAULT
	PASSWORD REUSE INTERVAL DEFAULT
	PASSWORD REQUIRE CURRENT DEFAULT

1. IDENTIFIED WITH: 사용자의 인증 방식과 비밀번호 설정
   1. 반드시 인증 방식을 명시
      1. Native Pluggable Authentication
      2. Caching SHA-2 Pluggable Authentication: 8.0 의 기본 인증 방식
      3. PAM Pluggable Authentication
      4. LDAP Pluggable Authentication
2. REQUIRE: 서버에 접속할때 암호화된 SSL/TLS 채널을 사용할지 여부
3. PASSWORD EXPIRE: 비밀번호 유효 기간 설정
   1. PASSWORD EXPIRE: 생성과 동시에 만료
   2. PASSWORD EXPIRE NEVER: 만료기간 없음
   3. PASSWORD EXPIRE DEFAULT: 시스템 변수 default_password_lifetime에 저장된 기간
   4. PASSWORD EXPIRE INTERVAL n DAY: 오늘부터 n일자
4. PASSWORD HISTORY: 비밀번호 재사용하지 못하게 설정
   1. PASSWORD HISTORY DEFAULT: 시스템 변수 password_history에 저장된 개수만큼 비밀번호 이력 저장
   2. PASSWORD HISTORY n: 비밀번호 이력을 최근 n개만 저장
5. PASSWORD REUSE INTERVAL: 한 번 사용했던 비밀번호 재사용 금지 기간 설정
   1. PASSWORD REUSE INTERVAL DEFAULT: 시스템 변수 password_reuse_interval에 저장된 기간으로 설정
   2. PASSWORD REUSE INTERVAL n DAY: n일자 이후에 재사용
6. PASSWORD REQUIRE: 비밀번호 만료 시 새 비밀번호 생성할때 현재 비밀번호 필요로 할지 말지 결정
   1. PASSWORD REQUIRE CURRENT: 현재 비밀번호 먼저 입력 설정
   2. PASSWORD REQUIRE OPTIONAL: 현재 비밀번호 필요 X
   3. PASSWORD REQUIRE DEFAULT: 시스템 변수 password_require_current의 값으로 설정
7. ACCOUNT LOCK/UNLOCK: 계정 정보 변경 시 계정 사용하지 못하게 잠글지 여부

 

비밀번호 관리

고수준 비밀번호: validate_password 컴포넌트 설치 시 비밀번호 유효성 체크 규칙 적용 가능

1. LOW: 비밀번호 길이만 검증
   1. 시스템 변수 validate_password.length
2. MEDIUM: 비밀번호 길이, 숫자 대소문자, 특수문자 배합 검증
   1. 시스템 변수 validate_password.mixed_case_count, validate_password.number_count, validate_password.special_char_count
3. STRONG: 레벨 검증 모두 수행+금칙어 포함 여부
   1. 시스템 변수 validate_password.dictionary_file

이중 비밀번호: 서비스가 실행 중일때 데이터베이스 계정의 비밀번호 변경이 불가능 하여 이를 해결하기 위해 계정 비밀번호로 2개 값 동시에 사용

→ 최근 설정된 비밀번호가 프라이머리 비밀번호, 이전 비밀번호가 세컨더리 비밀번호

//RETAIN CURRENT PASSWORD 옵션 추가하면 가능
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

//세컨더리 비밀번호 삭제
ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

 

권한

이전 버전: 글로벌 권한과 객체 단위 권한으로 구분

• 글로벌 권한: 데이터베이스나 테이블 이외의 객체에 적용되는 권한
  • GRANT로 권한 부여할 때 특정 객체 명시 X
• 객체 권한: 데이터베이스나 테이블 제어하는데 필요한 권한
  • GRANT로 권한 부여할 때 특정 객체 명시

8.0: 동적 권한 추가

• 동적 권한: 서버가 시작되면서 동적으로 생성하는 권한
• 정적 권한: 서버의 소스코드에 고정적으로 명시돼 있는 권한

 

권한 부여 명령어: GRANT

GRANT OPTION

• TO: 권한을 부여할 대상 사용자 명시
  • 칼럼 단위 권한 대신 테이블에서 권한 허용 후 칼럼만으로 별도의 뷰를 만들어 사용
• ON: 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정 → 권한의 범위에 따라 방법 달라짐

//글로벌 권한 -> DB단위나 오브젝트 단위로 부여할 수 있는 권한 X = 대상은 항상 *.* 
GRANT SUPER ON *.* TO 'user'@'localhost'; 

//DB 권한 -> 테이블에 대해 권한 부여 X = 테이블 명시 X 
GRANT EVENT ON employess.* TO 'user'@'localhost'; 

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

칼럼 단위 권한부여도 가능하긴 하다

하지만 테이블, 칼럼 단위 권한 잘 사용 X

 

 

역할(Role)

8.0 부터 권한을 묶어서 역할을 사용

 

예시)

//빈 껍데기만 있는 역할 정의 
CREATE ROLE role_emp_read, role_emp_write; 

//각 역할에 대해 실질적인 권한 부여 
GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write; 

//계정 생성 
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty'; 

//계정에 역할 부여 
GRANT role_emp_read TO reader@'127.0.0.1'; 

//역할을 사용할 수 있도록 설정 
SET ROLE 'role_emp_read';

위 예시처럼 하면 역할이 활성화 되지만 계정을 로그아웃 했다가 다시 로그인 하면 초기화된다.

→ 역할을 자동으로 활성화할지의 여부를 시스템 변수 activate_all_roles_on_login 로 설정

 

계정과 역할이 내부적으로 똑같은 객체인데 왜 역할을 사용할까?

→ 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용될 수 있게 하기 위해서