3.5 역할(Role)

MySQL 8.0 버전부터는 권한을 묶어서 역할을 사용할 수 있게 됐다.

실제 MySQL 서버 내부적으로 역할은 계정과 똑같은 모습을 하고 있다

 

우선 CREATE ROLE 명령을 이용해 role_emp_read 와 role_emp_write라는 이름의 역할을 정의

CREATE ROLE role_emp_read, role_emp_write;

- CREATE ROLE 명령에서는 빈 껍데기만 있는 역할을 정의

---

GRANT 명령으로 각 역할에 대해 실질적인 권한을 부여하면 된다

## employees DB 의 모든 객체에 대해 읽기 권한만 부여
GRANT SELECT ON employees.* TO role_emp_read;
## employees DB 의 모든 객체에 데이터변경(INSERT, UPDATE, DELETE) 권한 부여
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

---

기본적으로 역할은 그자체로 사용될 수 없고 계정에 부여해야 한다

CREATE 명령으로 reader 와 writer라는 계정 생성

## CREATE 명령으로 reader 와 writer 라는 계정 생성
CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'password';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'password';

- CREATE USER 명령으로 계정은 생성했지만 아무런 권한이 부여되지 않았다

- employees DB에 대해 아무런 쿼리도 실행 할수 없는 상태

---

이제 GRANT 명령으로 reader 와 writer 계정에 역할 부여

## 이제 GRANT 명령으로 reader 와 writer 계정에 역할 부여
GRANT role_emp_read TO reader@'127.0.0.1';
GRANT role_emp_read, role_emp_write TO writer@'127.0.0.1';

- reader 계정에는 role_emp_read 역할 부여

- writer 계정에는 role_emp_read, role_emp_write 역할 부여

---

reader 유저 권한 확인

SHOW GRANTS for reader@'127.0.0.1';

- 그런데 지금 상태에서 reader나 writer 계정으로 로그인해서

- employees DB의 데이터를 조회하거나 변경하면 에러를 만나게 된다

---

SELECT current_role();

- 실제 역할은 부여돼 있지만 계정의 활성화된 역할을 조회해보면 role_emp_read 역할이 없음을 확인할 수 있다

---

reader 계정이 role_emp_reader 역할을 사용할수 있게 하려면 다음과 같이 SET ROLE 명령을 실행해준다

SET ROLE 'role_emp_read';
## 현재 역활 확인
SELECT current_role();
## employees 갯수확인
SELECT COUNT(*) FROM employees.employees;

- MySQL 서버의 역할이 불편하고 수동적으로 보인다

- 이는 MySQL 서버의 역할이 자동으로 활성화되지 않게 설정돼 있기 때문이다

---

MySQl 서버에 로그인할 때 역할을 자동으로 활성화할지 여부를 active_all_roles_on_login 시스템 변수로 설정

set global activate_all_roles_on_login=ON;

- activate_all_roles_on_login 시스템 변수가 ON으로 설정되면 

- 매번 SET ROLE 명령으로 역할을 활성화하지 않아도 로그인과 동시에 부여된 역할이 자동으로 활성화된다

---

MySQL 서버의 역할은 사용자 계정과 거의 같은 모습을 하고 있으며,

MySQL 서버 내부적으로 역할과 계정은 동일한 객체로 취급된다

단지 하나의 사용자 계정에 다른 사용자 계정이 가진 권한을 병합해서 권한 제어가 가능해졌을 뿐이다

## 다음과 같이 mysql DB의 user 테이블을 살펴보면 실제권한과 사용자 계정이 구분 없이 저장된것을 확인할수 있다
select user, host, account_locked from mysql.user;

- mysql DB의 user 테이블에는 방금 생성했던 사용자 계정과 권한이 모두 저장돼 있다

- 역할과 계정의 차이는 account_locked 값이 다를 뿐 아무런 차이가 없다

- user 테이블에 역할이라는 것을 표기하는 플래그 칼럼도 없다

---

그렇다면 MySQL 서버는 계정과 권한을 어떻게 구분할까?

- 하나의 계정에 다른 계정의 권한을 병합하기만 하면 되므로 MySQL 서버는 역할과 계정을 구분할 필요가 없다

---

호스트 부분을 명시하지 않은 경우에는 자동으로 '모든 호스트(%)'가 자동으로 추가된다

- 일반적으로 CREATE USER 명령으로 계정을 생성할 때는 reader@'127.0.0.1'과 같이 계정 이름과 호스트 부분을 함께 명시한다

- 하지만 CREATE ROLE 명령으로 역할을 생성할 때는 호스트 부분을 별도로 명시하지 않았다

- 이것이 역할과 계정의 차이처럼 보일 수 있지만 호스트 부분을 명시하지 않은 경우에는 자동으로 '모든 호스트(%)'가 자동으로 추가된다

CREATE ROLE 
role_emp_read,
role_emp_write;

CREATE ROLE 
role_emp_read@'%',
role_emp_write@'%';

---

이제 계정과 역할이 내부적으로는 똑같은 객체라는 것을 이해했으니,

다음과 같이 호스트 부분을 가진 역할에 대해 한번 고민해보자

CREATE ROLE role_emp_local_read@localhost;

CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'password';

GRANT SELECT ON employees.* TO role_emp_local_read@'localhost';

GRANT role_emp_local_read@'localhost' TO reader@'127.0.0.1';

- 위 예제는 role_emp_local_read@'localhost' 역할을 reader@'127.0.0.1' 계정에 부여하는 예제

- 사용자 계정은 employees DB의 객체들에 대해 SELECT 권한이 부여된다

- 역할의 호스트 부분은 아무런 영향이 없다.

- 만약 역할을 다른 계정에 부여하지 않고 직접 로그인하는 용도로 사용한다면 그때는 역할의 호스트 부분이 중요해진다

---

왜 MySQL 서버에서는 굳이 CREATE ROLE 명령과 CREATE USER 명령을 구분해서 지원할까?

- 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용하기 위해서다

- CREATE USER 명령에 대해서는 권한이 없지만 CREATE ROLE 명령만 실행 가능한 사용자는 역할을 생성할 수 있다

- 이렇게 생성된 역할은 계정과 동일한 객체를 생성하지만 account_locked 칼럼 값이 'Y' 설정돼 있다

- account_locked 칼럼값이 'Y' 은 로그인 용도로 사용할 수 없게 된다