Real My SQL 8.0 읽고 공부하기 - ② 사용자 및 권한

1. 사용자 식별

MySQL의 사용자는 사용자의 접속지점 (클라이언트가 실행된 호스트명 혹은 도메인 또는 IP 주소)이 계정의 일부가 된다.

'cdt'@'127.0.0.1' 이면 서버와 같은 IP에서 cdt라는 아이디로 접속할 수 있다는 것을 의미한다.

만약 모든 IP를 접속 허용 가능하게 하고 싶다면 'cdt'@'%' 로 진행하면 된다.

 

만약 'cdt'@'%'와 'cdt'@'192.168.0.1' 이있다면 우선 순위는 조금 더 명확한 주소를 가진 'cdt'@'192.168.0.1'로 접속을 시도하므로

유의하여야 한다.

 

---

 

2. 사용자 계정 관리

 

1) 시스템 계정과 일반 계정

 

MySQL은 SYSTEM_USER 권한 여부에 따라서 시스템 계정과 일반 계정을 구분한다.

일반적으로 시스템 계정은 데이터베이스 서버관리자를 위한 계정이며, 일반계정은 응용프로그램이나, 개발자를 위한 계정이다.

시스템 계정은 계정관리, 다른 세션 또는 실행중 쿼리 강제 종료, 스토어드 프로그램 생성시 DEFINER를 타사용자로 설정 할 수 있다.

 

 

2) 계정 생성

 

일반적인 계정 생성문은 다음과 같다.

CREATE USER 'th'@'%'
	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 기본 인증방식을 사용하고자 한다면

~~ BY 'password' 으로 명시하여야 한다.

 

REQUIRE: MySQL 접속시 암호화된 SSL/TLS 채널을 사용할지 여부를 설정한다. 그러나 만약 설정하지 않고

위에서 비밀번호 인증 방식을 Caching SHA-2 Authentication 사용하면 암호화된 채널만으로 MySQL 서버에 접속할 수 있다.

 

PASSWORD EXPIRE: 비밀번호의 유효 기간을 설정하는 옵션이다. (현재 30일을 만료기간으로 두었다.)

 

PASSWORD HISTORY: 한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션 (이를 기억하기 위해 password_history테이블 사용)

 

PASSWORD REUSE INTERVAL DEFAULT: 한 번 사용했던 비밀번호의 재사용 금지기간을 설정하는 옵션

(현재는 REUSE INTERVAL 변수에 설정된 값으로 사용)

 

PASSWORD REQUIRE: 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호를 필요할지 말지 결정 옵션

 

ACCOUNT LOCK / UNLOCK: 계정 생성시 혹은 ALTER USER 명령을 통해 계정 사용을 잠금 / 해제

 

 

---

 

3. 비밀번호 관리

 

1) 고수준 비밀번호

 

쉬운 비밀번호를 사용하지 않기 위해 글자 조합 강제 혹은 금칙어 설정을 위해 유효성 체크 규칙을 사용할 수 있다.

이를 이용하려면 validate_password 컴포넌트를 설치해야 한다.

INSTALL COMPONENT 'file://component_validate_password'; 명령어를 통해 설치하고

(삭제는 UNINSTALL COMPONENT 'file://component_validate_password'; )

SELECT * FROM mysql.component; 를 통해 확인할 수 있다.

그 후 컴포넌트가 제공하는 시스템 변수를  SHOW GLOBAL VARIABLES LIKE 'validate_password%'; 명령어를 통해 확인해보자.

비밀번호 정책은 3가지가 있으며,

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

MEDIUM: 비밀번호의 길이를 검증하며, 숫자 대소문자 특수문자의 배합 검증

STRONG: MIDIUM을 포함, 금칙어 포함 여부를 검증

으로 되어있다.

 

 

2) 이중 비밀번호

 

데이터베이스 계정의 비밀번호는 서비스가 실행중인 상태에서 변경이 불가능하였는데,

이는 서비스를 종료할 수 없는 상태와 같이 이어지면서 몇년 동안 계속 사용되는 경우가 많다.

이 같은 문제를 해결하기 위해 8.0에서는 계정의 비밀번호를 2개 값을 사용할 수 있는 기능을 추가하였고 

이 기능을 이중 비밀번호라고 소개한다. (두개의 비밀번호중 하나만 일치하여도 로그인 성공한다.)

 

다음 두개의 명령어로 기존 세컨더리 비밀번호를 설정하고, 추후 삭제한다.

// 비밀번호를 'kth_1234' 로 변경
ALTER USER 'root'@'localhost' IDENTIFIED BY 'kth_1234';

// 비밀번호를 'kth_123456' 으로 변경 후 기존 비밀번호를 세컨더리 번호로 설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'kth_123456' RETAIN CURRENT PASSWORD;

// 세컨더리 번호 삭제
ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

 

 

 

---

 

4. 권한

 

1) 글로벌 권한

 

데이터베이스나 테이블 이외의 객체에 적용되는 권한 - 특정 객체를 명시하지 말아야한다.

ALL 권한을 부여하면 글로벌 수준에서 가능한 모든 권한을 부여받는다.

주로 서버관리에 대한 권한이 있다. (ex) CREATE ROLE, CREATE USER , SHOW DATABASES...

 

2) 객체 권한

 

데이터베이스나 테이블을 제어하는데 필요한 권한 - 특정 객체를 명시해야한다.

ALL 권한을 부여하면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여한다.

(ex) CREATE, DROP, INSERTE, DELETE, SELECT...

 

3) 동적 권한

 

MySQL8.0부터 추가된 권한인데, 서버가 시작되면서 동적으로 생성되는 권한을 의미한다.

MySQL 서버의 컴포넌트나 플러그인이 설치되면 그때부터 등록되는 권한이 포함되며,

5.7버전의 SUPER권한도 여기에 잘게 분해되어 동적권한으로 분산됬다.

(ex) INNODB_REDO_LOG_ARCHIVE, SYSTEM_USER ...

 

 

4) 권한 부여

 

GRANT privilege_list ON db.table TO 'cdt'@'localhost';

옵션을 같이 주고 싶으면

GRANT privilege_list ON db.table TO 'cdt'@'localhost' WITH GRANT OPTION;

ON 뒤에는 어떤 DB의 어떤 오브젝트에 대한 권한을 부여할지, TO는 누구에게 부여할지를 정할 수 있다.

 

● 글로벌 권한

GRANT SUPER ON *.* TO 'cdt'@'localhost';

글로벌 권한은 ON 절에 *.*를 사용해야한다. DB단위나 오브젝트 단위로 부여할 수 있는 권한이 아니어서 그렇다.

 

 

● DB 권한

GRANT EVENT ON *.* TO 'cdt'@'localhost';
GRANT EVENT ON employees.* TO 'cdt'@'localhost'

DB 권한은 ON 절에 *.*, 특정DB.*이 가능하다. 단 오브젝트 권한처럼 DB.table은 불가능하다.

 

 

● 테이블 권한

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'cdt'@'localhost';
GRANT SELECT,INSERT,UPDATE,DELETE ON employees.* TO 'cdt'@'localhost';
GRANT SELECT,INSERT,UPDATE,DELETE ON employees.role TO 'cdt'@'localhost';

테이블 권한은 특정 오브젝트에 대해서도 권한이 부여가능하다.

만약 특정 컬럼에만 권한을 부여하려고 하면

GRANT SELECT,INSERT,UPDATE(role_name) ON employees.role TO 'cdt'@'localhost';

과 같이 권한을 부여할 수 있다.

 

특정 컬럼에 대한 권한을 설정하면 전체적인 성능에 영향을 미칠 수 있으므로 자주 사용을 하지는 않는다.

꼭 필요하다면 별도의 뷰(View)테이블을 만들어 뷰자체에 대한 테이블 권한만 체크하게 하면 된다.

 

 

※ 각 계정이나 권한에 부여된 권한 혹은 역할을 확인하기 위해서는 SHOW GRANTS 명령어나 DB 권한 관련 테이블을 조회한다.

 

 

 

---

 

5. 역할

 

8.0 부터 권한을 묶어서 역할(Role)으로 사용할 수 있게 되었다.

역할을 만들고

CREATE ROLE 
role_read,
role_write

 

역할에 권한을 부여하고

GRANT SELECT ON employees.* role_emp_read;
GRANT INSERT, UPDATE, DELETE employees.* role_emp_write;

유저를 만들고

CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'cdt';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'cdt';

역할을 부여한다.

GRANT role_emp_read TO reader@'127.0.0.1';
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

이렇게 부여된 역할을

SELECT * FROM mysql.role_edges;

 

로 확인 할 수 있다.

 

 

이제 접속을 하려면

mysql -u reader -h 127.0.0.1 -p
mysql -u writer -h 127.0.0.1 -p

로 할 수 있는데, 들어가서 Show Grants를 통해 권한을 확인해보면

 

로 나옴을 확인할 수 있다.

 

책에서는 역할이 활성화 되어있지 않은 상태로 초기화 된다고하는데 버전 변경에 따라 변동된것 같다.

만약 활성화 되지 않은 경우 SET ROLE 'role_emp_read'; 을 통해 활성화 할 수 있다.

만약 로그인시 자동으로 활성화 하고 싶은 설정을 하려는경우

SET GLOBAL activate_all_roles_on_login=ON; 를 통해 활성화 시킬 수 있다.

 

역할은 사실 user테이블에 하나의 유저로써 생성되어있으며 account_locked이 활성화(로그인 불가능)되어있을뿐

다른 사용자 계정과 차이가 없다.

따라서 CREATE ROLE role_emp_read@'192.168.0.1'; 처럼 호스트를 붙일 수도 있고 안붙일 수도 있다.

추후 해당 역할을 로그인으로 사용하면 접속 가능한 곳이 정해질 수 있으나, 단순 역할 부여에는 호스트가 상관이 없다.

 

MySQL에서는 역할로 권한을 다른 사용자에게 병합해주는 방법으로 사용자와 같이 만들었으며,

또한, 데이터베이스 관리 직무를 분리하여 보안을 강화하기 위한 용도로 역할을 만들었다.