본문 바로가기
MySQL

MySQL 정적 및 동적 권한 2

by 타마마임팩트_쫀 2021. 7. 30.

전에 DBA 계정에서 SUPER 권한을 제거하는 방법을 해보았습니다.

그러나 실생활에서는 반대로 계정이 올바르게 작동하는 데 필요한 것만 추가해야 합니다.

각 사용자에게 권한을 하나씩 추가하는 것은 그들이 가질 수 있는 상호 작용 수준을 고려할 때 문제가 있고 실수하기 쉽습니다. 

대신 ROLES를 사용하여 훨씬 더 쉬운 방법으로 올바른 권한을 그룹화, 할당 및 취소할 수 있습니다.

ROLES를 올바르게 사용하려면

첫 번째 단계는 ROLES를 식별하는 것입니다. 기본 원칙을 유지하고 단순하게 만들어야 합니다. 따라서 ROLES이 너무 많거나 교차 기능 권한이 너무 많은 ROLE을 사용하지 않도록 해야 합니다.

  • DBA(모든 것을 할 수 있음)
  • MaintenanceAdmin(서버에서 일부 작업만 수행할 수 있음)
  • UserAdmin(사용자가 권한 부여 등을 할당하도록 생성할 수 있음)
  • MonitorUser(모든 프로세스 보기 및 performance_schema에서 읽기)
  • DBManager(스키마/테이블/트리거/보기/루틴 등 추가/삭제/수정 가능)
  • DBDesigner (스키마/테이블에 의한 명확한 식별로 대부분 특정 객체 수정 가능)
  • ReplicationAdmin(GR도 복제 시작/중지 추가/변경/제거 가능)
  • BackupAdmin(백업 가능, 복원 불가)

8개의 관리 역할로 나누었습니다.

롤을 생성해 보겠습니다.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16		 CREATE ROLE 'DBA', 'MaintenanceAdmin', 'UserAdmin', 'MonitorUser', 'DBManager', 'DBDesigner', 'ReplicationAdmin', 'BackupAdmin'


(root@localhost) [mysql]>Select user,host from mysql.user where account_locked ='Y' and password_expired='Y' order by 1;
+------------------+------+
| user             | host |
+------------------+------+
| BackupAdmin      | %    |
| DBA              | %    |
| DBDesigner       | %    |
| DBManager        | %    |
| MaintenanceAdmin | %    |
| MonitorUser      | %    |
| ReplicationAdmin | %    |
| UserAdmin        | %    |
+------------------+------+
8 rows in set (0.00 sec)

롤을 하나씩 확인하고 어떤 권한을 할당해야 하는지 알아보겠습니다.

MySQL

1
2
3		 (secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost`

 

DBA

GRANT ALL 은 MySQL 8.0.x에서 더 이상 사용되지 않는 SUPER도 할당됩니다. 

필요한 것만 추가합니다.

MySQL

1
2
3		 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `DBA`@`%` WITH GRANT OPTION;
  
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `DBA`@`%` WITH GRANT OPTION;

GRANT ALL과 정확히 동일해야 하지만 SUPER는 제외됩니다. 

테스트 사용자에게 ROLE을 할당하려면:

MySQL

1 GRANT `DBA`@`%` TO `secure_test`@`localhost`

이제 사용자는 다음을 수행합니다.

MySQL

1
2
3
4
5		 (secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT USAGE ON *.* TO `secure_test`@`localhost`
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT `DBA`@`%` TO `secure_test`@`localhost`

이제 DBA가 권한 부여로 표시되지만 활성 상태가 아닙니다.

 

MySQL

1
2		 (secure_test@localhost) [(none)]>show grants for DBA@'%'\G
ERROR 1142 (42000): SELECT command denied to user 'secure_test'@'localhost' for table 'user'

롤을 활성화하려면 명시적으로 수행해야 합니다.

 

MySQL

1  SET DEFAULT ROLE DBA TO  secure_test@'localhost';

그리고 사용자가 다시 연결하도록 합니다.

롤이 활성화되면 다음을 사용할 수도 있습니다.

 

MySQL

1 show grants for current_user()\G

현재 특정 사용자에 대해 어떤 권한이 활성화되어 있는지 확인합니다.

또한 mysql.default_roles 테이블을 쿼리하는 사용자에 대해 활성 롤을 제어할 수 있습니다. 

 

활성된 롤을 제거하려면:

MySQL

1 SET DEFAULT ROLE NONE TO  secure_test@'localhost';

어쨌든, 이제 우리는 모든 DBA가 사용할 수 있는 DBA 역할을 갖게 되었으며, 무언가를 변경해야 하는 경우 모든 단일 사용자가 아니라 ROLE에서 수행할 수 있습니다. 

 

MaintenanceAdmin

MySQL

1
2
3		 GRANT EVENT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, RELOAD, SHUTDOWN ON *.* TO `MaintenanceAdmin`@`%`;
GRANT BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN, REPLICATION_SLAVE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SYSTEM_VARIABLES_ADMIN ON *.* TO `MaintenanceAdmin`@`%`;
GRANT `MaintenanceAdmin`@`%` TO `secure_test`@`localhost` ;

 

UserAdmin 

MySQL

1
2
3		 GRANT CREATE USER, GRANT OPTION, RELOAD, SHOW DATABASES ON *.* TO `UserAdmin`@`%`;
GRANT ROLE_ADMIN  ON *.* TO `UserAdmin`@`%`;
GRANT `UserAdmin`@`%` TO `secure_test`@`localhost` ;

 

MonitorUser 

MySQL

1
2
3		 GRANT PROCESS, REPLICATION CLIENT ON *.* TO `MonitorUser`@`%`;
GRANT SELECT ON performance_schema.* TO `MonitorUser`@`%`;
GRANT `MonitorUser`@`%` TO `secure_test`@`localhost` ;

 

DBManager

MySQL

1
2
3		 GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, INDEX, INSERT, LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE  ON *.* TO `DBManager`@`%`;
GRANT SET_USER_ID, SHOW_ROUTINE ON *.* TO `DBManager`@`%`;
GRANT `DBManager`@`%` TO `secure_test`@`localhost` ;

 

DBDesigner

MySQL

1
2		 GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE VIEW, INDEX, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO `DBDesigner`@`%`;
GRANT `DBDesigner`@`%` TO `secure_test`@`localhost` ;

 

ReplicationAdmin

MySQL

1
2
3
4
5		 GRANT REPLICATION CLIENT ON *.* TO `ReplicationAdmin`@`%`;
GRANT REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, SERVICE_CONNECTION_ADMIN ON *.* TO `ReplicationAdmin`@`%`;
GRANT SELECT on performance_schema.* TO `ReplicationAdmin`@`%`;
GRANT SELECT on mysql.* TO `ReplicationAdmin`@`%`;
GRANT `ReplicationAdmin`@`%` TO `secure_test`@`localhost` ;

 

BackupAdmin 

MySQL

1
2
3		 GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *.* TO `BackupAdmin`@`%`;
GRANT BACKUP_ADMIN ON *.* TO `BackupAdmin`@`%`;
GRANT `BackupAdmin`@`%` TO `secure_test`@`localhost` ;

 

모든 롤이 지정되면 테스트할 수 있습니다. 

예를 들어 ReplicationAdmin 에서 바이너리 로그를 확인하고 replication 중지/시작하는 것을 확인할 수 있습니다 .

 

MySQL

1
2
3
4
5		 (secure_test@localhost) [(none)]>show binary logs;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation


(secure_test@localhost) [(none)]>stop group_replication;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operation

롤은 아직 활성 상태가 아닙니다. 이제 사용자의 롤을 활성화하겠습니다.

MySQL

1 SET DEFAULT ROLE ReplicationAdmin  TO  secure_test@'localhost';

 

다시 접속 합니다.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32		 (secure_test@localhost) [(none)]>show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 | 113802321 | No        |
| binlog.000012 |     19278 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)


(secure_test@localhost) [(none)]>stop group_replication;
Query OK, 0 rows affected (5.25 sec)


DC2-2(secure_test@localhost) [(none)]>select * from  performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5         |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)


(secure_test@localhost) [(none)]>start group_replication;
Query OK, 0 rows affected (3.70 sec)


(secure_test@localhost) [(none)]>select * from  performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 79ede65d-9797-11eb-9963-08002734ed50 | gr4         |        3306 | ONLINE       | PRIMARY     | 8.0.23         |
| group_replication_applier | 7e214802-9797-11eb-a0cf-08002734ed50 | gr6         |        3306 | ONLINE       | SECONDARY   | 8.0.23         |
| group_replication_applier | 7fddf04f-9797-11eb-a193-08002734ed50 | gr5         |        3306 | ONLINE       | SECONDARY   | 8.0.23         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

 

활성화된 권한은 다음과 같습니다.

MySQL

1
2
3
4
5
6
7
8
9
10
11
12		 DC2-2(secure_test@localhost) [(none)]>show grants for current_user()\G
*************************** 1. row ***************************
Grants for secure_test@localhost: GRANT REPLICATION CLIENT ON *.* TO `secure_test`@`localhost`
*************************** 2. row ***************************
Grants for secure_test@localhost: GRANT GROUP_REPLICATION_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,SERVICE_CONNECTION_ADMIN ON *.* TO `secure_test`@`localhost`
*************************** 3. row ***************************
Grants for secure_test@localhost: GRANT SELECT ON `mysql`.* TO `secure_test`@`localhost`
*************************** 4. row ***************************
Grants for secure_test@localhost: GRANT SELECT ON `performance_schema`.* TO `secure_test`@`localhost`
*************************** 5. row ***************************
Grants for secure_test@localhost: GRANT `BackupAdmin`@`%`,`DBA`@`%`,`DBDesigner`@`%`,`DBManager`@`%`,`MaintenanceAdmin`@`%`,`MonitorUser`@`%`,`ReplicationAdmin`@`%`,`UserAdmin`@`%` TO `secure_test`@`localhost`
5 rows in set (0.00 sec)

 

결론

ROLES를 사용하면 한 곳에서 필요한 권한을 수정할 수 있으며 동시에 구성 요소 또는 플러그인 사용으로 인한 동적 권한의 확산 가능성을 제어할 수 있어 여러 권한을 갖는 복잡성을 크게 줄일 수 있습니다. .

롤은 일반적으로 가장 일반적인 데이터베이스에서 사용되며 MySQL은 이를 상당히 늦게 구현했습니다. 그러나 롤을 사용하는 것은 소규모 플랫폼에서 중형/대형 플랫폼으로 이동할 때 사용자 권한을 처리하는 자연스러운 방법입니다. 

 

'MySQL' 카테고리의 다른 글

innodb_open_files과 open_files_limit  (0) 2021.08.04
pt-query-digest  (0) 2021.08.03
MySQL 정적 및 동적 권한  (0) 2021.07.29
MySQL 8.0 strage engine  (0) 2021.07.21
binlog2sql: Binlog를 이용한 Point In Time Recovery  (0) 2020.08.04

관련글

티스토리툴바