11. MySQL 데이터베이스 사용자 관리

MySQL 사용자 개요

1. 사용자 생성의 개요

- DB는 동일한 데이터에 대해 다수의 사용자가 접근하고 활용할 수 있는 특징

-> 이에 따라 사용자를 생성 가능

 

2. MySQL 8.0 이후 계정 변화

1) 개요

SYSTEM_USER 권한을 가지고 있느냐에 따라서 계정을 구분

- 시스템 계정(System Account)

- 일반 계정(Regular Account)

 

2) 시스템 계정과 일반 계정

 (1) 시스템 계정

  - 시스템 계정과 일반 계정을 관리할 수 있음(계정 생성, 삭제, 변경)

  - DB 서버 관리와 관련된 중요 작업은 시스템 계정으로만 가능

 (2) 일반 계정

  - 시스템 계정을 관리할 수 없음

 

3) 시스템 계정의 역할

계정 관리를 함

 - 계정 생성 및 삭제

 - 계정 권한 부여 및 제거

다른 세션(Connection) 또는 그 세션에서 실행 중인 쿼리를 강제로 종료함

 

데이터베이스 사용자 계정 정보 확인

1. 계정 확인

- 계정에 대한 정보는 mysql이라는 DB명에 저장되어 있음
- 등록된 계정은 user 테이블에서 확인 가능함
- user 테이블 필드 중 host와 user를 조회하면 됨

 

2. 기본 계정

- MySQL 서버에는 내장된 계정들이 있음

-> root를 제외한 3개의 계정은 각각의 목적이 있으므로 삭제되지 않도록 주의가 필요

1) mysql.sys - MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체(뷰, 함수, 프로시저)들의 DEFINER로 사용되는 계정

2) mysql.session - MySQL 플러그인이 서버로 접근할 때 사용되는 계정

3) mysql.infoschema - information_schema에 정의된 뷰의 DEFINER로 사용되는 게정

4) root - 기본 관리자 계정

 

3. 사용자 계정

- user 칼럼 중 hgb는 추가된 임이의 사용자 계정

 

4. 계정별 password

- 계정별 password는 authentication_string이라는 스키마에 저장됨

- password는 암호화되어 저장됨

 

사용자 계정 생성 및 삭제

1. 사용자 계정 생성 방법

1) localhost에서만 접근이 가능한 계정을 생성할 경우

CREATE USER '계정아이디'@localhost IDENTIFIED BY '비밀번호';

 

2) 외부에서 접근이 가능한 계정을 생성할 경우

CREATE USER '계정아이디'@'%' IDENTIFIED BY '비밀번호';

 

3) 특정 IP 대역에서 접근이 가능한 계정을 생성할 경우

ex) 192.168.xxx.xxx IP 대역에서 접근 허용

CREATE USER '계정아이디'@'192.168.%' IDENTIFIED BY '비밀번호';

 

2. 사용자 계정 삭제 방법

DELETE FROM USER WHERE USER='계정아이디';

1) 사용자 계정 테이블 확인

 

2) 'student'라고 하는 아이디의 계정 생성

 

3) 사용자 계정 테이블 확인 및 생성 계정 확인

 

4) password 확인

 

3. 사용자 계정 삭제 예

1) 사용자 계정 테이블 확인

2) 계정 삭제 쿼리 실행

 

사용자 계정 생성의 세부 기법

1. 사용자 계정 생성의 변화

1) ~ MySQL

 - GRANT 명령으로 권한의 부여와 동시에 계정 생성이 가능

2) MySQL 8.0 버전~

 - 유저 생성 시에는 CREATE USER 명령으로 진행

 - 권한 부여는 GRANT 명령으로 구분

 

2. 계정 생성 옵션의 종류

1) 계정 인증 방식과 비밀번호

2) 비밀번호 관련 옵션 정책 설정

 - 비밀번호 유효 기간

 - 비밀 번호 사용에 대한 재사용 제한

 - 비밀번호 재사용 불가 기간

3) 기본 역할(ROLE)

4) SSL 옵션

5) 계정 잠금 여부

 

3. 사용자 계정 생성의 세부 기법

mysql> CREATE USER 'test'@'%'
REQUIRE NONE
PASSWORD EXPIRE INTERVAL 30 DAY
ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT;

 

1) REQUIRE

 - MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정하는 항목

 - 별도로 설정하지 않으면 비암호화 채널로 연결

 

2) PASSWORD EXPIRE

 - 비밀번호의 유효 기간을 설정

 - 계정 생성 시 명시하지 않으면 default_password_lifetime 시스템 변수에 설정된 기간으로 설정

 - default_password_lifetime 기본값은 0

 -> 자동 만료 기능의 비활성화, 기본 설정은 일수 제한 없음

 - 패스워드 만료 시 로그인은 성공하나 쿼리를 수행하면 에러 메시지가 발생

 

3) PASSWORD HISTORY(8.0 기능)

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

 - 저장된 패스워드는 mysql.password_history 딕셔너리에서 확인 가능

 

4) PASSWORD REUSE INTERVAL(8.0 기능)

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

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

 - password_reuse_interval 시스템 변수의 기본값

 - 0으로 제한 없음

 

5) PASSWORD REQUIRE(8.0 기능)

 - 비밀번호가 만료되어 새로운 비밀번호를 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 재확인하는 과정 여부를 설정

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

 

사용자 계정 권한

1. 사용자 계정 권한의 개념

 - 사용자가 DB 시스템의 객체 등을 이용할 수 있는 권리

 

2. 사용자 계정 권한의 필요성

 - 사용자들 혹은 Application들이 접속할 수 있는 환경 설계가 필요

 - DB 테이블에는 수많은 데이터들이 있음 (개인 정보 등의 중요 정보 보호가 필요)

 - 계정 권한 관리는 권한 레벨에 맞게 부여되고 사용되어야 함

 

사용자 계정 권한 설정

1. 사용자 계정 권한 설정의 종류

1) 사용자 계정 권한 설정 기본 쿼리문

GRANT 권한명1, 권한명2... ON 데이터베이스명.오브젝트명 TO 사용자계정;

 

2) 인스턴스 레벨 단위 권한 부여

GRANT 권한명1, 권한명2... ON *.*TO '사용자명'@'접속호스트IP';

 

3) 데이터 베이스 레벨 단위로 권한 부여

GRANT 권한명1, 권한명2... ON 데이터베이스명.*

TO '사용자명'@'접속호스트IP';

 

4) 오브젝트 레벨 권한 부여

GRANT 권한명1, 권한명2... ON 데이터베이스명.오브젝트명

TO '사용자명'@'접속호스트IP';

 

5) 컬럼 단위 레벨 권한 부여

GRANT 권한명(컬럼1, 컬럼2), 권한명(컬럼3) ON 데이터베이스명.테이블명

TO '사용자명'@'접속호스트IP';

 

2. 권한 및 IP 옵션 종류

1) 할당 권한의 종류

 

2) 접속 IP 설정

 

3) 사용자 계정 권한 설정의 예

 (1) 권한 설정 사항

  - 권한 대상은 localhost에서의 student라는 계정

  - 권한 영역은  test라는 DB 안의 account 테이블

  - 권한은 select라는 쿼리만 허용

 

 (2) 권한 설정 적용

 

 (3) 권한 설정 적용 결과 확인

 

사용자 계정 권한 회수

1. 사용자 계정 권한 회수 쿼리문

REVOKE 권한명1, 권한명2...

ON 데이터베이스명.오브젝트명 FORM 사용자계정;

 

2. 사용자 계정 권한 회수 적용 예