[MySQL] 사용자 생성 및 권한 부여 방법

[개요]

MySQL은 사용자 계정의 관리 방식이 다른 DBMS와 약간 차이가 있습니다 많은 DBMS에서는 아이디로 사용자를 식별하지만, MySQL은 사용자 아이디 외에도 접속하는 IP 주소를 함께 고려하여 더욱 정밀한 접근 제어를 제공합니다. 또한, MySQL 8.0 버전부터는 '역할(ROLE)'이라는 개념도 도입되었고, 이를 통해 미리 정의된 권한 그룹, 즉 'Role'을 사용자에게 쉽게 부여할 수 있게 되었습니다.

 

 

[사용자 식별]

계정과 호스트명

대부분의 DBMS는 계정 이름만으로 사용자를 식별하지만, MySQL은 사용자의 계정과 접속 지점(클라이언트 호스트명, 도메인, IP 주소)을 동시에 고려합니다. 따라서 MySQL 계정을 언급할 때는 아래의 예처럼 아이디와 호스트 정보를 명시해야 합니다.

'svc_id'@'127.0.0.1'

위 예시는 'svc_id' 계정이 MySQL 서버가 동작 중인 로컬 호스트에서만 접속할 수 있다는 것을 의미합니다. 만약 다른 컴퓨터에서 'svc_id'로 접속하려고 한다면, 이 계정 정보로는 접속할 수 없습니다.

외부 접속을 허용하려면 호스트 부분을 %로 대체해야 합니다. 이때의 %는 모든 IP나 호스트명을 의미합니다.

 

중복된 계정명 주의

사용자 계정을 식별할 때 중요한 점이 있는데, 바로 동일한 아이디를 가지는 경우입니다. MySQL 서버는 동일한 아이디를 가진 여러 계정이 있을 경우 문제가 생기는데, MySQL은 동일한 아이디를 가지는 정보가 있을 때 범위가 좁은 계정을 선택합니다.

'svc_id'@'192.168.0.10' -- 비밀번호: 123
'svc_id'@'%' -- 비밀번호: abc

위와 같은 계정 정보가 있을 때, '192.168.0.10' IP로 접속을 시도하면, MySQL은 범위가 좁은 'svc_id'@'192.168.0.10' 계정을 우선 인증하려고 합니다. 이 상황에서 'svc_id' 계정으로 'abc' 비밀번호로 로그인을 시도하면 '비밀번호 불일치' 오류가 발생하게 됩니다. 이처럼 중첩된 계정 설정은 주의가 필요합니다.

 

 

[사용자 계정 관리]

시스템 계정과 일반 계정

MySQL 8.0부터 계정은 'SYSTEM_USER' 권한의 유무에 따라 시스템 계정과 일반 계정으로 나뉩니다. 시스템 계정은 서버 내부적인 백그라운드 스레드와는 연관이 없으며, 이 계정은 데이터베이스 관리자용입니다. 반면 일반 계정은 애플리케이션 사용자나 개발자를 위한 것입니다.

시스템 계정은 모든 계정의 관리 권한을 가지며, 다음과 같은 중요한 관리 작업을 수행할 수 있습니다.

• 계정 관리(계정 생성, 삭제 및 권한 설정)
• 다른 세션 또는 쿼리의 강제 종료
• 스토어드 프로그램 생성 시, 다른 사용자를 DEFINER로 지정

이러한 구분은 DBA 계정에만 'SYSTEM_USER' 권한을 부여하고 일반 사용자에게는 부여하지 않기 위함입니다.

또한 MySQL 서버에는 기본적으로 제공되는 다음과 같은 내장 계정들이 있습니다. 그중 'root'@'localhost'를 제외하면, 나머지 계정들은 특정 목적으로 사용되므로 삭제하지 않아야 합니다.

• 'mysql.sys'@'localhost': MySQL 8.0부터 제공되며, sys 스키마 객체의 DEFINER로 사용됩니다.
• 'mysql.session'@'localhost': MySQL 플러그인이 접근용 계정입니다.
• 'mysql.infoschema'@'localhost': information_schema뷰의 DEFINER로 사용됩니다.

위에 언급한 3개의 계정은 기본적으로 잠겨(account_locked 칼럼) 있는 상태이므로 의도적으로 잠긴 계정을 풀지 않는 한 악의적으로 사용될 걱정은 하지 않아도 됩니다.

 

 

계정 생성

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 UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;

위 예제의 각 옵션을 하나씩 살펴봅시다.

 

IDENTIFIED WITH

사용자의 인증 방식과 비밀번호를 설정합니다. MySQL 서버에서 제공하는 다양한 인증 방식 중 하나를 선택할 수 있는데, 주요 방식은 다음과 같습니다.

1. Native Pluggable Authentication: MySQL 5.7까지의 기본 방식으로, SHA-1 알고리즘을 사용해 비밀번호 해시값을 저장하고 인증합니다.
2. Cacging SHA-2 Pluggable Authentication: MySQL 8.0의 기본 인증 방식으로, 보안성이 높은 SHA-2 알고리즘을 사용합니다. 이 방식은 해시값 계산에 시간이 많이 소요되므로, 최적화를 위해 해시값을 메모리에 캐싱합니다.
3. PAM pluggable Authentication: 유닉스나 리눅스의 패스워드 또는 LDAP를 사용하는 방식으로, MySQL 엔터프라이즈 에디션에만 제공됩니다.
4. LDAP pluggable Authentication: LDAP 인증을 사용하는 방식으로, MySQL 엔터프라이즈 에디션에서만 제공됩니다.

MySQL 5.7 버전까지는 Native Authentication이 기본 인증 방식으로 사용됐지만 MySQL. 8.0 버 전부터는 Caching SHA-2 Authentication이 기본 인증으로 바뀌었습니다. 하지만 호환성 문제로 인해 Native Authentication 방식을 계속 사용하려는 경우 아래와 같이 설정 변경이 가능합니다.

--// Native Authentication을 기본 인증 방식으로 설정
SET GLOBAL default_authentication_plugin="mysql_native_password";

 

주의 사항

MySQL의 Caching SHA-2 Pluggable Authentication 방식은 SCRAM 인증을 사용하며, 이는 평문 비밀번호를 여러 번 암호화하여 무차별 대입 공격을 방지합니다. 하지만 이러한 방식은 일반적인 사용자나 응용 프로그램의 연결 속도를 느리게 만들 수 있습니다. SCRAM 인증의 해시 함수 실행 횟수는 'sha2_password_digest_rounds' 변수로 설정이 가능하며, 기본 값은 5000입니다.

 

REQUIRE

MySQL 서버에 접속 시 SSL/TLS 암호화 채널의 사용 여부를 결정합니다. 설정하지 않으면 기본적으로 비암호화 채널로 연결됩니다. 그러나 'REQUIRE' 옵션을 SSL로 설정하지 않더라도 Caching SHA-2 Authentication 방식을 사용하면 MySQL 서버 접속 시 암호화 채널만 허용됩니다.

 

PASSWORD EXPIRE

비밀번호의 유효 기간을 설정하는 옵션이며, 별도로 지정하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간이 적용됩니다. 비밀번호 유효기간 설정은 관리자나 개발자 계정에는 권장되지만, 응용 프로그램 계정에는 주의가 필요합니다.

PASSWORD EXPIRE 절에 설정 가능한 옵션은 다음과 같습니다.

• PASSWORD EXPIRE: 계정을 생성할 때 비밀번호 즉시 만료
• PASSWORD EXPIRE NEVER: 비밀번호 만료 없음
• PASSWORD EXPIRE DEFAULT: default_password_lifetime 시스템 변수에 저장된 기간으로 유효기간 설정
• PASSWORD EXPIRE INTERVAL n DAY: 오늘부터 n일 후 만료

 

PASSWORD HISTORY

한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션으로, PASSWORD HISTORY 절에 설정 가능한 옵션은 다음과 같습니다.

• PASSWORD HISTORY DEFAULT: 시스템 번수에 지정된 개수만큼의 이전 비밀번호를 저장하며, 저장된 이력 안의 비밀번호는 재사용 불가
• PASSWORD HISTORY n: 최근 n개의 비밀번호만 저장하며, 저장된 비밀번호는 재사용 불가

한 번 사용했던 비밀번호를 사용하지 못하게 하려면 이전에 사용했던 비밀번호를 MySQL 서버가 기억하고 있어야 하는데, 이를 위해 MySQL 서버는 mysql DB의 password_history 테이블을 사용합니다.

 

PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정됩니다. PASSWORD REUSE INTERVAL 절에 설정 가능한 옵션은 다음과 같습니다.

• PASSWORD REUSE INTERYAL DEFAULT: password_reuse_interval 변수에 저장된 기간으로 설정
• PASSWORD REUSE INTERVAL n DAY: n일 이후에 비밀번호를 재사용할 수 있게 설정

 

PASSWORD REQUIRE

비밀번호 반료 후 변경 시, 이전 비밀번호의 입력 여부를 설정합니다. 별도로 명시되지 않으면 password_require_current 시스템 변수의 값으로 설정됩니다. PASSWORD REQUIRE 절에 사용 가능한 옵션은 다음과 같습니다.

• PASSWORD REQUIRE CURRENT: 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
• PASSWORD REQUIRE OPTIONAL: 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
• PASSWORD REQUIRE DEFAULT: password_require_current 시스템 변수의 값으로 설정

 

ACCOUNT LOCK / UNLOCK

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

• ACCOUNT LOCK: 계정을 사용하지 못하게 잠금
• ACCOUNT UNLOCK: 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

 

 

[비밀번호 관리]

고수준 비밀번호

MySQL에서는 비밀번호의 유효기간, 재사용 제한, 복잡성 검증 등 다양한 보안 기능을 제공합니다. 특히, 사용자가 예상하기 쉬운 단어나 패턴을 포함하는 비밀번호의 사용을 방지하는 기능이 있습니다. 이를 위해 MySQL은 validate_password 컴포넌트를 제공하는데, 이 컴포넌트는 MySQL 서버에 기본으로 포함되어 있기 때문에 별도의 파일 경로 지정 없이 바로 설치가 가능합니다.

validate_password 컴포넌트 설치

INSTALL COMPONENT 'file://component_validate_password';

컴포넌트 설치 후 해당 컴포넌트에서 제공하는 시스템 변수들을 다음과 같은 명령어로 확인할 수 있습니다.

SHOW VARIABLES LIKE 'validate_password%';

비밀번호 정책은 크게 다음 3가지 중에서 선택할 수 있으며, 기본값은 MEDIUM으로 자동 설정됩니다.

• LOW: 비밀번호의 길이만 검증
• MEDIUM: 비밀번호 길이, 숫자, 대소문자, 특수문자 포함 여부 검증
• STRONG: MEDIUM으 모든 검증에 추가로 금칙어 포함 여부도 검증

validate_password.length는 비밀번호의 길이, validate_password.mixed_case_count와 validate_password.number_count, validate_password.special_char_count는 숫자와 대소문자, 특수문자를 일정 수 이상 포함하고 있는지 검증합니다.

그리고 마지막으로 금칙어는 validate_password.dictionary_file 시스템 변수에 금칙어들이 저장된 사전 파일을 등록하면 됩니다.

금칙어 파일은 다음과 같이 금칙어들을 한 줄에 하나씩 기록해서 저장한 텍스트 파일로 작성하면 됩니다.

password
123456
admin

금칙어 파일을 작성한 후, 이를 MySQL 서버에 적용하려면 다음과 같이 설정하면 됩니다. 단, 이 기능은 validate_password.policy가 'STRONG'일 때만 적용됩니다.

SET GLOBAL validate_password.dictionary_file='/path/to/prohibitive_word.data';
SET GLOBAL validate_password.policy='STRONG';

이렇게 설정하면 MySQL은 지정된 금칙어를 포함하는 비밀번호의 사용을 제한하게 됩니다.

 

이중 비밀번호

MySQL8.0부터는 계정의 보안을 더욱 강화하고자 하는 의도로 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능(DUAL 비밀번호)을 추가했습니다. 이 기능의 핵심은 하나의 계정에 두 개의 비밀번호를 할당하는 것이지만, 이 두 개의 비밀번호 중 하나만 맞게 입력해도 로그인이 가능합니다.

데이터베이스는 여러 응용 프로그램 서버들과 공유되는 경우가 많습니다. 이로 인해 데이터베이스의 계정 정보를 변경하는 것은 복잡하게 될 수 있습니다. 실제 서비스 운영 중에는 중요한 서비스에 영향을 줄 수 있기 때문에  데이터베이스 계정의 비밀번호 변경이 거의 불가능에 가깝습니다. 따라서, 많은 데이터베이스에서는 초기 설정 이후 오랜 시간 동안 동일한 비밀번호를 사용하는 경우가 흔한데, 이러한 배경에서 DUAL 비밀번호는 보아 강화와 유연한 과리를 동시에 가능하게 해주는 해결책으로 제시되었습니다.

아래는 사용 예시입니다.

-- 먼저, 'user'의 비밀번호를 'old_password'로 변경합니다.
ALTER USER 'user'@'%' IDENTIFIED BY 'old_password';

-- 이제 'old_password'는 그대로 유지하면서 'new_password'를 주 비밀번호로 추가합니다.
ALTER USER 'user'@'%' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

이렇게 설정한 후에는 'old_password'와 'new_password' 모두를 사용하여 로그인이 가능하게 됩니다.

 

 

[권한(Privilege)]

MySQL은 권한 관리를 통해 데이터베이스의 보안을 강화하며, 사용자나 애플리케이션에 적절한 권한만 부여하는 것이 중요합니다. MySQL 5.7 버전까지는 주로 다음 주 종류의 권한으로 나뉘어 있었습니다.

• 객체 권한: 특정 데이터베이스나 테이블에 적용되는 권한
• 글로벌 권한: MySQL 서버 전반에 적용되는 권한으로, 데이터베이스나 테이블 외에 다른 자원에도 적용

MySQL 8.0 버전부터는 동적 권한이라는 새로운 권한이 추가되면서, 이전 버전의 권한들은 정적 권한으로 불리게 되었습니다. 동적 권한은 서버에 플러그인이나 컴포넌트가 설치될 때 동적으로 추가할 수 있는 권한입니다.

 

글로벌 권한 부여하기

글로벌 권한을 부여하려면, 아래와 같은 쿼리를 실행하면 됩니다.

GRANT SUPER ON *.* TO 'user'@'%';

그러나, 글로벌 권한을 부여하더라도 특정 데이터베이스나 테이블에 접근하는 권한은 자동으로 부여되지 않기에 유의해야 하며, 글로벌 권한은 특정 데이터베이스나 테이블에 한정되어 있지 않기 때문에 항상 '*.*' 형식으로 작성해야 합니다.

권한을 확인하려면, 해당 계정에 대해 다음과 같은 쿼리를 실행하여 부여된 권한 목록을 확인할 수 있습니다.

SHOW GRANTS FOR '계정이름'@'호스트';

 

권한 부여 종류

-- 모든 데이터베이스에 대한 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

-- 특정 데이터베이스 오브젝트에 대해서만 권한 부여
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'%';

-- 특정 데이터베이스의 특정 테이블에 대해서만 권한 부여
GRANT SELECT, INSERT ON mydb.mytable TO 'user'@'%';

다만, 테이블이나 칼럼 단위의 권한은 잘 사용하지 않는데, 칼럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 칼럼에 대해서도 권한 체크를 하기 때문에 칼럼 하나에 대해서만 권한을 설정하더라도 전체적인 성능에 영향을 미칠 수 있기 때문입니다.

 

 

[역할(Role)]

MySQL8.0부터는 권한 관리를 보다 효과적으로 수행하기 위해 역할(Role) 기능이 도입되었습니다. 역할은 이름만으로는 계정과 구별되지만, 내부적으로는 계정과 동일하게 작동합니다.

역할 생성

역할을 생성하기 위해서는 'CREATE ROLE' 명령어를 사용합니다.

CREATE ROLE role_testdb_read, role_testdb_write;

역할에 권한 부여

생성된 역할에 권한을 부여하려면 아래와 같은 명령어를 사용합니다.

GRANT SELECT ON testdb.* TO role_testdb_read;
GRANT INSERT, UPDATE, DELETE ON testdb.* TO role_testdb_write;

사용자 계정 생성

다음으로 이 역할을 사용할 계정을 생성합니다.

CREATE USER reader@'%' IDENTIFIED BY '12345678';
CREATE USER writer@'%' IDENTIFIED BY '12345678';

계정에 역할 부여

생성한 계정에 역할을 부여합니다.

GRANT role_testdb_read TO reader@'%';
GRANT role_testdb_read, role_testdb_write TO writer@'%';

역할 활성화

'SELECT current_role();'를 사용하여 현재 활성회된 역할을 확인할 수 있습니다. 그러나 초기에는 역할이 활성화되지 않은 상태이기 때문에 'SET ROLE' 명령어를 사용하여 역할을 활성화해야 합니다.

SET ROLE 'role_testdb_read';

역할 자동 활성화

매번 역할을 수동으로 활성화하는 것은 번거로울 수 있습니다. MySQL은 이를 자동화하기 위한 시스템 변수를 제공합니다. 다음과 같이 activate_all_roles_on_login 시스템 변수가 ON으로 설정되면 매번 SET ROLE 명령으로 역할을 활성화하지 않아도 로그인과 동시에 부여된 역할이 자동으로 활성화됩니다.

SET GLOBAL activate_all_roles_on_login=ON;

 

역할과 계정의 관계

이전에 말했듯이, MySQL 서버에서의 '역할'과 '사용자 계정'은 많은 유사점을 가지고 있으며, 실제로 MySQL 서버 내부에서는 둘을 동일한 객체로 간주합니다. 이러한 주목적은 사용자 계정에 다른 사용자 계정이 가진 권한을 효율적으로 병합하기 위함입니다.

계정(User)

• 로그인이 가능한 객체
• 데이터베이스와 테이블에 직접적인 권한을 가질 수 있다.

역할(Role)

• 로그인이 불가능하다.
• 권한을 묶어서 관리하는 단위이다.
• 계정에게 부여될 수 있다.

실제로 아래의 명령어를 실행하면 MySQL에 저장된 계정을 모두 조회할 수 있습니다.

SELECT user,host,account_locked from mysql.user;

위 명령어를 실행하면, 역할과 계정이 모두 'user' 테이블에서 관리되고 있음을 확인할 수 있습니다.

계정에 역할을 부여하는 과정은 근본적으로 하나의 사용자가 가진 권한을 다른 사용자의 권한 집합에 통합하는 것이기 때문입니다. 때문에 'CREATE ROLE' 명령어에서 호스트 정보가 생략되어 있으며, 실제로 이는 모든 호스트('%')에 대해 암시적으로 적용한다는 의미가 됩니다.

그렇다면, 왜 'CREATE ROLE'과 'CREATE USER'를 구분해야 할까요?

단순히 말하면, 'CREATE ROLE'은 계정을 생성하는 것이 아니라 권한의 집합(역할)을 정의하는 명령이기 때문입니다. 'account_locked' 컬럼이 'Y'로 설정된 역할은 로그인이 불가능하지만, 'CREATE USER'로 생성된 계정은 로그인이 가능합니다.

더 깔끔하게 역할 관계를 확인하려면, 다음의 테이블을 참조하면 됩니다.

• mysql.default_roles: 각 계정에 부여된 기본 역할을 나타낸다.
• mysql.role_edges: 부여된 역할 간의 관계를 표현하는 그래프 정보를 제공한다.

 

 

참고

Real MySQL 8.0(1권)