권한 관리 시스템 개발 시 사용자·역할·권한 매핑 데이터의 중복 등록을 막는 검증 규칙을 정리했습니다. DB 유니크 인덱스 설계, 서버 사전 검증, 동시성 제어까지 3계층 방어 전략과 구현 방법을 단계별로 설명합니다.
- 결론 먼저: 중복 차단은 DB 제약·서버 검증·동시성 제어 3계층으로 나눠야 한다
- 1단계 – 유니크 인덱스 설계: 컬럼 조합과 NULL 처리
결론 먼저: 중복 차단은 DB 제약·서버 검증·동시성 제어 3계층으로 나눠야 한다
같은 사용자에게 같은 역할이 두 줄 들어앉는 순간이 실제 사고의 시작입니다. 관리자 화면에서 버튼을 두 번 누르거나, 두 관리자가 같은 계정을 동시에 저장하거나, 배치 작업을 재실행하는 것 — 어느 쪽이든 매핑 테이블에 중복 행이 쌓이면 권한 계산이 어긋나고, 되돌리는 데 몇 배의 시간이 듭니다. 핵심은 중복 차단을 한 곳에만 두면 반드시 샌다는 점입니다. 프론트엔드에서 버튼을 막아도 API를 직접 호출하면 무력화되고, 서버에서 검증해도 저장 직전에 동시 요청이 겹치면 빠져나갑니다. 그래서 역할을 나눠야 합니다.
1단계 – 유니크 인덱스 설계: 컬럼 조합과 NULL 처리
학회 운영 플랫폼의 관리자 화면에서 스태프에게 '현장 접수' 역할을 줬는데, 다른 담당자가 실수로 같은 역할을 한 번 더 부여했다고 상상해 보자. 화면에는 같은 권한 버튼이 두 개 나타나고, QR 체크인 담당 설정이 어느 매핑을 따라야 할지 애매해진다. 이런 중복은 애플리케이션 코드의 "이미 있으면 넣지 않기" 로직으로는 막기 어렵다. 두 요청이 동시에 들어오면 둘 다 '없다'고 확인한 뒤 둘 다 저장하기 때문이다. 최후의 방어선은 결국 데이터베이스의 유니크 인덱스다.
유니크 키에는 '정체성 컬럼'만 넣는다
판단 기준은 하나로 충분하다. 그 컬럼이 바뀌어도 여전히 '같은 매핑'이라면 유니크 키에 넣지 않고, 바뀌는 순간 '다른 매핑'이 되는 컬럼만 포함한다.
user_role테이블 → (user_id, role_id) 조합: "이 사용자의 이 역할"은 세상에 하나면 충분하다role_permission테이블 → (role_id, permission_id) 조합: "이 역할의 이 권한"도 마찬가지다
expires_at을 넣는 순간 생기는 구멍
2단계 – 서버 사전 검증과 DB 제약의 역할 분담, 동시성 처리
사전 조회 검증이 있어도 중복은 생깁니다. 두 명의 관리자가 같은 아이디를 거의 동시에 등록 버튼을 누르면, 둘 다 "중복 아님" 조회를 통과하고 각자 INSERT를 낄 수 있거든요. 그래서 이 단계의 핵심은 "검증은 서버가, 최후 방어는 DB가" 하는 역할 분담입니다.
- 등록 요청이 들어오면 먼저 서버에서
SELECT로 중복 여부 확인 - 중복이 아니면 트랜잭션 안에서
INSERT실행 - 그래도
DuplicateKeyException이 터지면(동시 등록으로 유니크 인덱스에 걸린 경우) "이미 등록된 값입니다" 같은 사용자 친화적 메시지로 변환해서 응답
여기서 자주 하는 실수가 예외를 그대로 500 에러로 흘려보내는 겁니다. DB 제약 위반은 "시스템 오류"가 아니라 설계된 방어선 작동이므로, 반드시 비즈니스 예외로 잡아 안내 문구로 바꿔주세요. 반대로 try-catch로 삼켜버리고 성공처럼 응답하는 것도 금물입니다.
동시 등록이 잦은 화면이라면 락 전략도 정해야 합니다.
3단계 – 엑셀 일괄 등록·자동 부여 병합·사후 점검 쿼리
엑셀 일괄 등록은 중복 데이터가 가장 많이 쌓이는 지점입니다. 화면에서 한 명씩 넣을 때는 눈에 보이지만, 수백 줄짜리 파일이 한 번에 들어오면 파일 안에서 겹치는 것, 이미 디비에 있는 것, 권한 규칙끼리 겹치는 것이 섞여버립니다. 이 단계에서는 그 세 겹을 한 번에 걸러내는 흐름을 정리합니다.
엑셀 업로드, 어떻게 검증할까
핵심은 행 단위 반복 조회가 아니라 집합 비교입니다. 엑셀의 각 행을 select 문으로 하나씩 확인하면 행 수만큼 쿼리가 나가고, 그 사이에 다른 관리자가 같은 계정을 등록하면 그대로 중복이 됩니다.
대신 이 순서로 묶어 처리하세요.
- 엑셀을 임시 테이블에 전체 적재
- 임시 테이블 내부 중복 검출 (
GROUP BY 사번 HAVING COUNT(*) > 1) - 기존 테이블과 조인해 이미 존재하는 계정 추출
- 신규 유효 행만 bulk insert, 실패 목록은 파일로 반환
반환되는 실패 목록에 "몇 번째 행, 어떤 사유(파일 내 중복/기존 중복/형식 오류)"까지 담으면, 담당자가 엑셀을 고쳐 다시 올리는 재작업이 확 줄어듭니다. 관리자 화면에서 이 사유 메시지 설계까지 해두는 것이 실무적인 완성점입니다.
에러 메시지 UX, 테스트 작성, 그리고 도입 판단 체크리스트
기술적으로 유니크 인덱스와 검증 로직을 다 갖춰도, 현장에서 문제를 만드는 건 대부분 에러 메시지와 테스트입니다. 관리자가 벌크 등록을 눌렀는데 "오류가 발생했습니다" 한 줄만 뜨면, 몇 백 명 데이터 중 어디가 걸린 건지 찾느라 한 시간이 날아갑니다.
좋은 에러 메시지의 기준은 하나입니다. 관리자가 수정 동작을 바로 할 수 있는가.
- 행 번호를 함께 안내하기: "12번째 행, 홍길동(이메일 example@..)이 기존 등록과 중복"
- 중복 사유 명시: 사번 중복인지, 이메일 중복인지, 내부 중복(같은 파일에 두 번)인지 구분
-
- 실패 행만 CSV로 재다운로드 받게 하면 관리자는 엑셀에서 수정 후 재업로드하면 끝
에러 코드 자체(유니크 제약 위반)를 그대로 노출하는 건 최악입니다. 관리자는 DB 제약이 아니라 "내가 무엇을 고칠지"가 필요합니다.
단위 테스트와 통합 테스트가 검증하는 지점이 다릅니다.
실행 전 점검표
| 점검 | 기준 |
|---|---|
| 결론 먼저: 중복 차단은 DB 제약·서버 검증·동시성 제어 3계층으로 나눠야 한다 | 중복 데이터는 한 곳에서만 막으면 새는 구조다. DB 유니크 인덱스가 최후 방어선, 서버 단 사전 검증이 사용자 경험 담당, 트랜잭션·락이 동시 |
| 1단계 – 유니크 인덱스 설계: 컬럼 조합과 NULL 처리 | user_id + role_id, role_id + permission_id 조합의 유니크 인덱스 설계 기준을 다룬다. NULL은 유니크 제약에 |
| 2단계 – 서버 사전 검증과 DB 제약의 역할 분담, 동시성 처리 | 서버 단 사전 조회 검증 후 INSERT하고 DB 중복 예외(DuplicateKeyException 등)를 잡아 사용자 친화적 메시지로 변환하는 |
| 3단계 – 엑셀 일괄 등록·자동 부여 병합·사후 점검 쿼리 | 엑셀 업로드 시 파일 내부 중복과 기존 데이터 중복을 한 번에 걸러내는 벌크 검증 로직(집합 비교), 부서/직급 자동 권한과 수동 부여가 겹칠 |