[MariaDB] 그룹(ROLE) 기반 권한 부여

DB 계정 별로 모든 권한을 부여하지 않고 차등 권한을 부여하기 위해

계정별로 그룹(role)을 생성, 그룹에 권한 부여 하는 방법 테스트

MariaDB [mysql]> CREATE ROLE developer;
Query OK, 0 rows affected (0.012 sec)

MariaDB [mysql]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| webservice         |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [mysql]> GRANT SELECT,INSERT,UPDATE,DELETE ON webservice.* TO developer;
Query OK, 0 rows affected (0.032 sec)

MariaDB [mysql]> SHOW GRANTS FOR developer;
+-----------------------------------------------------------------------+
| Grants for developer                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `developer`                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `webservice`.* TO `developer` |
+-----------------------------------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [mysql]>
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]>

 

기존 사용자에게 그룹 롤 부여

MariaDB [mysql]> show grants for 'ky.lee'@'172.30.2.27';
+---------------------------------------------------------------------------------+
| Grants for ky.lee@172.30.2.27                                                   |
+---------------------------------------------------------------------------------+
| GRANT `developer` TO `ky.lee`@`172.30.2.27`                                     |
| GRANT USAGE ON *.* TO `ky.lee`@`172.30.2.27` IDENTIFIED BY PASSWORD '*보안삭제' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `fb_ebmp`.* TO `ky.lee`@`172.30.2.27`   |
+---------------------------------------------------------------------------------+
3 rows in set (0.000 sec)

MariaDB [mysql]> revoke ALL ON webservice.* from 'ky.lee'@'172.30.2.27';
Query OK, 0 rows affected (0.017 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> select current_role;
+--------------+
| current_role |
+--------------+
| NULL         |
+--------------+
1 row in set (0.000 sec)

MariaDB [mysql]> set role developer;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> select current_role;
+--------------+
| current_role |
+--------------+
| developer    |
+--------------+
1 row in set (0.000 sec)

MariaDB [mysql]> grant developer to 'ky.lee'@'172.30.2.27';
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> SET DEFAULT ROLE 'developer' for 'ky.lee'@'172.30.2.27';
Query OK, 0 rows affected (0.019 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]> show grants for 'ky.lee'@'172.30.2.27';
+---------------------------------------------------------------------------------+
| Grants for ky.lee@172.30.2.27                                                   |
+---------------------------------------------------------------------------------+
| GRANT `developer` TO `ky.lee`@`172.30.2.27`                                     |
| GRANT USAGE ON *.* TO `ky.lee`@`172.30.2.27` IDENTIFIED BY PASSWORD '*보안삭제' |
+---------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [mysql]> REVOKE developer from 'ky.lee'@'172.30.2.27';
Query OK, 0 rows affected (0.029 sec)

MariaDB [mysql]> show grants for 'ky.lee'@'172.30.2.27';
+---------------------------------------------------------------------------------+
| Grants for ky.lee@172.30.2.27                                                   |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `ky.lee`@`172.30.2.27` IDENTIFIED BY PASSWORD '*보안삭제' |
+---------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mysql]>

 

함수 권한

MariaDB [mysql]> show grants for developer;
+-------------------------------------------------------------------------------------------+
| Grants for developer                                                                      |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `developer`                                                         |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW ON `webservice`.* TO `developer` |
+-------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [mysql]> revoke execute on webservice.* from developer;
Query OK, 0 rows affected (0.018 sec)

MariaDB [mysql]> show grants for developer;
+----------------------------------------------------------------------------------+
| Grants for developer                                                             |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `developer`                                                |
| GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON `webservice`.* TO `developer` |
+----------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [mysql]> show function status where db = 'webservice';
+------------+----------------------+----------+---------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db         | Name                 | Type     | Definer             | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------+----------------------+----------+---------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| webservice | AES128_DECRYPT       | FUNCTION | db_admin@10.10.50.% | 2021-04-23 10:55:16 | 2021-06-07 23:35:20 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| webservice | AES128_ENCRYPT       | FUNCTION | db_admin@10.10.50.% | 2021-04-23 10:55:08 | 2021-06-07 23:35:20 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| webservice | GET_CD_NM            | FUNCTION | db_admin@10.10.50.% | 2021-11-04 18:04:36 | 2021-11-04 18:04:36 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| webservice | GET_CHILD_CD         | FUNCTION | db_admin@%          | 2022-07-04 17:02:39 | 2022-07-04 17:02:39 | DEFINER       |         | utf8mb4              | utf8mb4_general_ci   | utf8_unicode_ci    |
| webservice | GET_PARENT_CD        | FUNCTION | db_admin@10.10.50.% | 2021-11-04 18:05:07 | 2021-11-04 18:05:07 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| webservice | GET_PARENT_CD_CONCAT | FUNCTION | db_admin@10.10.50.% | 2021-11-04 18:05:28 | 2021-11-04 18:05:28 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| webservice | GET_PARENT_CD_NM     | FUNCTION | db_admin@10.10.50.% | 2021-11-04 18:05:49 | 2021-11-04 18:05:49 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------------+----------------------+----------+---------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
7 rows in set (0.003 sec)

MariaDB [mysql]> 
MariaDB [mysql]> show grants for developer;
+-------------------------------------------------------------------------------------------+
| Grants for developer                                                                      |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `developer`                                                         |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW ON `webservice`.* TO `developer` |
+-------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [mysql]> revoke execute on webservice.* from developer;
Query OK, 0 rows affected (0.422 sec)

MariaDB [mysql]> grant execute on function webservice.AES128_ENCRYPT to developer;
Query OK, 0 rows affected (0.042 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]> show grants for developer;
+----------------------------------------------------------------------------------+
| Grants for developer                                                             |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `developer`                                                |
| GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON `webservice`.* TO `developer` |
| GRANT EXECUTE ON FUNCTION `webservice`.`aes128_encrypt` TO `developer`           |
+----------------------------------------------------------------------------------+
3 rows in set (0.000 sec)

MariaDB [mysql]>

 

이상 과거 작업 내용 갈무리 한것

 

끝.

 

 

2024-10-25 추가

실제로 서버에 적용해 보면 role 을 부여받은 계정의 권한을 확인해 보면 정상으로 표시되는데, 실제 접속을 해보면

[MySQL] Access denied for user '유저아이디'@'ip'

에러가 발생함

 

몇가지 방법을 시도해 보았으나 결국 role 에 권한을 부여하고 계정에 다시 부여하는 경우는 잘 적용되지 않았습니다.

결국 계정별로 일일히 권한을 부여하는 방식으로 해결 했으나... 권한 테이블이 많이 복잡하고 유저가 많은 경우 매우 불편 할 것으로 예상되어 조금더 찾아보고 업데이트 하겠습니다.