데이터베이스 관리 세계에서 사용자 액세스 제어는 데이터 무결성과 보안을 유지하는 데 매우 중요합니다. 이 블로그 게시물은 프로세스, 잠재적인 함정, 디버깅 단계를 포함하여 MySQL 사용자 권한을 설정하는 실제 시나리오를 안내합니다.

시나리오

당신이 여러 데이터베이스가 있는 회사의 데이터베이스 관리자라고 가정해 보십시오.

• 원래 데이터베이스: 제품, 고객, 주문, 분석
• 데이터베이스 복사: products_copy,customers_copy,orders_copy,analytics_copy

귀하의 작업은 다음 요구 사항에 따라 'analyst'라는 사용자에 대한 권한을 설정하는 것입니다.

1. 분석가는 원본 데이터베이스의 데이터베이스와 테이블을 볼 수 있어야 합니다.
2. 분석가는 원본 데이터베이스를 편집할 수 없어야 합니다.
3. 분석가는 새 데이터베이스를 생성할 수 없어야 합니다.
4. 분석가는 '_copy' 데이터베이스에 대한 전체 액세스 권한(테이블 보기, 편집, 삭제, 생성)을 가지고 있어야 합니다.

MySQL의 GRANT 및 REVOKE 문을 사용하여 이를 달성할 수 있는 방법을 살펴보겠습니다.

1단계: 초기 설정

먼저 관리 계정으로 MySQL 서버에 연결해야 합니다.

mysql -h hostname -P port -u admin -p

'호스트 이름', '포트' 및 '관리자'를 실제 서버 세부 정보 및 관리자 사용자 이름으로 바꾸세요.

2단계: 사용자 생성

사용자가 아직 존재하지 않는 경우 이를 생성해야 합니다.

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

'비밀번호'를 강력하고 안전한 비밀번호로 바꾸세요.

3단계: 필요한 권한 부여

이제 필요한 권한을 부여해 보겠습니다.

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

4단계: 권한 확인

권한을 설정한 후에는 이를 확인하는 것이 중요합니다.

SHOW GRANTS FOR 'analyst'@'%';

디버깅 및 문제 해결

문제 1: 권한이 너무 많음

우리 시나리오에서는 처음에 '분석가'에게 권한이 너무 많은 문제가 발생했습니다.

mysql> SHOW GRANTS FOR 'analyst'@'%';
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, 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 ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

해결책:

이 문제를 해결하기 위해 모든 권한을 취소한 다음 필요한 권한만 부여했습니다.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

문제 2: 복사본 데이터베이스에 대한 사용 권한 누락

과도한 권한을 수정한 후 복사 데이터베이스에 대한 권한이 누락된 것을 확인했습니다.

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
 ----------------------------------------------------- 

해결책:

사본 데이터베이스에 대해 누락된 권한을 추가했습니다.

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

최종 결과

이러한 모든 변경 사항과 수정 사항을 적용한 후 최종 승인은 다음과 같습니다.

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
 ----------------------------------------------------- 

결론

적절한 MySQL 사용자 권한을 설정하는 것은 까다로울 수 있지만 데이터베이스 관리의 중요한 측면입니다. GRANT 및 REVOKE 문을 주의 깊게 사용하고 항상 결과를 확인하면 사용자를 위한 안전하고 기능적인 환경을 만들 수 있습니다.

다음 핵심 사항을 기억하세요.

1. 항상 최소 권한의 원칙으로 시작하세요.
2. 변경 후 SHOW GRANTS를 사용하여 권한을 확인하세요.
3. 전역 권한에 주의하세요(ON .).
4. 변경한 후에는 권한을 플러시하는 것을 잊지 마세요.

이 지침과 이 게시물에 설명된 단계를 따르면 MySQL 사용자 권한을 효과적으로 관리할 수 있는 준비를 갖추게 됩니다.