MySQL 8.0 - Role 기능 추가

개요

MySQL 8.0부터는 ANSI 에서 제공하는 “특정권한에 대한 집합” 을 나타내는 ROLE기능을 추가하였다.
- https://en.wikipedia.org/wiki/SQL:1999#Role-based_access_control

MySQL에서 ROLE은 마치 로그인을 할 수 없는 새로운 계정 (ROLE_NAME@%) 처럼 생성 되어지며, 해당 계정의 권한을 다른 계정에게 승계하는것처럼 ROLE의 부여 및 삭제가 이루어진다.

mysql> CREATE ROLE 'foolish';
Query OK, 0 rows affected (0.07 sec)

--^^ 'foolish'라는 ROLE을 만든다.


mysql> CREATE USER 'foolish'@'localhost';
Query OK, 0 rows affected (0.03 sec)

--^^ 'foolish'@'localhost' 계정은 잘 만들어진다.


mysql> CREATE USER 'foolish'@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foolish'@'%'

--^^ 'foolish'@'%' 계정의 경우, 생성실패 에러가 발생한다. 해당이유는 이미 해당 계정 (foolish@%)이 ROLE의 이름으로 존재하기 때문이다.


mysql> SELECT USER, HOST, ACCOUNT_LOCKED from mysql.user where user='foolish';
+---------+------+----------------+
| USER    | HOST | ACCOUNT_LOCKED |
+---------+------+----------------+
| foolish | %    | Y              |
+---------+------+----------------+
1 row in set (0.00 sec)

ROLE을 만들게 되면, 기본적으로 ‘ROLE_NAME’@’%’ 이라는 계정이 생성되는것에 유의해야 한다.

‘ROLE_NAME’@’%’ 의 account를 생성할 수 없음으로, ROLE 이름을 규칙성 있게 짖는것이 중요한다. (e.g. role_xyz)

사용예제

ROLE은 부여 후 활성화가 되어야 정상적으로 사용이 가능하다. ROLE에 대한 활성화는 해당 ROLE을 부여받은 자기자신 혹은 SUPER 권한을 가진 계정에서 가능하다.

ROLE 생성과 부여

-- ROLE을 생성한다.

mysql> CREATE ROLE 'DEVSEL';
Query OK, 0 rows affected (0.06 sec)


-- ROLE에 대해 권한을 부여한다.



mysql> GRANT SELECT, SHOW VIEW ON `%\_%`.* TO 'DEVSEL';
Query OK, 0 rows affected (0.07 sec)


-- ROLE을 특정계정에 부여한다.

mysql> CREATE USER 'some.developer'@'localhost' IDENTIFIED BY 'somePassword';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT 'DEVSEL' TO 'some.developer'@'localhost';
Query OK, 0 rows affected (0.02 sec)


-- 특정계정의 권한을 확인한다.

mysql> show grants for 'some.developer'@'localhost';
+----------------------------------------------------+
| Grants for some.developer@localhost                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `some.developer`@`localhost` |
| GRANT `DEVSEL`@`%` TO `some.developer`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.01 sec)




-- ROLE에 대한 권한을 정확히 확인하려면, using 구문을 사용하여 확인할 수 있다.

mysql> show grants for 'some.developer'@'localhost' using 'DEVSEL';
+---------------------------------------------------------------------+
| Grants for some.developer@localhost                                 |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `some.developer`@`localhost`                  |
| GRANT SELECT, SHOW VIEW ON `%\_%`.* TO `some.developer`@`localhost` |
| GRANT `DEVSEL`@`%` TO `some.developer`@`localhost`                  |
+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

활성화

/MySQL/binaries/8.0.13/bin/mysql -usome.developer -psomePassword --socket=/var/folders/7v/j50shy2154jcrmtvg71x262r0000gn/T/mysql_sandbox8013.sock


-- 특정계정으로 접속하여, 나에게 부여된 권한을 확인한다.
/MySQL/binaries/8.0.13/bin/mysql --user some.developer -psomePassword --socket=/tmp/mysql_sandbox21621.sock;

mysql> show grants;
+----------------------------------------------------+
| Grants for some.developer@localhost                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `some.developer`@`localhost` |
| GRANT `DEVSEL`@`%` TO `some.developer`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.00 sec)


-- DEVSEL 권한(계정)은 부여되었지만, 아직 권한이 없는것이 확인된다.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)



-- 부여받은 계정에 대해 아래의 명령어로 활성화시킨다. 활성화 시키는 방법은 두가지 방법이 존재한다.

---- SET DEFAULT ROLE 명령어를 이용해 super권한을 가진 계정이 다른 계정의 ROLE을 활성화 시키기거나 자신이 가지고 있는 ROLE을 활성화 시킬 수 있다.

---- 두번째 방법으로는 SET ROLE 명령어를 통해, 현재 세션에 대해 자신이 가지고 있는 ROLE을 활성화 시킬 수 있다. 이는 mandatory_roles 과 activate_all_roles_on_login 의 옵션에 따라 자동으로 부여될 수 있다.

mysql> SET DEFAULT ROLE DEVSEL to 'some.developer'@'localhost';
Query OK, 0 rows affected (0.05 sec)

mysql> show grants;
+----------------------------------------------------+
| Grants for some.developer@localhost                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `some.developer`@`localhost` |
| GRANT `DEVSEL`@`%` TO `some.developer`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.00 sec)



-- 다시 접속하여 권한을 확인한다.

mysql> connect;
Connection id:    15
Current database: *** NONE ***

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for some.developer@localhost                                 |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `some.developer`@`localhost`                  |
| GRANT SELECT, SHOW VIEW ON `%\_%`.* TO `some.developer`@`localhost` |
| GRANT `DEVSEL`@`%` TO `some.developer`@`localhost`                  |
+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test_xyz           |
+--------------------+
3 rows in set (0.00 sec)


-- ^^ 새로운 "test_xyz" 와 "performance_schema" database에 대한 권한이 생긴것이 확인된다.

Default Role 명령어에 의해 Activation된 ROLE의 확인

mysql> select * from mysql.default_roles;
Empty set (0.00 sec)

mysql> SET DEFAULT ROLE DEVDBA to 'min.cho'@'localhost';
Query OK, 0 rows affected (0.10 sec)

mysql> select * from mysql.default_roles;
+-----------+---------+-------------------+-------------------+
| HOST      | USER    | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+---------+-------------------+-------------------+
| localhost | min.cho | %                 | DEVDBA            |
+-----------+---------+-------------------+-------------------+
1 row in set (0.00 sec)
...


mysql> select * from mysql.default_roles;
+-----------+----------------+-------------------+-------------------+
| HOST      | USER           | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+----------------+-------------------+-------------------+
| localhost | some.developer | %                 | DEVSEL            |
+-----------+----------------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> set default role NONE to `some.developer`@`localhost`;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.default_roles;
Empty set (0.00 sec)

ROLE 의 상속과 제거

-- DML가능한 ROLE 을 DEVDML 이라는 이름으로 만든다.


mysql> CREATE ROLE 'DEVDML';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT INSERT, UPDATE, DELETE  ON `%\_%`.* TO 'DEVDML';
Query OK, 0 rows affected (0.05 sec)

mysql> show grants for DEVDML;
+----------------------------------------------------------+
| Grants for DEVDML@%                                      |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `DEVDML`@`%`                       |
| GRANT INSERT, UPDATE, DELETE ON `%\_%`.* TO `DEVDML`@`%` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)



-- DEVDML 권한에 DEVSEL 권한을 상속한다.

mysql> GRANT 'DEVSEL' to 'DEVDML';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for DEVDML;
+----------------------------------------------------------+
| Grants for DEVDML@%                                      |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `DEVDML`@`%`                       |
| GRANT INSERT, UPDATE, DELETE ON `%\_%`.* TO `DEVDML`@`%` |
| GRANT `DEVSEL`@`%` TO `DEVDML`@`%`                       |
+----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for DEVDML using 'DEVSEL';
+-----------------------------------------------------------------------------+
| Grants for DEVDML@%                                                         |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `DEVDML`@`%`                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON `%\_%`.* TO `DEVDML`@`%` |
| GRANT `DEVSEL`@`%` TO `DEVDML`@`%`                                          |
+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)




-- REVOKE를 통해 DEVSEL 권한을 제거한다.


mysql> REVOKE 'DEVSEL' from 'DEVDML';
Query OK, 0 rows affected (0.09 sec)

mysql>  show grants for 'DEVDML';
+----------------------------------------------------------+
| Grants for DEVDML@%                                      |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `DEVDML`@`%`                       |
| GRANT INSERT, UPDATE, DELETE ON `%\_%`.* TO `DEVDML`@`%` |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

주의사항

ROLE에 대한 요구사항이 발생하는 경우, 명확하게 ROLE에 대한 이름들을 지정하여 사용하여야 한다. 사용자의 계정과 절대 비슷해서는 안되며, grant 구문으로 ROLE 이 부여되었는지를 한눈에 알기 쉽도록 구성하기 위함이다. 예를들면 아래와 같다. “

ROLE_

” prefix를 붙여 가독성을 높인다.
- ROLE_SELECT
- ROLE_CRUD
- ROLE_ADMIN
- ROLE_DBA

MySQL 8.0 - Role 기능 추가

Categories

Tags

개요

사용예제

ROLE 생성과 부여

활성화

Default Role 명령어에 의해 Activation된 ROLE의 확인

ROLE 의 상속과 제거

주의사항