MySQL Role 권한 설정

MySQL 8 버전에서 지원하는 role 기능에 대해서 소개해보고자 한다.

 

 

1. MySQL Role 이란?

2. MySQL Role 기본 쿼리

3. 조건 별 Role 권한 할당

 

 

 

 

---

1. MySQL Role 이란?

말 그대로 역할을 의미한다. 더 풀어서 설명하자면, 권한을 모아 둔 역할이다. 

마치 '학생은 다른 학생들의 개인 정보를 볼 수 없다.', '선생님은 본인 반에 한하여 학생 정보를 볼 수 있다.'와 같이, 각자의 role에 맞는 역할이 있는 것과 같다.

이를 통해 집합에 대한 권한을 한번에 설정 및 관리할 수 있으므로, 사용자에 대한 권한 관리 및 운영이 더욱 수월해지게 된다는 장점이 있다.

 

 

 

1-1. Role 생성 순서 및 구조

 

즉, role 을 생성하고 & role에 권한을 할당하고 & 이로써 특정 권한이 부여된 role을 특정 user에 할당한다고 생각하면 된다.

 

 

1-2. 하나의 role은 여러 명이 가질 수 있는가 & 한 사람이 여러 개의 role을 가질 수 있는가

role을 가질 수 있는 대상은 여러 명이고, user도 여러 role을 가질 수 있다.

마치 학생 이라는 role을 여러 명의 학생이 가질 수 있고, 선생님이라는 role을 여러 선생님이 가질 수 있듯 말이다.

또한 어떤 선생님은 국어 선생님이자 1학년 담임 선생님일 수 있다. 즉 이처럼, 한 개의 user가 2개 혹은 여러 개의 role을 가질 수도 있다.

 

하지만 user에는 default role이 설정할 수 있는데, 이때 default role로 설정된 role만이 초기 활성화된다.

따라서, 전체 권한을 부여하더라도 default role으로 select 만 가능한 권한을 설정한다면, 

Error Code: 1142. INSERT command denied to user '<user_name>'@'<host>'

라는 에러를 뱉는다.

하지만 default role로 설정할 수 있는 role 은 하나가 아닌 여러 개여도 되기 때문에, 해당 user 에 할당된 모든 role을 default role 로 활성화할 수도 있다.

 

 

---

 

2. MySQL Role 기본 쿼리

기본 쿼리는 문서에 잘 나와 있으므로, 서술 전 문서 링크를 우선 공유하겠다.

https://dev.mysql.com/doc/refman/8.0/en/roles.html

MySQL :: MySQL 8.0 Reference Manual :: 6.2.10 Using Roles

 

중요한 내용 몇 가지를 소개해보겠다.

 

 

2-1. Role 생성과 삭제

CREATE role '<role_name>';
DROP role '<role_name>';

CREATE와 DROP으로 role을 생성 및 삭제할 수 있다.

 

 

 

2-2. Role 권한 할당

GRANT SELECT ON *.* TO '<role_name>';
GRANT SELECT, DELETE ON *.* TO '<role_name>';

GRANT TO 쿼리를 통해 권한을 할당할 수 있다.

또한 만약, 할당하고 싶은 권한이 여러 개라면 , 를 통해 여러 개의 권한을 작성할 수 있다.

 

 

 

2-3. Role 권한 삭제

REVOKE SELECT ON *.* FROM '<role_name>';
REVOKE SELECT, DELETE ON *.* FROM '<role_name>';

REVOKE FROM을 이용해서 권한을 삭제할 수도 있다.

권한 할당하는 GRANT TO 쿼리와 로직이 상당히 유사하다.

 

 

 

2-4. Role 권한 확인

SHOW GRANTS FOR '<role_name>'@'%';

SHOW GRANTS를 통해 role에 할당된 권한을 확인할 수 있다.

 

 

 

2-5. 유저에게 권한 할당

GRANT '<role_name>' TO '<user_name>'@'%';

SET DEFAULT ROLE '<role_name>' TO '<user_name>';
SET DEFAULT ROLE '<role_name1>','<role_name2>' TO '<user_name>';
SET DEFAULT ROLE ALL TO '<user_name>';

GRANT TO 쿼리로 특정 user에 role을 할당하고 & SET DEFAULT ROLE 쿼리로 디폴트 role을 세팅할 수 있다.

하지만 만약, 특정 user에 2개 role을 default role로 설정하고 싶다면, 2개의 role name을 작성해줘도 default role 로서 두 가지의 role에 대한 권한을 지닐 수 있다.

혹은, 만약 특정 user가 갖고 있는 모든 role 에 대한 활성화를 시킨다면, SET DEFAULT ROLE ALL 쿼리를 날려주면 된다.

 

만약, SET DEFAULT ROLE ALL 쿼리 이후,
해당 user에 추가적인 role을 나중에 누군가가 할당한다면, 기존에 있던 role들만 default role 로 인정될까 혹은 추가된 role도 default role로 인식될까? 라는 궁금증이 생겨서 테스트 해봤는데, 뒤늦게 추가된 role도 default role로 인식된다.

 

 

 

2-6. 유저에 할당된 role 확인

 

SELECT * FROM mysql.role_edges WHERE TO_USER='<user_name>';
SELECT * FROM mysql.role_edges WHERE TO_USER='<user_name>' AND TO_HOST='<host>';

mysql.role_edges 테이블을 확인하면 user에 할당된 role 을 확인할 수 있다.

만약 user의 host 가 특정되어 있다면, 두 번째처럼 AND TO_HOST에 대한 WHERE 절을 추가해주면 더 보기 수월하다.

 

 

 

---

 

 

3. 조건 별 Role 권한 할당

 

3-1. 특정 db, 특정 table에 대한 권한을 부여하고 싶을 때

GRANT SELECT ON '<db_name>'.* TO '<role_name>';
GRANT SELECT ON '<db_name>'.'<table_name>' TO '<role_name>';

GRANT ~ ON *.* 에서 첫 번째 * 은 db, 두 번째 *은 table을 의미한다.

*.* 일 경우, 모든 db에 대한 모든 table에 지정한 권한을 모두 부여한다는 의미고,

<db_name>.* 은 <db_name>의 모든 table에 대해서만(다른 db에 대한 권한 X) 권한을 부여한다는 의미다.

 

 

 

3-2. 특정 테이블에 대한 column 에만 권한을 부여하고 싶을 때

GRANT SELECT("<column_name>") ON ;'<db_name>'.'<table_name>' TO '
<role_name>';
GRANT SELECT("<column_name1>, <column_name2>, <column_name3>") ON ;'<db_name>'.'<table_name>' TO '
<role_name>';

만약 특정 role에 특정 테이블의 특정 column 에만 조회가 가능한 권한을 부여하고 싶다면, 위와 같이 role에 권한을 추가하면 된다.

 

 

 

3-3. 특정 테이블에 대한 접근 권한만을 제외하고 권한을 부여하고 싶을 때

내가 찾아본 바로는, 아쉽게도 아직까지(22년 10월)는 이 기능을 제공하지 않는 것으로 알고 있다.

따라서 아래와 같은 쿼리문으로 원하는 권한만 제외한 쿼리 리스트를 출력하여 한 번에 쿼리 하는 것이 현재로서 최선인 것 같다.

(만약 제가 알고 있는 사실과 다르다면, 해당 기능을 현재 제공하고 있다는 사실을 아시는 분이 계신다면 공유 부탁드립니다...ㅎ)

 

 

3-3-1. (예시) 특정 테이블을 제외하고 select 권한을 부여하고 싶을 때

SELECT CONCAT("GRANT SELECT ON '<db_name>'.", table_name, " TO '<role_name>';")
FROM information_schema.TABLES
WHERE table_schema = "<db_name>" AND table_name <> "<table_name_if_you_want_to_skip>";

위와 같은 GRANT 쿼리 리스트를 만드는 쿼리를 실행하여, 출력된 쿼리문을 한 번에 실행한다.

그러면 원하는 대로 동작한다.

 

이런 식으로, 특정 column을 제외하고 접근 가능한 role을 생성할 때 등등 활용이 가능하다.

 

 

 

 

---

 

 

이처럼 role 기능은, 보안적 관점에서 db user를 훨씬 수월하게 관리할 수 있도록 돕는 기능인 것 같다.

요즘 유용이 사용 중이어서 정리 및 공유 차원에서 포스팅을 해보았다!