[Real Mysql] 03. 사용자 및 권한

MySQL에서는 사용자 계정을 생성하는 방법이나 각 계정의 권한을 설정하는 방법은 다른 DBMS와는 조금 차이가 있다.

대표적으로는 MySQL의 사용자 계정은 단순히 사용자의 아이디뿐 아니라 해당 사용자가 어느 IP에 접속하고 있는지도 확인한다. 또한 MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할(ROLE)의 개념이 도입되어 권한 세트(ROLE)를 부여하는 것도 가능하다.

사용자 식별

MySQL의 사용자는 다른 DBMS와는 조금 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점도 계정의 일부가 된다.

따라서 MySQL에서 계정을 언급할 떄는 다음과 같이 항상 아이디와 호스트를 함께 명시해야 한다.

'svc_id@'127.0.0.1'

위 사용자 계정은 항상 MySQL 서버가 기동 중인 로컬 호스트에서 svc_id라는 아이디로 접속할 때만 사용될 수 있는 계정이다. 만약 사용자 계정에 다음과 같은 계정만 등록돼 있다면 다른 컴퓨터에서는 svc_id라는 아이디로 접속할 수 없음을 의미한다.

사용자 계정 식별에서 한 가지 주의해야 할 점은 서로 동일한 아이디가 있을 때 MySQL서버가 해당 사용자의 인증을 위해 어떤 계정을 선택하느냐다. 예를 들어, 다음과 같은 2개의 사용자 계정이 있는 MySQL 서버가 있다고 해보자.

'svc_id'@'192.168.0.10'
'svc_id'@'%'

IP 주소가 192.168.0.10인 PC에서 이 MySQL서버에 접속할 때 MySQL서버가 첫 번째 계정 정보를 이용해 인증을 실행할지, 아니면 두 번째 계정정보를 이용할지에 따라 이 접속은 성공할 수 도 있고 실패할 수 도 있다. MySQL은 권한이나 계정 정보에 대해 범위가 가자 작은 것을 항상 먼저 선택한다. 즉, 위 두 계정 정보 가운데 범위가 좁은 것은 %가 포함되지 않은 'svc_id'@'192.168.0.10'이기 때문에 IP가 명시된 계정 정보를 이용해 이 사용자를 인증하게 된다.

사용자 계정 관리

MySQL 8.0부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반 계정으로 구분된다.

시스템 계정은 시스템 계정과 일반 계정을 관리할 수 있지만 일반 계정은 시스템 계정을 관리할 수 없다.

또한 다음과 같이 데이터 베이스 서버 관리와 관련된 중요한 작업은 시스템 계정으로만 수행할 수 있다.

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

MySQL에는 내장된 계정들이 있는데 'root@localhost'를 제외한 3개의 계정은 내부적으로 다른 목적이 존재하기 때문에 삭제되지 않도록 주의하자.

• 'mysql.sys'@'localhost' : MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체들의 DEFINER로 사용되는 계정
• 'mysql.session'@'localhost' : MySQL 플러그인이 서버로 접근할 때 사용되는 계정
• 'mysql.infoschema'@localhost' : information_schema에 정의된 뷰의 DEFINER로 사용되는 계정

계정 생성

MySQL 5.7 버전까지는 GRANT 명령으로 권한의 부여와 동시에 계정 생성이 가능했다. 하지만 MySQL 8.0부터는 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행하도록 바뀌었다. 계정을 생성할 때는 다음과 같은 다양한 옵션을 설정할 수 있다.

• 계정 인증 방식과 비밀번호
• 비밀번호 관련 옵션
• 기본 역할
• SSL 옵션
• 계정 잠금 여부

IDENTIFIED WITH

사용자의 인증 방식과 비밀번호를 설정한다. IDENTIFIED WITH 뒤에는 반드시 인증 방식을 명시해야 하는데, MySQL 서버의 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시해야 한다.

REQUIRE

MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정한다.

PASSWORD EXPIRE

비밀번호 유효 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 유효 기간으로 설정된다.

PASSWORD HISTORY

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

PASSWORD REUSE INTERVAL

한번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정된다.

PASSWORD REQUIRE

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

ACCOUNT LOCK / UNLOCK

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

비밀번호 관리

고수준 비밀번호

MySQL 서버의 비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능뿐만 아니라 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 있다. MySQL 서버에서 비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 되는데, 이 컴포넌트를 설치해야만 이용할 수 있다.

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

• LOW : 비밀번호의 길이만 검증
• MEDIUM: 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
• STRONG : 레벨의 검증을 모두 수행하며, 금칙어가 포함됐는지 여부까지 검증

이중 비밀번호

일반적으로 많은 응용 프로그램 서버들이 공용으로 데이터베이스 서버를 사용하기 때문에 데이터베이스 서버의 계정 정보는 응용 프로그램 서버로부터 공용으로 사용되는 경우가 많다. 이러한 구현 특성으로 인해 데이터베이스 서버의 계정 정보를 쉽게 변경하기가 어려운데, 그중에서도 데이터베이스 계정의 비밀번호는 서비스가 실행 중인 상태에서 변경이 불가능했다. 그래서 서비스에서 데이터베이스 계정의 비밀번호는 처음 설정한 상태로 몇 년 동안 사용되는 경우가 많다. 지금까지는 서비스를 모두 멈추지 않고서는 비밀번호를 변경하는 것은 불가능했다. 이러한 문제를 해결하기 위해 MySQL 8.0부터는 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능을 추가했다.

2개의 비밀번호는 프라이머리와 세컨더리로 구분된다. 최근에 설정된 비밀번호는 프라이머리 이전 비밀번호는 세컨더리이다.

-- // 비밀번호를 "ytrewq"로 설정
mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password'

-- // 비밀번호를 "qwerty"로 변경하면서 기존 비밀번호를 세컨더리 비밀번호로 설정
mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

첫 번째 ALTER USER 명령이 실행되면 root 계정의 프라이머리 비밀번호는 'old_password'로 변경되고 세컨더리 비밀번호는 빈 상태가 된다. 그리고 두 번째 ALTER USER 명령이 실행되면 이전 비밀번호였던 'old_password'는 세컨더리 비밀번호로 설정되고 새롭게 설정한 'new_password'는 프라이머리 비밀번호가 된다. 이 상태는 두 비밀번호 주 ㅇ아무거나 입력해도 로그인된다. 세컨더리 비밀번호를 꼭 삭제해야 하는 건 아니지만 보안을 위해 삭제하는 것이 좋다.

mysql > ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

권한

MySQL 5.7까지 권한은 글로벌 권한과 객체 권한으로 구분됐다. 데이터베이스나 테이블 이외의 객체에 적용되는 권한을 글로벌 권한이라고 하며, 데이터베이스나 테이블을 제어하는 데 필요한 권한을 객체권한이라 한다. 객체 권한은 GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시해야 하며, 반대로 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야 한다.

글로벌 권한

mysql > GRANT SUPER ON *.* TO 'user'@'localhost';

DB 권한

mysql > GRANT EVENT ON *.* TO 'user'@'localhost';
mysql > GRANT EVENT ON employee.* TO 'user'@'localhost';

테이블 권한

mysql GRANT SELECT,INSERT,UPDATE,DELTE ON *.* TO 'user'@'localhost';

역할(ROLE)

MySQL 8.0 버전부터 권한을 묶어서 역할(ROLE)을 사용할 수 있게 됐다. 실제 내부적으로 역할(ROLE)은 계정과 똑같은 모습을 하고 있다.

mysql > CREATE ROLE role_emp_read, role_emp_write
mysql > GRANT SELECT ON employee.* TO role_emp_read;
mysql > GRANT INSERT, UPDATE, DELETE ON employee.* TO role_emp_write;

mysql > CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty';
mysql > GRANT role_emp_read to reader@'127.0.0.1';

권한을 확인해 보자

mysql > SHOW GRANTS;

실제로 쿼리를 실행해 보면 오류가 발생한다. 실제 역할은 부여돼 있지만 계정의 활성화된 역할을 조회해 보면 role_emp_read역할이 없음을 확인할 수 있다.

reader 계정이 role_emp_read 역할을 사용할 수 있게 하려면 다음과 같이 SET ROLE 명령을 실행해 해당 역할을 활성화해야 한다.

mysql > SET ROLE 'role_emp_read';
mysql > SELECT current_role();

사용자가 MySQL 서버에 로그인할 때 역할을 자동으로 활성화할지 여부를 activate_all_roles_on_login 시스템 변수로 설정할 수 있다. 매번 SET ROLE 명령으로 역할을 활성화하지 않아도 로그인과 동시에 부여된 역할이 자동으로 활성화된다.