3.1 사용자 식별
MySQL의 사용자는 다른 DBMS와 조금 다르게 사용자의 계정뿐 아니라 사용자의 접속 지접(클라이언트가 실행된 호스트명이나 도메인 또는 IP 주소)도 계정의 일부가 된다. 따라서 MySQL에서 계정을 언급할 때는 다음과 같이 항상 아이디와 호스트를 함께 명시해야 한다. 다음의 사용자 계정은 항상 MySQL 서버가 기동 중인 로컬 호스트에서 svc_id라는 아이디로 접속할 때만 사용할 수 있는 계정이다.
'svc_id'@'127.0.0.1'
만약 모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고 싶다면, %를 사용하면 된다.
'svc_id'@'192.168.0.10' (pw : 123)
'svc_id'@'%' (pw : abc)
위의 두 계정 중 어떤 계정 정보를 사용할지에 대해, MySQL은 더 좁은 범위인 첫 번째 계정을 사용해 접속을 시도한다. 사용자가 IP 192.168.0.10인 PC에서 'svc_id'라는 아이디와 abc라는 비밀번호로 로그인할 경우, 접속이 거절될 것이다.
3.2 사용자 계정 관리
3.2.1 시스템 계정과 일반 계정
MySQL 8.0부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반 계정으로 구분된다.
시스템 계정은 DB서버 관리자(DBA)를 위한 계정, 일반 계정은 응용 프로그램이나 개발자를 위한 계정 정도로 생각하자.
다음과 같은 중요한 작업의 경우 시스템 계정으로만 수행할 수 있다.
•
계정 관리(계정 생성 및 삭제, 계정의 권한 부여 및 제거)
•
다른 세션(Connect) 또는 그 세션에서 실행 중인 쿼리를 강제 종료
•
스토어드 프로그램 생성 시 DEFINER를 타 사용자로 지정
삭제되지 않도록 주의해야할 내장 계정
'mysql.sys'@'localhost' : MySQL 8.0부터 기본으로 내장된 sys 스키마의 객체 (뷰나 함수, 그리고 프로시저)들의 DEFINER로 사용되는 계정
'mysql.session'@'localhost' : MySQL 플러그인이 서버로 접근할 때 사용되는 계정
'mysql.infoschema'@'localhost' : information_schema에 정의된 뷰의 DEFINER로 사용되는 계정
3.2.2 계정 생성
MySQL 8.0 버전부터 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행.
계정 생성시에는 다음과 같은 다양한 옵션을 설정할 수 있다.
•
계정의 인증 방식과 비밀번호
•
비밀번호 관련 옵션 (비밀번호 유효 기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 기간)
•
기본 역할 (Role)
•
SSL 옵션
•
계정 잠금 여부
일반적으로 많이 사용되는 옵션을 가진 CREATE UESR 명령은 다음과 같다.
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;
SQL
복사
IDENTIFIED WITH
사용자의 인증 방식과 비밀번호를 설정한다.
IDENTIFIED WITH 뒤에는 반드시 인증 방식(인증 플러그인의 이름)을 명시
MySQL 서버의 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시
다양한 인증 방식을 플러그인 형태로 제공하며, 다음 4가지 방식이 대표적.
인증 방식
Caching SHA-2 Authentification 방식이 기본 인증이지만, SSL/TLS 또는 RSA 키페어를 필요로 하기 때문에 기존 방식과는 다른 방식으로 접속해야 한다.
호환성을 고려하면 Native Authentification 인증 방식으로 계정을 생성해야 할 수도 있다.
-- // Native Authentification을 기본 인증 방식으로 설정
SET GLOBAL default_authentification_plugin="mysql_native_password"
SQL
복사
CREATE USER 또는 ALTER USER 명령을 이용해 연결 방식과 비밀번호 옵션, 자원 사용과 관련된 여러 옵션을 설정할 수 있다.
⇒ 현재는 read only variable이라 수정 안되는 문제 발생.
REQUIRE
MySQL 서버에 접속할 때 암호화 된 SSL/TLS 채널을 사용할지 여부를 설정.
별도로 설정하지 않으면 비암호화 채널로 연결된다.
REQUIRE 옵션을 SSL로 설정하지 않았다고 해도 Caching SHA-2 Authentification 인증 방식을 사용하면 암호화된 채널만으로 접속할 수 있게 된다.
PASSWORD EXPIRE
비밀번호의 유효 기간을 설정하는 옵션.
별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정
개발자나 데이터베이스 관리자의 비밀번호는 유효기간을 설정하는 것이 보안상 안전하지만 응용 프로그램 접속용 계정에 유효기간을 설정하는 것은 위험할 수 있으므로 주의
설정 가능한 옵션은 다음과 같다.
•
PASSWORD EXPIRE
◦
계정 생성과 동시에 비밀번호의 만료 처리
•
PASSWORD EXPIRE NEVER
◦
계정 비밀번호의 만료 기간 없음
•
PASSWORD EXPIRE DEFAULT
◦
default_password_lifetime 시스템 변수에 저장된 기간으로 비밀번호의 유효 기간 설정
•
PASSWORD EXPIRE INTERVAL n DAY
◦
비밀번호의 유효 기간을 오늘로부터 n일자로 설정
PASSWORD HISTORY
한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션
설정 가능한 옵션은 다음과 같다.
•
PASSWORD HISTORY DEFAULT
◦
시스템 변수에 저장된 개수만큼 비밀번호 이력을 저장
◦
저장된 이력에 남아있는 비밀번호는 재사용할 수 없다.
•
PASSWORD DEFAULT n
◦
비밀번호의 이력을 최근 n개까지만 저장
◦
저장된 이력에 남아있는 비밀번호는 재사용할 수 없다.
MySQL 서버는 mysql DB의 password_history 테이블을 사용해서 이전 사용했던 비밀번호를 서버가 기억
PASSWORD REUSE INTERVAL
한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션
별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정
설정 가능한 옵션은 다음과 같다.
•
PASSWORD REUSE INTERVAL DEFAULT
◦
password_reuse_interval 시스템 변수에 저장된 기간으로 설정
•
PASSWORD REUSE INTERVAL n DAY
◦
n일자 이후에 비밀번호를 재사용할 수 있게 설정
PASSWORD REQUIRE
비밀번호가 만료되어 새로운 비밀번호로 변경할 때, 현재 비밀번호(변경하기 전 비밀번호)를 필요로 할지 말지를 결정하는 옵션.
별도로 명시되지 않으면 password_require_current 시스템 변수의 값으로 설정
설정 가능한 옵션은 다음과 같다.
•
PASSWORD REQUIRE CURRENT
◦
비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
•
PASSWORD REQUIRE OPTIONAL
◦
비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
•
PASSWORD REQUIRE DEFAULT
◦
password_require_current 시스템 변수의 값으로 설정
ACCOUNT LOCK / UNLOCK
계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부를 결정
•
ACCOUNT LOCK
◦
계정을 사용하지 못하게 잠금
•
ACCOUNT UNLOCK
◦
잠김 계정을 다시 사용 가능한 상태로 잠금 해제
3.3 비밀번호 관리
3.3.1 고수준 비밀번호
비밀번호는 유효기간이나 이력 관리를 통한 재사용 금지 기능뿐만 아니라 비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 있다.
비밀번호의 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 이용하면 되는데, 우선 컴포넌트를 설치해야 한다.
# validate_password 컴포넌트 설치
mysql> INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn |
+--------------+--------------------+------------------------------------+
| 1 | 1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name | Value |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0 |
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+-------------------------------------------------+--------+
8 rows in set (0.01 sec)
SQL
복사
비밀번호 정책은 크게 다음 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에 설정된 사전 파일에 명시된 단어를 포함하고 있는지
MySQL 서버에 금칙어 파일을 등록하면 된다.
비밀번호 금칙어는 validate_password.policy 가 STRONG인 경우에만 작동하므로 금칙어를 적용하려면 함께 변경해야 한다.
SET GLOBAL validate_password.dictionary_file='prohibitive_word.data';
SET GLOBAL validate_password.policy='STRONG';
SQL
복사
3.3.2 이중 비밀번호
데이터베이스 서버의 계정 정보는 쉽게 변경하기 어렵다.
그중에서도 데이터베이스 계정의 비밀번호는 서비스가 실행 중인 상태에서는 변경이 불가능했다.
이러한 문제를 해결하기 위해 MySQL 8.0 버전부터는 계정의 비밀번호로 2개의 값을 동시에 사용할 수 있는 기능 추가했다.
이 기능을 이중 비밀번호(Dual Password)라고 한다.
•
비밀번호 2개를 모두 맞게 입력했을 때 로그인되는 것이 아니라 2개의 비밀번호 중 하나만 일치하면 로그인이 통과되는 것을 의미
-- // 비밀번호를 "abc"로 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';
-- // 비밀번호를 "123"으로 변경하면서 기존 비밀번호를 세컨더리 비밀번호로 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;
SQL
복사
MySQL 서버의 이중 비밀번호 기능은 하나의 계정에 대해 2개의 비밀번호를 동시에 설정하는 것이다.
2개의 비밀번호는 프라이머리(Primary)와 세컨더리(Secondary)로 구분한다. 최근에 설정한 비밀번호가 프라이머리, 이전 비밀 번호는 세컨더리.
기존 비밀 번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션 추가
3.4 권한(Privilege)
5.7 버전까지 권한은 아래의 표와 같이 글로벌(Global) 권한과 객체 단위의 권한으로 구분
•
글로벌 권한
◦
데이터베이스나 테이블 이외의 객체에 적용되는 권한을
◦
GRANT 명령에서 특정객체를 명시하지 말아야한다.
•
객체 권한
◦
데이터베이스나 테이블을 제어하는 데 필요한 권한
◦
GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시
•
예외
◦
ALL(또는 ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용
◦
특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한 부여
◦
글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한 부여
MySQL 8.0 부터는 동적 권한이 더 추가 됐다.
정적 권한은 MySQL 서버의 소스코드에 고정적으로 명시되어 있는 권한
동적 권한은 (일부는 명시되어 있기도 하지만) MySQL 서버가 시작되면서 동적으로 생성되는 권한
•
예를 들어, MySQL 서버의 컴포넌트나 플로그인이 설치되면 그 때 등록되는 권한
이전 버전에서 꼭 필요한 권한인 SUPER 가 8.0 버전부터는 잘게 쪼개져 동적 권한으로 분산됐다.
→ 백업 관리자와 복제 관리자 개별로 꼭 필요한 권한만 부여할 수 있게 되었다.
3.4.1 권한 부여
사용자에게 권한을 부여할 때는 GRANT 명령어를 사용한다.
각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.
GRANT privilege_list ON db.table TO 'user'@'host'
SQL
복사
8.0버전 부터 존재하지 않는 사용자에 대해 GRANT 명령이 실행되면 에러가 발생한다. 따라서 반드시 사용자를 먼저 생성하고 GRANT 명령으로 권한을 부여해야한다.
→ ERROR 3619 (HY000): Illegal privilege level specified for table 이런식
GRANT OPTION 권한은 다른 권한과 달리 GRANT 명령의 마지막에 WITH GRANT OPTION을 명시해서 부여.
privilege_list 에는 구분자(,)를 써서 앞의 명시된 권한 여러 개를 동시에 명시할 수 있다.
•
TO 키위드 뒤에는 권한을 부여할 대상 사용자를 명시
•
ON 키워드 뒤에는 어떤 DB의 어떤 오브젝트에 권한을 부여할지 결정할 수 있는데,
권한의 범위에 따라 사용하는 방법이 달라진다.
3.4.1.1 글로벌 권한
GRANT SUPER ON *.* TO 'user'@'localhost';
SQL
복사
글로벌 권한
글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에 권한을 부여할 때 GRANT 명령의 ON 절에는 항상 *.*를 사용하게 된다.
*.*는 DB의 모든 오브젝트(테이블, 스토어드 프로시저, 함수 등)를 포함해서 MySQL 서버 전체를 의미한다.
CREATE USER나 CREATE ROLE과 같은 글로벌 권한은 DB 단위나 오브젝트 단위로 부여할 수 있는 권한이 아니므로 항상 *.*로만 대상을 사용할 수 있다.
3.4.1.2 DB 권한
GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT EVENT ON employees.* TO 'user'@'localhost';
SQL
복사
DB 권한
DB 권한은 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있기 때문에 위의 예와 같이 ON절에 *.* 이나 employee.* 모두 사용할 수 있다.
→ 여기서 DB라 함은 DB내부에 존재하는 테이블뿐만 아니라 스토어드 프로그램들도 모두 포함.
DB 권한만 부여하는 경우에는(DB 권한은 테이블에 대해 부여할 수 없기 때문에) employees, department와 같이 테이블까지 명시할 수 없다.
DB 권한은 서버의 모든 DB에 적용할 수 있으므로 대상에 *.*을 사용할 수 있다.
특정 DB에 대해서만 권한을 부여하는 것을 db.*처럼 대상을 설청하는 것도 가능하다. 하지만 오브젝트 권한처럼 db.table로 오브젝트(테이블)까지 명시할 수는 없다.
3.4.1.3 테이블 권한
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.department TO 'user'@'localhost';
SQL
복사
테이블 권한
특정 DB의 특정 컬럼에 대해서만 권한을 부여하는 경우이다.
컬럼에 부여할 수 있는 권한은 SELECT, INSERT, UPDATE로 3가지며 DELETE는 제외한다.
각 권한 뒤에 컬럼을 명시하는 형태로 부여한다.
각 계정이나 권한에 부여된 권한이나 역할을 확인하기 위해서는 SHOW GRANTS 를 사용한다.
표 형태로 보려면 mysql DB의 권한 관련 테이블 참조
3.5 역할(Role)
8.0 버전부터 권한을 묶어서 역할(Role)을 사용할 수 있게 됐다.
우선 CREATE ROLE 명령을 이용해 role_emp_read 와 role_emp_write 라는 역할을 정의해보자.
CREATE ROLE
role_emp_read,
role_emp_write;
SQL
복사
위의 CREATE ROLE 명령만으로는 빈 껍데기만 있는 역할을 정의하는 것이다. GRANT 명령으로 각 역할에 대해 실질적인 권한을 부여하면 된다.
GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;
SQL
복사
기본적으로 역할은 그 자체로 사용될 수 없고 계정에 부여해야 하므로 CREATE USER 명령으로 reader와 writer라는 계정을 생성해보자.
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'Role1234!';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'Role1234!';
SQL
복사
계정을 생성한 후 권한을 부여한다.
GRANT role_emp_read TO reader@'127.0.0.1';
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';
SQL
복사
부여된 권한이 사용될 수 있게 하려면 SET ROLE 명령어를 실행해 해당 역할을 활성화한다.
역할이 활성화되면 그역할이 가진 권한은 사용할 수 있는 상태가 되지만 계정이 로그아웃됐다가 다시 로그인하면 역할이 활성화되지 않은 상태로 초기화
자동으로 역할을 활성화하려면 activate_all_roles_on_login 시스템 변수를 설정
SET GLOBAL activate_all_roles_on_login=ON;
SQL
복사
