information technology: Partial Revokes 시스템 변수에 대하여

MySQL 8.0.16에서 Partial Revokes 시스템 변수가 추가되었다.

통상적으로 모든 권한을 부여하지만, 일부 권한만 박탈할 수 기능으로 부분적인 제한 설정이 가능해졌다.
이 변수를 굳이 사용하지 않아도 동일한 권한을 부여할 수 있지만, 부여하는 스키마의 대상이 많은 경우는 해당 변수를 사용하는 게 편리할 지도 모른다.

partial_revokes 설정
기본 값이 OFF로 설정되어 있으므로, 기능을 사용하기 위해서는 ON으로 해주어야 한다.

mysql> SET GLOBAL partial_revokes = 1;
Query OK, 0 rows affected (0.04 sec)

모든 데이터베이스의 SELECT권한을 갖는 유저를 작성한 후 mysql 스키마에 대한 권한을 박탈해보자.

mysql> CREATE USER test_user@localhost IDENTIFIED BY '*****';
Query OK, 0 rows affected (0.05 sec)


mysql>GRANT SELECT ON *.* TO test_user@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>REVOKE SELECT ON mysql.* FROM test_user@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>SHOW GRANTS FOR test_user@localhost;
+-----------------------------------------------------------+
| Grants for test_user@localhost                          |
+-----------------------------------------------------------+
| GRANT SELECT ON *.* TO `test_user `@`localhost`          |
| REVOKE SELECT ON `mysql`.* FROM `test_user `@`localhost` |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

실제로 작성한 유저로 mysql 스키마에 접근해보면 에러가 발생하는 것을 확인할 수 있다.

mysql> SELECT current_user();
+-----------------------+
| current_user()        |
+-----------------------+
| test_user@localhost |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'test_user'@'localhost' for table 'user'

partial_revokes는 "일부를 제외한 모든 권한을 부여한다" 라고 이해하면 될 거 같다.
실제로 partial_revokes를 이용한 revoke 정보는 mysql.user.user_attributes 컬럼에서 확인할 수 있다.

mysql> SELECT concat(user,'@', host), user_attributes FROM mysql.user WHERE user = 'test_user' and host = 'localhost';
+------------------------+---------------------------------------------------------------------+
| concat(user,'@', host) | user_attributes                                                     |
+------------------------+---------------------------------------------------------------------+
| test_user@localhost  | {"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]} |
+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

partial_revokes 주의점
partial_revokes를 이용하고 있는 유저가 있는 경우, 설정을 OFF로 변경하는 건 불가능하다.

mysql> SET GLOBAL partial_revokes = 0;
ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON.

OFF로 변경하고 싶은 경우는, partial_revokes로 박탈된 권한 삭제 또는 유저 삭제로 partial_revokes를 이용하지 않는 상태로 되어야 한다.
partial_revokes가 이용되고 있는 유저의 유무 확인은 mysql.user.user_attributes 컬럼의 Restrictions 속성으로 가능하다.

partial_revokes는 스키마를 지정해야 하고 일부 테이블이나 컬럼, % 등의 와일드 카드를 이용해서 스키마를 제외하는 건 불가능하다.
또한 CREATE TABLE이나 FILE과 같은 글로벌 레벨의 권한에 대해서도 partial_revokes를 이용할 수 없다.

replication구성에서 master가 partial_revokes ON, slave가 partial_revokes OFF인 경우 에러가 발생하기 때문에 각 인스턴스의 설정을 맞춰주어야 한다.

partial_revokes: master ON, slave OFF 상황에서의 에러

mysql> SHOW SLAVE STATUS\G
〈skip〉
Last_SQL_Errno: 1141
Last_SQL_Error: Error 'There is no such grant defined for user 'test_user' on host 'localhost'' on query. Default database: '
'. Query: 'REVOKE SELECT ON mysql.* FROM test_user@localhost'
  Replicate_Ignore_Server_Ids: