새소식
데이터베이스

MySQL - 사용자 및 권한

  • -

💡 Real MySQL 8.0을 정리한 글입니다. 버전별로 상이할 수 있으니 정확한 내용은 공식 메뉴얼을 확인해주세요.

1. 사용자 식별

MySQL은 사용자의 계정 뿐만 아니라 클라이언트가 실행된 호스트명이나 IP 주소 같은 사용자의 접속 지점까지 계정의 일부로 식별합니다. 

 

그러므도 계정을 언급할 때는 다음과 같이 항상 아이디와 호스트를 함께 명시해야합니다.

-- ` 나 ' 로 아이디와 호스트를 감쌉니다.
'dokuny' @ '127.0.0.1'

 

위의 예시는 127.0.0.1의 주소에서 dokuny 아이디로 접속할 때만 사용가능한 계정입니다. 다른 주소에서 해당 아이디로 접속할 수 없습니다.

 

모든 주소에서 접속이 가능한 사용자 계정을 생성하고 싶다면 아래와 같이 호스트 부분을 '%'로 변경해주면 됩니다.

-- 여기서 %는 모든 IP or 호스트명을 의미
'dokuny' @ '%'

 

 

MySQL은 동일한 아이디가 여러 개 있을 때,범위가 가장 작은 것을 선택하여 인증하도록 합니다.

그렇기 때문에 중첩된 계정을 생성할 땐 주의해야합니다.

-- 다음과 같은 계정이 있을 시
'dokuny' @ '192.168.0.10' (pwd = 123)
'dokuny' @ '%'            (pwd = abc)

-- 192.168.0.10의 주소에서 아이디 dokuny, 비밀번호 abc로 접속하려는 경우엔 접속 실패
-- 범위가 더 좁은 'dokuny' @ '192.168.0.10'으로 인증을 시도하기 때문

 

2. 사용자 계정 관리

시스템 계정과 일반 계정

MySQL에서 계정은 SYSTEM_USER 권한을 가지고 있는냐,없느냐에 따라 시스템 계정, 일반계정으로 구분됩니다.

 

시스템 계정이 가지고 있는 SYSTEM_USER 권한은 다음과 같은 권한을 포함합니다.

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

 

두 계정 모두 사용자를 위한 계정이며, 시스템 계정은 데이터베이스 서버 관리자를 위한 계정, 일반 계정은 응용 프로그램이나 개발자를 위한 계정 정도로 생각하시면 됩니다.

 

MySQL 다음과 같은 내장된 계정들이 존재하는데, 내부적으로 사용되는 계정이므로 삭제되지 않도록 주의해야합니다.

'mysql.sys' @ 'localhost'        -- sys 스키마의 객체(뷰,함수,프로시저)들의 DEFINER로 사용되는 계정
'mysql.session' @ 'localhost'    -- MySQL 플러그인이 서버로 접근할 때 사용하는 계정
'mysql.infoschema' @ 'localhost' -- information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

 

계정 생성

계정의 생성은 CREAT USER 명령으로, 권한 부여는 GRANT 명령으로 할 수 있습니다.

 

계정 생성 시 다음과 같은 옵션을 설정할 수 있습니다.

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

 

일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령은 다음과 같습니다.

CREATE USER '아이디'@'%'
	IDENTIFIED WITH 'mysql_native_password' BY '비밀번호'
    REQUIRE NONE
    PASSWORD EXPIRE INTERVAL 30 DAY
    ACCOUNT UNLOCK
    PASSWORD HISTORY DEFAULT
    PASSWORD REUSE INTERVAL DEFAULT
    PASSWORD REQUIRE CURRENT DEFAULT;

 

IDENTIFIED WITH

사용자 인증 방식과 비밀번호를 설정합니다. IDENTIFIED WITH 뒤에는 인증 방식(인증 플러그인의 이름)을 명시해야 합니다.

 

MySQL에선 다양한 인증 방식을 플러그인 형태로 제공합니다.

  • Native Pluggable Authentication : 비밀번호에 대한 암호화 해시(SHA-1)값을 저장해두고 클라이언트가 보낸 값과 일치하는지 비교
  • Caching SHA-2 Pluggable Authentication : 위의 방식에서 SHA-2로 바뀐 방식, 해시값을 계산하는 방식이 상당한 시간을 소모하므로 해시 결괏값을 메모리에 캐싱해서 사용, SSL/TLS 또는 RSA 키페어가 필요
  • PAM Pluggable Authentication : 유닉스나 리눅스 패스워드 또는 LDAP 같은 외부인증을 사용할 수 있게 해주는 방식, MySQL 엔터프라이즈 에디션에서만 사용 가능
  • LDAP Pluggable Authentication : LDAP를 이용한 외부 인증을 사용할 수 있게 해주는 방식, 엔터프라이즈 에디션에서만 사용 가능

 

현재 8.0버전에서는 Caching SHA-2 Pluggable Authentication 방식이 기본 인증 방식입니다.

 

기본 인증 방식으로 바꾸고 싶다면 다음과 같이 하거나 my.cnf 설정 파일에 추가하면 됩니다.

SET GLOBAL default_authentication_plugin = 'mysql_native_password"

-- caching_sha2_password , sha256_password 등 으로 설정 변경 가능

 

 

REQUIRE

암호화된 SSL/TLS 채널을 사용할지 여부를 설정합니다. 별도로 설정하지 않는 경우, 비암호화 채널로 연결되지만, 위에서 다룬 Caching SHA-2 Pluggable Authentication을 사용한다면  암호화된 채널만으로만 서버에 접속할 수 있습니다.

 

PASSWORD EXPIRE

비밀번호의 유효기간을 설정합니다.

 

별도로 명시하지 않는다면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효기간이 설정됩니다.

 

설정 가능한 옵션은 다음과 같습니다.

  • PASSWORD EXPIRE : 계정 생성과 동시에 비밀번호의 만료 처리
  • PASSWORD EXPIRE NEVER : 만료기간 없음
  • PASSWORD EXPIRE DEFAULT : 별도로 명시하지 않는 것과 동일
  • PASSOWRD EXPIRE INTERVAL n DAY : 비밀번호의 유효기간을 오늘부터 n일자로 지정 

 

PASSWORD HISTORY

한 번 사용했던 비밀번호를 재사용하지 못하도록 설정합니다.

 

  • PASSWORD HISTORY DEFAULT : password_history 시스템 변수에 저장된 개수만큼 이력을 저장, 이력에 남은 비밀번호는 재사용 불가
  • PASSWORD HISTORY n : 비밀번호 이력을 최근 n개까지만 저장, 이력에 남은 비밀번호는 재사용 불가

이력을 저장하기 위해 password_history 테이블을 사용합니다.

 

PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호의 재사용 금지 기간을 설정합니다.

 

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

 

  • PASSWORD REUSE INTERVAL DEFAULT : 시스템 변수에 설정된 값으로 설정
  • PASSWORD REUSE INTERVAL n : n일자 이후에 비밀번호 재사용 가능

 

PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호로 변경할 때, 만료된 비밀번호를 필요로 할지 결정하는 옵션입니다.

 

별도 명시되지않으면 password_require_current 시스템 변수의 값으로 설정됩니다.

 

  • PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 입력 O
  • PASSWORD REQUIRE OPTIONAL : 비밀번호를 변경할 때 현재 비밀번호를 입력 X
  • PASSWORD REQUIRE DEFAULT : 시스템 변수의 값으로 설정

 

ACCOUNT LOCK / UNLOCK

계정 생성 시나 ALTER USER 명령을 사용해 계정 정보 변경할 때 계정을 사용하지 못하게  잠글지 여부를 결정합니다.

 

  • ACCOUNT LOCK : 계정을 잠금
  • ACCOUNT UNLOCK : 잠긴 계정을 잠금 해제

 

3. 비밀번호 관리

고수준 비밀번호

비밀번호를 쉽게 유추할 수 없도록 특정 단어들의 사용을 막거나, 글자 조합을 강제하는 기능입니다.

 

이 기능을 이용하기위해선 validate_password 컴포넌트를 이용해야합니다.

-- 컴포넌트 설치
INSTALL COMPONENT 'component_validate_password';

-- 설치된 컴포넌트 확인
SELECT * FROM mysql.component;

-- 컴포넌트 제공 시스템 변수 확인
SHOW GLOBAL VARIABLE LIKE 'validate_password%';

 

비밀번호 정책은 크게 3가지 중에 선택할 수 있으며, 기본값은 MEDIUM 입니다.

  • LOW : 비밀번호의 길이만 검증
  • MEDIUM : 비밀빈호 길이 , 숫자 , 대소문자, 특수문자 배합 검증
  • STRONG : MEDIUM 레벨 검증 + 금칙어 포함 여부 검증

설정에 이용되는 시스템 변수는 다음과 같습니다.

  • validate_password.length : 길이가 설정 이상인지
  • validate_passowrd.mixed_case_count : 대소문자가 설정 이상 포함되어있는지
  • validate_passowrd.number_count : 숫자가 설정 이상 포함되어있는지
  • validate_password.special_char_count : 특수문자가 설정 이상 포함되어 있는지
  • validate_password.dictionary_file : 설정된 사전 파일에 명시된 단어를 포함하고 있는지

금칙어 사전 파일(dictionary file)은 금칙어를 한 줄에 하나씩 기록한 텍스트 파일입니다.

 

금칙어 사전 파일은 다음과 같이 등록할 수 있습니다.

-- 금칙어 파일 설정
SET GLOBAL validate_password.dictionary_file='파일명';

-- 금칙어 정책 설정
SET GLOBAL validate_password.policy='STRONG';

 

이중 비밀번호

한 계정에 비밀번호 2개를 동시에 사용하는 기능입니다.(둘 중 뭘 사용하던 OK)

 

2개의 비밀번호는 Primary(최근에 설정된 비밀번호)와 Secondary(이전 비밀번호)로 구분됩니다.

 

이중 비밀번호를 사용하려면 다음과 같이 설정하면 됩니다.

-- 계정 비밀빈호 설정
ALTER USER '사용자명'@'호스트명' IDENTIFIED BY '첫번째 비밀번호';

-- 기존 비밀번호(SECONDARY)를 유지한 채, 새로운 비밀번호(PRIMARY) 설정
ALTER USER '사용자명'@'호스트명' IDENTIFIED BY '두번째 비밀번호' RETAIN CURRENT PASSWORD;

-- SECONDARY 비밀번호 삭제
ALTER USER '사용자명'@'호스트명' DISCARD OLD PASSWORD;

 

4. 권한

MySQL에서는 사용자에게 권한을 부여할 수 있습니다.

 

권한엔 정적 권한(글로벌 권한,객체 권한)과 동적 권한으로 나눠집니다.

 

  • 글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한, GRANT 명령에 특정 객체 명시
  • 객체 권한 : 데이터베이스나 테이블을 제어하는 데 필요한 권한, GRANT 명령에 특정 객체 명시 X
  • 정적 권한 : 시스템 내부적으로 제공되는 권한들
  • 동적 권한 : 플러그인이나 컴포넌트 등으로 런타임시에 동적으로 추가된 권한들

 

ALL (ALL PRIVILEGES)권한의 경우, 글로벌과 객체 권한으로 사용될 수 있으며 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여하고, 글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한을 부여합니다.

 

권한에 대한 종류 및 자세한 내용은 메뉴얼을 참고하시면 됩니다. 

 

권한 부여는 GRANT 명령을 사용합니다.

-- 권한 부여 시, 존재하지 않는 사용자라면 에러가 발생합니다. 
GRANT 권한명 ON 오브젝트 TO '사용자명'@'호스트명';

 

글로벌 권한의 경우, 특정 DB나 테이블에 부여될 수 없기 때문에 다음과 같이 *.*을 사용합니다.

-- *.*은 모든 DB의 모든 오브젝트(테이블,스토어드 프로시저,함수 등)를 포함한 MySQL서버 전체를 의미
GRANT 글로벌 권한 ON *.* TO '사용자명'@'호스트명';

 

DB 권한은 다음과 같이 부여합니다.

-- 모든 DB에 대한 권한 부여
GRANT DB권한 ON *.* TO '사용자명'@'호스트명';

-- 특정 DB에 대한 권한 부여, DB 권한은 테이블에 명시할 수 없기 때문에 DB명.테이블명 형식으로 사용할 수 없습니다.
GRANT DB권한 ON DB명.* TO '사용자명'@'호스트명';

 

테이블 권한 부여는 다음과 같이합니다.

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO '사용자명'@'호스트명';
GRANT SELECT,INSERT,UPDATE,DELETE ON DB명.* TO '사용자명'@'호스트명';
GRANT SELECT,INSERT,UPDATE,DELETE ON DB명.테이블명 TO '사용자명'@'호스트명';

 

컬럼 권한 부여는 INSERT, UPDATE, SELECT만 가능하며 다음과 같이합니다.

-- 컬럼 권한 같은 경우, 하나를 설정해두면 나머지 모든 테이블의 모든 칼럼에 대해서도 
-- 권한 체크를 하기 때문에 성능에 악영향이 갈 수 있습니다.
GRANT SELECT,INSERT,UPDATE(컬럼명) ON DB명.테이블명 TO '사용자명'@'호스트명';

 

MySQL에서 각 계정에 부여된 권한이나 역할을 확인하려면 SHOW GRANTS 명령이나 권한 관련 테이블을 참조하면 됩니다.

 

테이블은 다음과 같습니다. 

  • mysql.user , mysql.db , mysql.tables_priv, mysql.colums_priv, mysql_procs_priv : 글로벌, DB, 테이블, 컬럼, 스토어드 프로그램 순으로 정적 권한 확인
  • mysql.global_grants : 동적 글로벌 권한 확인

 

5. 역할

권한을 묶어서 역할로 만들어 부여할 수 있습니다. 역할은 내부적으로 계정과 똑같은 모습으로 처리됩니다.

 

역할은 다음과 같이 사용합니다.

-- 역할 생성
CREATE ROLE 역할명1,역할명2;
-- MySQL 내부적으로는 CREATE ROLE 역할명@'%'; 로 처리가 됩니다.

-- 역할에 권한 부여 예시
GRANT 권한명 ON 오브젝트 TO 역할명;

-- 계정에 역할 부여
GRANT 역할명 TO '사용자명'@'호스트명';

-- 역할 활성화
SET ROLE '역할명';

 

MySQL은 로그아웃 시 역할 활성화가 초기화됩니다. 자동으로 활성화를 시키고 싶다면 activate_all_roles_on_login 시스템 변수를 ON으로 바꿔주면 됩니다.

SET GLOBAL activate_all_roles_on_login=ON;

 

계정의 역할이나 그래프 관계는 SHOW GRANTS 명령이나 mysql.default_roles,mysql.role_edges 테이블을 참조하면 됩니다.

Contents
댓글 0

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.

SHARE

DARK

MENU

티스토리툴바