[Database] MySQL 사용자 및 권한

MySQL에서 사용자 계정을 생성하는 방법이나 각 계정의 권한을 설정하는 방법은 다른 DBMS와는 조금 다르다. 대표적으로 MySQL의 사용자 계정은 단순히 사용자의 ID뿐 아니라 사용자가 어느 IP에서 접속하고 있는지도 체크한다. 또한 MySQL 8.0 부터 권한을 묶어 관리하는 Role 개념이 도입되었기 때문에 각 사용자의 권한으로 미리 준비된 권한 세트를 부여하는 것도 가능하다.

• 계정의 식별 방식
• 권한
• 역할

위 세 가지에 대해 반드시 숙지하자.

식별

MySQL의 사용자는 ID와 클라이언트가 실행된 호스트명이나, 도메인 또는 IP 주소도 계정의 일부가 된다.

따라서 ID와 호스트를 함께 명시해야 하며, ID와 호스트를 감싸는 역따옴표(₩)는 종종 작은 따옴표(')로 사용하기도 한다.

'gorany1'@'127.0.0.1' //로컬에서만 사용 가능
'gorany2'@'%' //외부에서 접속 가능
'gorany3'@'192.103.24.1' //192.103.24.1 이라는 IP에서만 접속 가능

 

추가로 만약 MySQL 서버에 동일한 ID가 2개 이상 있다고 할때, 해당 사용자의 인증을 어떻게 구분할까?

case 1: 'gorany'@'192.168.0.10' (password:12345)
case 2: 'gorany'@'%' (password:abcde)

 

권한이나 계정 정보에 대해 MySQL은 가장 범위가 작은 것을 항상 먼저 선택한다. 즉, 위의 케이스에서는 192.168.0.10이 더 적은 범위이므로 이를 먼저 선택해 인증하게 된다.

만약 IP가 192.168.0.10인 곳에서 'gorany'라는 아이디와 abcde라는 password로 로그인하면 '비밀번호가 일치하지 않는다'며 접속이 안될 것이다.

계정 관리

MySQL 8.0부터 계정은 'SYSTEM_USER' 권한 여부에 따라

• 시스템 계정 (서버 관리자)
• 일반 계정 (개발자)

으로 구분된다.

 

※ 시스템 계정만 가능한 작업

• 계정 관리(계정 생성/삭제, 권한 부여/제거)
• 다른 세션(Connection) 또는 그 세션에서 실행 중인 쿼리 강제 종료
• 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

 

※ MySQL의 내장된 계정

• 'root'@'localhost'
• 'mysql.sys'@'localhost'
  • MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체(뷰나 함수, 프로시저)들의 DEFINER로 사용되는 계정
• 'mysql.session'@'localhost'
  • MySQL 플러그인이 서버로 접근할 때 사용되는 계정
• 'mysql.infoschema'@'localhost'
  • infomation_schema에 정의된 뷰의 DEFINER로 사용되는 계정

생성

~ MySQL 5.7

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

 

MySQL 8.0 ~

계정 생성 : CREATE USER 명령

권한 부여 : GRANT

# 계정 생성의 옵션

- 계정의 인증 방식과 비밀번호
- 비밀번호 관련 옵션(유효기간, 이력 개수, 재사용 불가 기간)
- 기본 역할 (Role)
- SSL 옵션
- 계정 잠금 여부

# 일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령
CREATE USER 'user'@'%'
    IDENTIFIED WITH 'mysql_native_password' BY 'password'
    REQUIRE NONE
    PASSWORD EXPIRE INTERVAL 30 DAY
    ACCOUNT LOCK
    PASSWORD HISTORY DEFAULT
    PASSWORD REUSE INTERVAL DEFAULT
    PASSWORD REQUIRE CURRENT DEFAULT;

• IDENTIFIED WITH
  • 사용자의 인증 방식과 비밀번호 설정
  • 뒤에 반드시 인증 방식을 명시해야 하는데, MySQL 서버의 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시
  • 다음 4가지 형식이 대표적
    • Native Pluggable Authentication (~ MySQL 5.7)
      • 비밀번호에 대한 해시 저장해두고, 클라이언트가 보낸 값과 해시값이 일치하는지 비교
    • Caching SHA-2 Pluggable Authentication (MySQL 5.6 ~ MySQL 8.0)
    • PAM Pluggable Authentication
    • LDAP Pluggable Authentication
• REQUIRE
  • MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할 지 여부
• PASSWORD EXPIRE
  • 비밀번호의 유효기간을 설정하는 옵션
• PASSWORD HISTORY
  • 한 번 사용했던 비밀번호를 재사용하지 못하게 하는 옵션
• PASSWORD REUSE INTERVAL
  • 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션
• PASSWORD REQUIRE
  • 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 필요로 할지 여부 옵션
• ACCOUNT LOCK / UNLOCK
  • 계정 생성/수정 시 계정을 사용하지 못하도록 잠글지 여부 옵션

권한 (Privilege)

~ MySQL 5.7 글로벌 권한과 객체 단위의 권한으로 구분되었다.

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

 

※ 주의점

객체 권한은 GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시해야 하며, 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야 한다.

 

구분	저장소 테이블	설명
정적 권한	mysql.user	계정 정보 & 계정이나 역할에 부여된 글로벌 권한
	mysql.db	계정 / 역할에 DB단위로 부여된 권한
	mysql.tables_priv	계정 / 역할에 테이블 단위로 부여된 권한
	mysql.colums_priv	계정 / 역할에 컬럼 단위로 부여된 권한
	mysql.procs_priv	계정 / 역할에 스토어드 프로그램 단위로 부여된 권한
동적 권한	mysql.global_grants	계정 / 역할에 부여되는 동적 글로벌 권한

역할 (Role)

MySQL 8.0 ~ 

권한을 묶어서 역할 (Role)을 사용할 수 있게 됨.

 

기본적으로 역할은 그 자체로 사용될 수 없고, 계정에 부여해야 하므로 CREATE USER 명령으로 reader와 writer라는 계정을 생성해본다.

# 역할 생성
mysql > CREATE ROLE
    role_emp_read,
    role_emp_write;

# 권한 부여
mysql > GRANT select on employees.* TO role_emp_read;
mysql > GRANT insert, update, delete on employees.* TO role_emp_write;

# 계정 생성
mysql > CREATE USER reader@'127.0.0.1' IDENTIFIED BY '123456';
mysql > CREATE USER writer@'127.0.0.1' IDENTIFIED BY '123456';

# 계정에 역할 설정
mysql > GRANT role_emp_read TO reader@'127.0.0.1';
mysql > GRANT role_emp_write TO writer@'127.0.0.1';

# reader로 접속
$ mysql -ureader -p123456 -h127.0.0.1

# 역할 active
mysql > SET ROLE 'role_emp_read';

mysql > SELECT current_role();

 

일단 역할이 활성화되면 그 역할이 가진 권한은 사용할 수 있는 상태가 되나, 계정이 로그아웃 됬다가 다시 로그인하면 역할이 활성화되지 않은 상태로 초기화된다. (자동화하지 않았기 때문)

 

이를 자동화하기 위해 activate_all_roles_on_login 시스템 변수로 설정할 수 있다.

mysql > SET GLOBAL activate_all_roles_on_login=ON;

 

#Reference

백은빈 & 이성욱. 2021. Real MySQL 8.0 (1 ,전면 개정판). 위키북스.