[realMySQL][5] 사용자 및 권한

사용자 식별

MySQL 는 사용자 계정 뿐만 아니라 사용자의 접속 지점(도메인 이름 또는 IP주소) 도 계정의 일부가 됩니다.

 

select user ,host,account_locked from mysql.user

 

위 사진에서 보이듯 계정 이름이 'root' 인것이 2개가 존재합니다. 

 

1) 'root'@'%' 

2) 'root'@'localhost'

 

만약 'svc_id'@'192.168.0.10' 이라는 계정이 있다면 '192.168.0.10' 에서만 'svc_id'로 접속가능합니다.

 

모든 지점에서 접속을 가능하게 하려면 '%' 를 붙이면 모든 IP주소에서 접근할 수 있습니다.

 

그러면 'svc_id'@'192.168.0.10' 의 비밀번호는 123 이고 'svc_id'@'%'의 비밀번호는 abc라면 'svc_id'의 비밀번호는 무엇일까요?

 

MySQL은 범위가 작은거 부터 선택하기 때문에 '192.168.0.10'에서 abc라는 비밀번호로 로그인하게 된다면 접속이 불가능합니다.

 

시스템 계정과 일반 계정

시스템 계정과 일반계정의 가장 큰 차이점은 SYSTEM_USER 권한이 있는 것과 없는 것입니다.

 

SYSTEM_USER 는 아래와 같은 권한을 가집니다.

 

1) 계정생성 및 삭제, 계정의 권한 부여 및 제거

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

3) 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

 

잘보면 'root'@'localhost' 외에 잠겨있는 3개의 계정이 있습니다.

 

1) mysql.infoschema : information_schema 에 정의된 뷰의 DEFINER로 사용되는 계정

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

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

 

계정 생성

MySQL 5.7 까지는 GRANT 명령어로 권한의 부여와 동시에 계정생성까지 하였습니다.

MySQL 8 부터는 생성은 CREATE USER 로 하고 권한부여는 GRANT 로 구분합니다.

 

 

계정을 생성할 때 여러 옵션을 설정할 수 있습니다.

1) 인증방식 , 비밀번호

2) 비밀번호 옵션( ex. 유효기간,비밀번호 이력 개수, 비밀번호 재사용 불가 기간)

3) 역할(ROLE)

4) SSL 옵션

5) 잠금 여부

 

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_native_password 는 인증 방식을 명시하는 것이며 'password' 는 user의 비밀번호가 'password' 인 것입니다.

mysql 클라이언트를 종료하고 다시 user로 로그인 하여 비밀번호로 'password'를 입력해보면 확인 가능합니다.

 

MySQL은 인증방식을 다양한 플러그인을 통해서 제공하며 아래 4가지가 대표적입니다.

 

1) Native Pluggable Authentication : 단순히 비밀번호 해시값을 저장해두고 클라이언트가 보낸값과 해시값이 일치하는지 비교하는 방식입니다.

 

2) Caching SHA-2 Pluggable Authentication : 암호화 해시값 알고리즘을 SHA-2를 사용합니다. Native와 가장 큰 차이는 Native는 SHA-1을 이용한 알고리즘인지 SHA-2 를 쓰는 것인지 차이입니다. Native는 입력에 대해 동일한 해시값결과가 나타나지만 SHA-2 알고리즘은 동일한 키 값에 대해서도 수천번의 해시계산이 이루어지므로 결과값이 다릅니다. 따라서 시간이 상당히 드는 작업이므로 해시 결과값을 메모리에 캐시해두고 사용하는데 그래서 이름이 Caching이 포함되어 있는 것입니다. 이 방식을 사용하기 위해서는 SSL/TLS 또는 RSA 키페어를 반드시 사용해야하고 클라이언트에서는 접속할때 SSL 옵션을 활성화 해야합니다. MySQL 8.0의 기본 인증방식입니다.

 

3) PAM Pluggable Authentication : 유닉스,리눅스등 외부 인증을 사용할 수 있는 인증방식

 

4) LDAP Pluggable Authentication : LDAP를 이용한 외부 인증을 사용할 수 있는 인증방식.

 

- 3),4) 은 엔터프라이즈 에디션에서만 사용가능 합니다.

 

 

MySQL 8.0 의 기본 인증방식을 살펴보겠습니다.

 

기본적으로 caching_sha2_password를 사용하고 있고 caching_sha2_password_digest_rounds 값이 5000인걸로 보아 해시함수를 5000번이나 실행함을 알 수 있습니다. 이러한 인증방식에 대해 자세히 알고 싶다면 SCRAM 인증방식에 대해서 조사해보면 될거 같습니다.

 

REQUIRE

MySQL 서버에 접속 할때 암호화된 SSL/TLS 채널을 사용할 지 여부를 설정합니다. REQUIRE 옵션을 설정하지 않더라도 인증 방식이 Caching SHA-2 Authentication 인증방식을 사용하면 암호화된 채널만으로만 접속이 가능합니다.

 

PASSWORD EXPIRE

비밀번호 유효기간을 설정하는 옵션입니다. 별도로 명시하지 않으면 default_password_lifetime에 명시된 기간으로 유효기간이 설정됩니다. 

 

아래 사진은 default_password_lifetime 값을 보여줍니다. user는 30일로 설정했지만 default 값은 0으로 이는 만료하지 않겠다는 의미입니다.

 

1) PASSWORD EXPIRED : 계정 생성과 동시에 비밀번호의 만료처리

2) PASSWORD EXPIRED NEVER : 만료기간 없음

3) PASSWORD EXPIRED DEFAULT : default_password_lifetime 를 만료기간으로 사용

4) PASSWORD EXPIRED INTERVAL n DAY : 비밀번호를 오늘부터 n일자로 설정 

 

PASSWORD HISTORY 

한 번 사용했던 비밀번호를 재사용하지 못하게 하는 옵션입니다. 

root 계정은 변경한 내역이 없어서 비어있습니다. 참고로 user 계정은 mysql 테이블에 접근권한이 없습니다.

1) PASSWORD HISTORY DEFAULT : 시스템 변수에 저장된 개수 만큼 비밀번호의 이력을 저장하며 , 저장된 이력에 있는 비밀번호는 사용할 수 없습니다.

2) PASSWORD HISTORY n : 최근 n개 까지만 저장하며 저장된 이력에 남아있는 비밀번호는 재 사용할 수 없습니다. 

 

PASSWORD REUSE INTERVAL

한 번 사용했던 비밀번호의 재사용 금지기간을 설정하는 옵션입니다. 별도 명시하지 않으면 password_reuse_interval 시스템 변수를 기간으로 설정합니다. 

 

1) PASSWORD REUSE INTERVAL DEFAULT : password_reuse_interval 변수에 저장된 기간으로 설정

2) PASSWORD REUSE INTERVAL n DAY : n일자 이후에 비밀번호를 재사용 가능

 

PASSWORD REQUIRE

비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 필요로 할지 말지 결정하는 옵션입니다. 별도 명시가 없다면 password_require_current 시스템 변수의 값으로 설정됩니다.

 

1) PASSWORD REQUIRE CURRENT : 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정

2) PASSWORD REQUIRE OPTIONAL : 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정

3) PASSWORD REQUIRE DEFAULT : password_require_current 시스템 변수의 값으로 설정

 

ACCOUNT LOCK / UNLOCK

계정을 생성 또는 ALTER USER 명령을 통해 변경할때 계정을 잠글지 풀지 결정하는 옵션입니다. 

 

1) ACCOUNT LOCK : 계정을 잠금

2) ACCOUNT UNLOCK : 계정을 품

 

---

 

비밀번호 관리 

 

좀 더 고수준의 비밀번호를 사용하려면 validate_password 컴포넌트를 설치해야합니다. 고수준의 비밀번호라고 하면 글자의 조합을 강제하거나 금칙어 설정등 유효성체크가 들어간 비밀번호를 말합니다. 

 

현재 설치된 컴포넌트가 없습니다.

 

컴포넌트를 설치해보겠습니다. 

 

INSTALL COMPONENT 'file://component_validate_password';

 

그리고 확인하는 명령어는 다음과 같습니다.

SELECT * FROM mysql.component;

 

컴포넌트에서 제공하는 시스템 변수목록은 다음과 같습니다.

 SHOW GLOBAL VARIABLES LIKE 'validate_password%';

 

 

policy 부분은 보면 기본적으로 MEDIUM으로 설정되어 있습니다.

 

1) LOW : 비밀번호의 길이만 검증

2) MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자,그리고 특수문자의 배합을 검증합니다.

3) STRONG : MEDIUM + 금칙어 포함여부 검사

 

금칙어 파일은 dictonary_file 시스템변수에 금칙어들이 저장된 사전파일을 등록하면 됩니다.

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

 

지금보면 컴포넌트를 사용하여 validate_password가 제공되었지만 MySQL 5.7 버전까지는 플러그인 형태로 제공되었습니다. 플러그인 정보는 mysql.plugin 에서 찾아보면 됩니다. MySQL 8.0 부터 컴포넌트가 도입되어 컴포넌트를 권장하는 편입니다.

 

이중 비밀번호

영어로는 DUAL 비밀번호입니다. 일반적으로 생각하는 이중비밀번호의 의미는 아닙니다. 즉, 하나의 계정에 대해 2개의 패스워드가 모두 정확해야 로그인 되는 것이 아니라 하나의 계정에 2개의 비밀번호중 하나만 맞아도 로그인 되는 것입니다. 이런 기능이 왜 생겨났을까요?

 

일반적으로 많은 응용 프로그램 서버들이 공용으로 데이터베이스 서버를 사용합니다. 이러한 특성 때문에 데이터베이스 서버의 계정 정보는 쉡게 변경하기가 어렵습니다. 특히 서비스가 실행중인 상태에서는 변경이 불가능합니다. 그래서 데이터베이스계정의 비밀번호는 처음 설정한 상태로 몇 년 동안 사용되는 경우가 많습니다. 이러한 불편함 때문에 이중 비밀번호가 생겨났습니다.

 

아래 예시를 통해 이중 비밀번호를 설정해보겠습니다.

 

1) user의 비밀번호를 'old_password' 로 변경합니다.

ALTER USER 'user'@'%' IDENTIFIED BY 'old_password';

2) old_password를 유지한채로 new_password를 프라이머리 비밀번호로 변경합니다. 

ALTER USER 'user'@'%' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

위 처럼 설정하면 old_password 와 new_password 로 2개다 로그인이 가능합니다.

 

권한 ( Privilege)

 

MySQL 5.7 버전까지는 글로벌 권한과 객체 권한으로 나뉘어져 있었습니다.

 

1) 객체권한 - 데이터베이스 혹은 테이블에 적용되는 권한

2) 글로벌권한 - 데이터베이스,테이블 외의 권한

 

MySQL 8.0이 들어서면서 권한 종류가 추가되었습니다. 바로 동적권한 입니다. MySQL 5.7이전 버전의 권한들은 모두 정적권한으로 고정적으로 명시된 권한입니다. 동적권한은 플러그인이나 컴포넌트들이 서리되면 그때 등록되는 권한들입니다.

 

글로벌 권한은 아래처럼 줄 수 있습니다.

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

글로벌 권한을 주었다고 해서 데이터베이스와 테이블접근 권한까지 있는것이 아님을 한번더 확인해보시기 바랍니다. 또한 글로벌 권한은 특정 데이터베이스나 테이블에 권한을 부여할수 없기 때문에 항상 *.* 로 작성해야합니다.

 

'계정이름' 에 권한을 확인할 계정을 적어주면 권한목록을 확인 할 수 있습니다.

SHOW GRANTS FOR '계정이름';

글로벌 권한을 추가했기 때문에 아래의 사진처럼 권한이 등장합니다.

 

아직은 root 계정에서 생성한 데이터베이스인 'testdb'에 접근 할 수 없습니다.

 

 

이제 root 계정으로 재로그인하고 user계정이 testdb 에 접근할 수 있도록 합시다.

grant event on testdb.* to 'user'@'%';

 

 

역할 (ROLE) 

 

MySQL 8.0 버전 부터는 권한을 묶어서 역할(ROLE)을 사용할 수 있게 되었습니다. 이름만 역할이지 사실은 MySQL의 계정과 내부적으로는 똑같습니다.  어떻게 계정과 역할이 똑같을 수가 있는지 알아보고 어떻게 역할을 부여할 수 있는지 알아보겠습니다.

 

 

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 와 writer 계정을 생성해 보겠습니다.

create user reader@'%' IDENTIFIED BY '12345678';
create user writer@'%' IDENTIFIED BY '12345678';

이제 계정에 권한을 부여해 보겠습니다.

 

grant role_testdb_read TO reader@'%';
grant role_testdb_read TO writer@'%';
grant role_testdb_write TO writer@'%';

reader ,writer 로 로그인하여 각각 show grants; 를 입력하면 아래와 같이 나옵니다.

 

이제 reader로 로그인하여 SELECT 해보려고 하지만  권한이 없다는 문구가 표시됩니다.

 

이유는 SELECT current_role() 명령을 통해 확인할 수 있는데 권한은 부여되었지만 활성화되지 않았습니다.

 

권한을 활성화 하기 위해서는 SET ROLE 이라는 명령어를 사용해야합니다.

 

SET ROLE 'role_testdb_read';

 

 

SET ROLE 을 이용하여 역할을 활성화한뒤 SELECT 명령어를 실행하면 결과가 잘 나옵니다.

 

SELECT COUNT(*) FROM testdb.professor;

또한 update 가 되지 않음으로서 reader의 역할이 잘 반영되었습니다.

 

이렇게 ROLE을 부여할때마다 역할을 자동으로 활성화하지 않는 이유는 잘 모르겠지만 MySQL에서는 자동으로 활성화 할 수 있는 시스템 변수를 제공합니다.

 

SET GLOBAL activate_all_roles_on_login=ON;

 

역할과 계정에는 처음에 같은 구조라고 언급했었습니다. 아래 명령어를 통해서 MySQL에 저장된 계정들을 모두 조회해봅시다.

SELECT user,host,account_locked from mysql.user;

 

 

역할과 계정이 모두 user에서 관리가 되고 있는 모습입니다. 

계정에 역할을 부여하는 과정은 실제로는 다른 사용자가 가진 권한을 또 다른 사용자의 권한에 병합하는 과정입니다.

 

그래서 CREATE ROLE 명령어에 호스트가 빠져있는데 이는 실제로 모든 호스트('%') 가 생략된 것입니다.

 

그렇다면 왜 CREATE ROLE 과 CREATE USER를 구분한것일까요?

 

CREATE ROLE은 말그대로 역할을 수행하는 계정을 생성하고 account_locked 컬럼이 Y로 되어있어서 로그인 용도로 사용할 수 없지만 USER는 로그인 용도로 사용가능합니다.

 

역할관계를 좀더 깔끔하게 보고자 한다면 아래 테이블을 참고하면 됩니다.

 

1) mysql.default_roles : 계정별 기본 역할

2) mysql.role_edges : 역할에 부여된 역할 관계 그래프

 

 

 

참고자료 : http://www.kyobobook.co.kr/product/detailViewKor.laf?mallGb=KOR&ejkGb=KOR&barcode=9791158392703 

Real MySQL 8.0 (1권) - 교보문고