📋 IdP Backend System - 과제 제출 가이드

Simple-ID: 토스(Toss)와 같은 간편인증(IdP) 백엔드 시스템
기술 스택: Django 5.2.7 + MySQL 8.0 + Bootstrap 5.3

📦 제출물 목록 (총 6가지)

#	제출물	파일명	설명
1	ERD(EER) 다이어그램	`erd_diagram.png`	MySQL Workbench로 생성한 ER 다이어그램
2	백업(SQL) 파일	`idp_database_backup.sql`	DDL, 뷰, 인덱스, 제약조건, 트리거, 프로시저, 원본데이터 포함
3	테스트 시나리오 SQL	`test_scenarios.sql`	CRUD, JOIN, 동시성 테스트 포함
4	보안/개인정보 설계서	문서	RBAC 표, 감사 로그 테이블 샘플 포함
5	성능 보고서	문서	튜닝 전후 EXPLAIN/시간 비교
6	데모 체크리스트 + 화면 캡처	문서/이미지	평가 기준 항목별 동작 확인

🔧 사전 준비

MySQL 접속 정보

# MySQL 접속 (모든 명령어에서 사용)
mysql -u idp_user -p'IdP_Secure_2025!' idp_database

Django 서버 시작

cd /home/z3rotig4r/IdP_Backend_System
python3 manage.py runserver

📁 제출물 1: ERD(EER) 다이어그램

생성 방법 (MySQL Workbench)

# 1. MySQL Workbench 설치
sudo apt install mysql-workbench

# 2. 실행 후: Database → Reverse Engineer
# 3. 연결 정보 입력:
#    Host: localhost, Port: 3306
#    Username: idp_user, Password: IdP_Secure_2025!
#    Schema: idp_database

# 4. ERD 자동 생성됨 → File → Export → PNG/PDF

CLI로 ERD 정보 추출 (대안)

# 테이블/컬럼 정보 추출
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'idp_database'
ORDER BY TABLE_NAME, ORDINAL_POSITION;" > erd_columns.txt

# 외래키 관계 추출
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'idp_database' AND REFERENCED_TABLE_NAME IS NOT NULL;" > erd_relations.txt

📸 캡처 대상

ERD 전체 다이어그램 (테이블 간 관계 표시)
주요 테이블: accounts_user, auth_transactions_authtransaction, audit_logs_auditlog

📁 제출물 2: 백업(SQL) 파일

하나의 SQL 파일에 DDL, 뷰, 인덱스, 제약조건, 트리거, 프로시저, 원본데이터를 모두 포함

2-0. 백업 전 사전 적용 (⚠️ 필수!)

중요: mysqldump 전에 모든 SQL 요소가 DB에 적용되어 있어야 합니다!

현재 적용 상태 확인

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT '뷰' AS type, COUNT(*) AS cnt FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'idp_database'
UNION SELECT '트리거', COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'idp_database'
UNION SELECT '프로시저', COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'idp_database' AND ROUTINE_TYPE = 'PROCEDURE';
"

예상 결과 (모두 적용된 상태):

+------------+-----+
| type       | cnt |
+------------+-----+
| 뷰         |   3 |
| 트리거     |   3 |
| 프로시저   |   5 |
+------------+-----+

누락된 경우 적용하기

1) 뷰 생성 (3개)

-- MySQL 접속 후 실행
CREATE OR REPLACE VIEW v_user_masked AS
SELECT id, username,
    CONCAT(LEFT(email, 3), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked,
    CONCAT(LEFT(phone_number, 3), '****', RIGHT(phone_number, 4)) AS phone_masked,
    is_active, is_staff, is_superuser, date_joined, last_login,
    '***ENCRYPTED***' AS ci_status, '***ENCRYPTED***' AS di_status
FROM accounts_user;

CREATE OR REPLACE VIEW v_daily_auth_stats AS
SELECT 
    DATE(created_at) AS stat_date,
    COUNT(*) AS total_requests,
    SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_count,
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_count,
    SUM(CASE WHEN status = 'EXPIRED' THEN 1 ELSE 0 END) AS expired_count,
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count
FROM auth_transactions_authtransaction
GROUP BY DATE(created_at);

CREATE OR REPLACE VIEW v_user_role_permissions AS
SELECT 
    u.id AS user_id, u.username, u.is_superuser,
    r.role_name, r.permissions,
    ura.assigned_at,
    assigner.username AS assigned_by
FROM accounts_user u
LEFT JOIN accounts_userroleassignment ura ON u.id = ura.user_id
LEFT JOIN accounts_userrole r ON ura.role_id = r.id
LEFT JOIN accounts_user assigner ON ura.assigned_by_id = assigner.id;

2) 트리거 생성 (3개)

DELIMITER //

CREATE TRIGGER trg_auth_transaction_status_change
AFTER UPDATE ON auth_transactions_authtransaction
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO audit_logs_auditlog (
            user_id, action, details, ip_address, user_agent,
            request_path, request_method, status_code, timestamp
        ) VALUES (
            NEW.user_id, CONCAT('AUTH_STATUS_', NEW.status),
            JSON_OBJECT('transaction_id', NEW.transaction_id, 'old_status', OLD.status, 'new_status', NEW.status),
            '127.0.0.1', 'MySQL Trigger', '/trigger', 'TRIGGER', NULL, NOW(6)
        );
    END IF;
END//

CREATE TRIGGER trg_auth_transaction_insert
AFTER INSERT ON auth_transactions_authtransaction
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs_auditlog (
        user_id, action, details, ip_address, user_agent,
        request_path, request_method, status_code, timestamp
    ) VALUES (
        NEW.user_id, 'AUTH_REQUEST_CREATED',
        JSON_OBJECT('transaction_id', NEW.transaction_id, 'status', NEW.status),
        '127.0.0.1', 'MySQL Trigger', '/trigger', 'TRIGGER', NULL, NOW(6)
    );
END//

CREATE TRIGGER trg_user_create
AFTER INSERT ON accounts_user
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs_auditlog (
        user_id, action, details, ip_address, user_agent,
        request_path, request_method, status_code, timestamp
    ) VALUES (
        NEW.id, 'USER_CREATED',
        JSON_OBJECT('username', NEW.username, 'email', NEW.email),
        '127.0.0.1', 'MySQL Trigger', '/trigger', 'TRIGGER', NULL, NOW(6)
    );
END//

DELIMITER ;

3) 프로시저 생성 (5개)

DELIMITER //

CREATE PROCEDURE sp_expire_old_transactions()
BEGIN
    UPDATE auth_transactions_authtransaction
    SET status = 'EXPIRED', updated_at = NOW(6)
    WHERE status = 'PENDING' AND expires_at < NOW();
    SELECT ROW_COUNT() AS expired_count;
END//

CREATE PROCEDURE sp_get_daily_stats(IN p_days INT)
BEGIN
    SELECT DATE(created_at) AS stat_date, COUNT(*) AS total,
           SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed
    FROM auth_transactions_authtransaction
    WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL p_days DAY)
    GROUP BY DATE(created_at) ORDER BY stat_date DESC;
END//

CREATE PROCEDURE sp_health_check()
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM accounts_user) AS total_users,
        (SELECT COUNT(*) FROM auth_transactions_authtransaction) AS total_transactions,
        (SELECT COUNT(*) FROM audit_logs_auditlog) AS audit_logs,
        NOW() AS check_time;
END//

CREATE PROCEDURE sp_user_auth_stats(IN p_user_id BIGINT)
BEGIN
    SELECT u.username, COUNT(at.transaction_id) AS total_auth
    FROM accounts_user u
    LEFT JOIN auth_transactions_authtransaction at ON u.id = at.user_id
    WHERE u.id = p_user_id GROUP BY u.id;
END//

-- sp_generate_sample_data는 SUBMISSION_GUIDE.md 5-1단계 참조

DELIMITER ;

4) 역할 데이터 (4개)

INSERT IGNORE INTO accounts_userrole (role_name, description, permissions, created_at)
VALUES 
    ('SUPER_ADMIN', 'Super Administrator', '{"all": true}', NOW(6)),
    ('SERVICE_ADMIN', 'Service Administrator', '{"service_manage": true}', NOW(6)),
    ('AUDITOR', 'Auditor', '{"audit_read": true}', NOW(6)),
    ('USER', 'Regular User', '{"self_manage": true}', NOW(6));

2-1. 전체 백업 생성 (mysqldump)

# 전체 백업 (스키마 + 데이터 + 프로시저 + 트리거)
mysqldump -u idp_user -p'IdP_Secure_2025!' \
  --routines --triggers --events \
  --add-drop-table \
  --single-transaction \
  idp_database > idp_database_backup.sql

# 파일 크기 확인
ls -lh idp_database_backup.sql

# 백업 파일 내용 요약
echo "=== 백업 파일 요약 ==="
grep -c "CREATE TABLE" idp_database_backup.sql
grep -c "CREATE.*VIEW" idp_database_backup.sql  
grep -c "CREATE.*TRIGGER" idp_database_backup.sql
grep -c "CREATE.*PROCEDURE" idp_database_backup.sql

예상 결과:

-rw-r--r-- 1 user user 15M Dec 22 03:18 idp_database_backup.sql
=== 백업 파일 요약 ===
18  (테이블)
3   (뷰)
4   (트리거)
5   (프로시저)

2-2. 개별 항목 확인 명령어

DDL 스키마 확인

# 테이블 목록
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "SHOW TABLES;"

# 특정 테이블 DDL
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW CREATE TABLE accounts_user\G
SHOW CREATE TABLE auth_transactions_authtransaction\G
SHOW CREATE TABLE audit_logs_auditlog\G
"

뷰(View) 확인

# 뷰 목록
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW FULL TABLES WHERE Table_type = 'VIEW';
"

# 뷰 정의 확인
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW CREATE VIEW v_user_masked\G
SHOW CREATE VIEW v_daily_auth_stats\G
SHOW CREATE VIEW v_user_role_permissions\G
"

# 뷰 데이터 확인 (캡처용)
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT * FROM v_user_masked LIMIT 5;
SELECT * FROM v_daily_auth_stats ORDER BY stat_date DESC LIMIT 10;
"

인덱스 확인

# 인덱스 목록
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW INDEX FROM auth_transactions_authtransaction;
"

# 모든 테이블 인덱스 요약
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'idp_database'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
"

제약조건 확인

# CHECK 제약조건
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT CONSTRAINT_NAME, TABLE_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'idp_database';
"

# FOREIGN KEY 제약조건
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, 
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'idp_database' AND REFERENCED_TABLE_NAME IS NOT NULL;
"

트리거 확인

# 트리거 목록
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "SHOW TRIGGERS\G"

# 트리거 동작 확인 (인증 상태 변경 → 감사 로그 자동 기록)
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
-- 변경 전 감사 로그 수
SELECT COUNT(*) AS before_count FROM audit_logs_auditlog;
"

저장 프로시저 확인

# 프로시저 목록
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW PROCEDURE STATUS WHERE Db = 'idp_database';
"

# 프로시저 정의 확인
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW CREATE PROCEDURE sp_generate_sample_data\G
"

원본 데이터 확인

# 데이터 카운트 요약
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT 'Users' as tbl, COUNT(*) as cnt FROM accounts_user
UNION SELECT 'Roles', COUNT(*) FROM accounts_userrole
UNION SELECT 'RoleAssignments', COUNT(*) FROM accounts_userroleassignment
UNION SELECT 'ServiceProviders', COUNT(*) FROM services_serviceprovider
UNION SELECT 'AuthTransactions', COUNT(*) FROM auth_transactions_authtransaction
UNION SELECT 'AuditLogs', COUNT(*) FROM audit_logs_auditlog;
"

# 샘플 데이터 확인
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT id, username, email, is_active, date_joined FROM accounts_user LIMIT 5;
SELECT * FROM accounts_userrole;
SELECT transaction_id, status, created_at FROM auth_transactions_authtransaction LIMIT 5;
"

📸 캡처 대상

SHOW TABLES; 결과 (전체 테이블 목록)
주요 테이블 SHOW CREATE TABLE 결과
SHOW FULL TABLES WHERE Table_type = 'VIEW'; 결과
SHOW INDEX FROM auth_transactions_authtransaction; 결과
SHOW TRIGGERS\G 결과
SHOW PROCEDURE STATUS 결과
데이터 카운트 요약

📁 제출물 3: 테스트 시나리오 SQL (동시성 1건 포함)

3-1. 기본 CRUD 테스트

mysql -u idp_user -p'IdP_Secure_2025!' idp_database

-- ============================================
-- 테스트 시나리오 SQL (복사하여 실행)
-- ============================================

-- [CREATE] 인증 요청 생성
INSERT INTO auth_transactions_authtransaction (
    transaction_id, user_id, service_provider_id, status,
    created_at, updated_at, expires_at, failure_reason
) VALUES (
    'TEST-SCENARIO-001', 3, 1, 'PENDING',
    NOW(6), NOW(6), DATE_ADD(NOW(), INTERVAL 30 MINUTE), ''
);

-- [READ] 조회
SELECT transaction_id, status, created_at 
FROM auth_transactions_authtransaction 
WHERE transaction_id = 'TEST-SCENARIO-001';

-- [UPDATE] 상태 변경
UPDATE auth_transactions_authtransaction
SET status = 'COMPLETED', updated_at = NOW(6)
WHERE transaction_id = 'TEST-SCENARIO-001';

-- [DELETE] 삭제
DELETE FROM auth_transactions_authtransaction 
WHERE transaction_id = 'TEST-SCENARIO-001';

3-2. 복잡한 쿼리 테스트

-- JOIN: 사용자별 인증 이력
SELECT u.username, COUNT(at.transaction_id) AS auth_count, 
       MAX(at.created_at) AS last_auth
FROM accounts_user u
LEFT JOIN auth_transactions_authtransaction at ON u.id = at.user_id
GROUP BY u.id, u.username;

-- 서브쿼리: 평균보다 많은 인증 요청을 받은 사용자
SELECT username FROM accounts_user
WHERE id IN (
    SELECT user_id FROM auth_transactions_authtransaction
    GROUP BY user_id
    HAVING COUNT(*) > (
        SELECT AVG(cnt) FROM (
            SELECT COUNT(*) AS cnt 
            FROM auth_transactions_authtransaction 
            GROUP BY user_id
        ) AS sub
    )
);
-- ※ 주의: user_id가 1명뿐이면 평균과 같아서 결과가 없을 수 있음

-- CTE: 일별 인증 통계
WITH daily_stats AS (
    SELECT DATE(created_at) AS dt, COUNT(*) AS cnt
    FROM auth_transactions_authtransaction
    GROUP BY DATE(created_at)
)
SELECT dt, cnt, SUM(cnt) OVER (ORDER BY dt) AS cumulative
FROM daily_stats
ORDER BY dt DESC LIMIT 10;

-- Window Function: 순위
SELECT transaction_id, status, created_at,
       ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn,
       RANK() OVER (PARTITION BY status ORDER BY created_at) AS rank_by_status
FROM auth_transactions_authtransaction
LIMIT 10;

-- ROLLUP: 상태별 집계
SELECT COALESCE(status, 'TOTAL') AS status, COUNT(*) AS cnt
FROM auth_transactions_authtransaction
GROUP BY status WITH ROLLUP;

3-3. 동시성 테스트 (SELECT FOR UPDATE) ⭐ 필수

⚠️ 중요: 2개의 터미널을 동시에 열어서 실행!

터미널 준비

# 터미널 1
mysql -u idp_user -p'IdP_Secure_2025!' idp_database

# 터미널 2 (새 터미널에서)
mysql -u idp_user -p'IdP_Secure_2025!' idp_database

[터미널 1] 먼저 실행

-- 테스트용 트랜잭션 생성
INSERT IGNORE INTO auth_transactions_authtransaction (
    transaction_id, user_id, service_provider_id, status,
    created_at, updated_at, expires_at, failure_reason
) VALUES (
    'CONCURRENT-TEST-001', 3, 1, 'PENDING',
    NOW(6), NOW(6), DATE_ADD(NOW(), INTERVAL 30 MINUTE), ''
);

-- 트랜잭션 시작 및 행 잠금
SET autocommit = 0;
START TRANSACTION;

SELECT transaction_id, status 
FROM auth_transactions_authtransaction 
WHERE transaction_id = 'CONCURRENT-TEST-001'
FOR UPDATE;
-- ↑ 이 행은 이제 잠김!

SELECT '터미널 1: 행 잠금 완료, 5초 대기...' AS step;
SELECT SLEEP(5);

-- 상태 변경
UPDATE auth_transactions_authtransaction
SET status = 'COMPLETED', updated_at = NOW(6)
WHERE transaction_id = 'CONCURRENT-TEST-001';

COMMIT;
SELECT '터미널 1: 승인 완료!' AS result;

[터미널 2] 터미널 1의 SELECT FOR UPDATE 직후 실행

SET autocommit = 0;
START TRANSACTION;

SELECT '터미널 2: 행 잠금 시도 (대기 예상)...' AS step;

-- 터미널 1이 잠금 해제할 때까지 대기됨 (블로킹!)
SELECT transaction_id, status 
FROM auth_transactions_authtransaction 
WHERE transaction_id = 'CONCURRENT-TEST-001'
FOR UPDATE;
-- ↑ 터미널 1이 COMMIT할 때까지 여기서 대기

-- 잠금 해제 후 조회하면 이미 COMPLETED 상태
SELECT '터미널 2: 상태 확인' AS msg, status 
FROM auth_transactions_authtransaction 
WHERE transaction_id = 'CONCURRENT-TEST-001';

COMMIT;

예상 결과

터미널	동작	결과
터미널 1	행 잠금 → 5초 대기 → COMPLETED 변경 → COMMIT	정상 완료
터미널 2	행 잠금 시도 → 5초간 대기(블로킹) → 잠금 획득	이미 COMPLETED 상태 확인

정리

-- 테스트 데이터 삭제
DELETE FROM auth_transactions_authtransaction 
WHERE transaction_id LIKE 'CONCURRENT-TEST%';

📸 캡처 대상

터미널 1: SELECT FOR UPDATE 실행 및 잠금 상태
터미널 2: 대기 중인 상태 (블로킹)
터미널 1: COMMIT 후 완료
터미널 2: 잠금 획득 후 COMPLETED 상태 확인

📁 제출물 4: 보안/개인정보 설계서

4-1. 개인정보 마스킹 확인

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
-- 마스킹된 사용자 정보 (뷰)
SELECT * FROM v_user_masked LIMIT 5;
"

예상 결과:

+----+----------+------------------+---------------+-----------+
| id | username | email_masked     | phone_masked  | ci_status |
+----+----------+------------------+---------------+-----------+
|  1 | admin    | adm***@example.com | 010****6789  | ***ENCRYPTED*** |
|  3 | testuser1| tes***@example.com | 010****6789  | ***ENCRYPTED*** |
+----+----------+------------------+---------------+-----------+

4-2. 암호화된 CI/DI 필드 확인

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT username, 
       LEFT(ci, 30) AS ci_encrypted_preview,
       LEFT(di, 30) AS di_encrypted_preview
FROM accounts_user;
"

4-3. 비밀번호/PIN 해시 확인

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT username, 
       LEFT(password, 40) AS password_hash_preview,
       LEFT(pin_code, 40) AS pin_hash_preview
FROM accounts_user;
"

예상 결과 (해시 저장 확인):

+----------+------------------------------------------+---------------------------+
| username | password_hash_preview                    | pin_hash_preview          |
+----------+------------------------------------------+---------------------------+
| admin    | pbkdf2_sha256$870000$...                 | pbkdf2_sha256$...         |
| testuser1| pbkdf2_sha256$870000$...                 | pbkdf2_sha256$...         |
+----------+------------------------------------------+---------------------------+

4-4. RBAC(역할 기반 접근 제어) 표

역할 목록

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT role_name, description, permissions 
FROM accounts_userrole;
"

RBAC 권한 매트릭스:

역할	all	service_manage	audit_read	self_manage	설명
SUPER_ADMIN	✅	-	-	-	전체 관리자
SERVICE_ADMIN	-	✅	-	-	서비스 관리자
AUDITOR	-	-	✅	-	감사자
USER	-	-	-	✅	일반 사용자

사용자-역할 매핑

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT u.username, r.role_name, ura.assigned_at
FROM accounts_userroleassignment ura
JOIN accounts_user u ON ura.user_id = u.id
JOIN accounts_userrole r ON ura.role_id = r.id;
"

권한 JSON 추출

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT role_name, 
       JSON_EXTRACT(permissions, '$.all') AS all_access,
       JSON_EXTRACT(permissions, '$.service_manage') AS service_manage,
       JSON_EXTRACT(permissions, '$.audit_read') AS audit_read,
       JSON_EXTRACT(permissions, '$.self_manage') AS self_manage
FROM accounts_userrole;
"

4-5. 감사 로그 테이블 샘플

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
-- 최근 감사 로그 10건
SELECT id, user_id, action, ip_address, timestamp
FROM audit_logs_auditlog 
ORDER BY timestamp DESC LIMIT 10;
"

사용자별 활동 요약

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT u.username, al.action, COUNT(*) as count, MAX(al.timestamp) as last_action
FROM audit_logs_auditlog al
LEFT JOIN accounts_user u ON al.user_id = u.id
GROUP BY u.username, al.action
ORDER BY count DESC;
"

의심 활동 탐지 (실패 3회 이상)

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SELECT u.username, COUNT(*) as failed_count
FROM audit_logs_auditlog al
JOIN accounts_user u ON al.user_id = u.id
WHERE al.action = 'LOGIN_FAILED'
  AND al.timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY u.username
HAVING failed_count >= 3;
"

📸 캡처 대상

SELECT * FROM v_user_masked 결과 (이메일/전화번호 마스킹 확인)
CI/DI 암호화 저장 확인 (원문이 아닌 암호화 데이터)
비밀번호/PIN 해시 저장 확인 (pbkdf2_sha256$...)
accounts_userrole 테이블 (4개 역할)
accounts_userroleassignment 테이블 (사용자-역할 매핑)
audit_logs_auditlog 테이블 샘플 (10건)

📁 제출물 5: 성능 보고서 (튜닝 전후 비교)

5-0. 테스트 데이터 생성 (필수!)

⚠️ 중요: 데이터가 적으면 Table scan이 Index scan보다 빠르게 나옵니다!
최소 10만 건 이상의 데이터가 필요합니다.

mysql -u idp_user -p'IdP_Secure_2025!' idp_database

-- 대량 데이터 생성 프로시저
DELIMITER //
DROP PROCEDURE IF EXISTS sp_generate_sample_data //
CREATE PROCEDURE sp_generate_sample_data(IN record_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE current_batch INT DEFAULT 0;
    
    SET autocommit = 0;
    
    WHILE i < record_count DO
        INSERT INTO auth_transactions_authtransaction (
            transaction_id, user_id, service_provider_id, status,
            created_at, updated_at, expires_at, failure_reason
        ) VALUES (
            REPLACE(UUID(), '-', ''),
            3, 1,
            ELT(FLOOR(1 + RAND() * 4), 'PENDING', 'COMPLETED', 'FAILED', 'EXPIRED'),
            DATE_SUB(NOW(6), INTERVAL FLOOR(RAND() * 90) DAY),
            NOW(6),
            DATE_ADD(NOW(6), INTERVAL 30 MINUTE),
            ''
        );
        
        SET i = i + 1;
        SET current_batch = current_batch + 1;
        
        IF current_batch >= batch_size THEN
            COMMIT;
            SET current_batch = 0;
        END IF;
    END WHILE;
    
    COMMIT;
    SET autocommit = 1;
    
    SELECT CONCAT(record_count, '건 데이터 생성 완료!') AS result;
END //
DELIMITER ;

-- 10만 건 생성 (약 1~2분 소요)
CALL sp_generate_sample_data(100000);

-- 확인
SELECT COUNT(*) AS total FROM auth_transactions_authtransaction;

5-1. 튜닝 전: DATE 함수 사용 (인덱스 사용 불가)

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
EXPLAIN ANALYZE
SELECT * FROM auth_transactions_authtransaction
WHERE DATE(created_at) = '2025-12-20';
"

예상 결과:

-> Filter: (cast(created_at as date) = '2025-12-20')
   -> Table scan on auth_transactions_authtransaction (cost=XXXX rows=100000)
      (actual time=0.XXX..XXX rows=100000 loops=1)

문제점: DATE() 함수를 컬럼에 적용하면 인덱스를 사용할 수 없음!

5-2. 튜닝 후: 범위 조건 사용 (인덱스 사용 가능)

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
EXPLAIN ANALYZE
SELECT * FROM auth_transactions_authtransaction
WHERE created_at >= '2025-12-20 00:00:00'
  AND created_at < '2025-12-21 00:00:00';
"

예상 결과:

-> Index range scan on auth_transactions_authtransaction using idx_tx_created_at
   over ('2025-12-20 00:00:00' <= created_at < '2025-12-21 00:00:00')
   (cost=XXX rows=XXX) (actual time=0.XXX..0.XXX rows=XXX loops=1)

개선점: 범위 조건은 idx_tx_created_at 인덱스 활용 가능!

5-3. 비교 요약표

항목	튜닝 전 (DATE 함수)	튜닝 후 (범위 조건)
실행 계획	`Table scan`	`Index range scan`
사용 인덱스	없음	`idx_tx_created_at`
스캔 행 수	100,000 (전체)	약 1,000 (필터된 결과)
예상 시간	느림	빠름 ✅

5-4. 인덱스 목록 확인

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
SHOW INDEX FROM auth_transactions_authtransaction;
"

5-5. 복합 인덱스 활용 테스트

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
EXPLAIN ANALYZE
SELECT * FROM auth_transactions_authtransaction
WHERE user_id = 3 AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 10;
"

📸 캡처 대상

튜닝 전 EXPLAIN ANALYZE 결과 (Table scan 확인)
튜닝 후 EXPLAIN ANALYZE 결과 (Index range scan 확인)
비교 요약표 (직접 작성)
SHOW INDEX FROM auth_transactions_authtransaction; 결과

📁 제출물 6: 데모 체크리스트 + 화면 캡처

체크리스트

서버 환경

#	항목	확인 명령어	예상 결과	확인
1	MySQL 서버 실행	`sudo service mysql status`	`active (running)`	☐
2	Django 서버 실행	`python3 manage.py runserver`	`Starting development server at http://127.0.0.1:8000/`	☐
3	DB 접속 확인	`mysql -u idp_user -p'...' idp_database -e "SELECT 1"`	`1`	☐

데이터베이스

#	항목	확인 명령어	예상 결과	확인
4	테이블 수	`SHOW TABLES;`	18+ 테이블	☐
5	뷰 수	`SHOW FULL TABLES WHERE Table_type = 'VIEW';`	3+ 뷰	☐
6	역할 데이터	`SELECT * FROM accounts_userrole;`	4개 역할	☐
7	사용자 데이터	`SELECT COUNT(*) FROM accounts_user;`	2+ 사용자	☐
8	인증 트랜잭션	`SELECT COUNT(*) FROM auth_transactions_authtransaction;`	100,000+ 건	☐

웹 UI

#	항목	URL	동작 확인	확인
9	홈페이지	http://localhost:8000/	랜딩 페이지 표시	☐
10	로그인 페이지	http://localhost:8000/accounts/login/	로그인 폼 표시	☐
11	로그인 동작	`testuser1` / `testuser123!`	로그인 성공 → 대시보드 이동	☐
12	대시보드	http://localhost:8000/dashboard/	사용자 정보 표시	☐
13	인증 이력	http://localhost:8000/auth/history/	인증 이력 목록	☐
14	관리자 페이지	http://localhost:8000/admin/	Django Admin 표시	☐
15	Admin 로그인	`admin` / `admin123!`	관리자 로그인 성공	☐

API

#	항목	확인 방법	예상 결과	확인
16	API 인증 요청	`./test_auth_flow.sh`	인증 요청 생성 성공	☐
17	인증 상태 조회	API 응답	`status: PENDING`	☐

보안 기능

#	항목	확인 명령어/방법	예상 결과	확인
18	비밀번호 해시	DB에서 password 컬럼 확인	`pbkdf2_sha256$...`	☐
19	PIN 해시	DB에서 pin_code 컬럼 확인	`pbkdf2_sha256$...`	☐
20	마스킹 뷰	`SELECT * FROM v_user_masked`	이메일/전화번호 마스킹	☐
21	감사 로그	`SELECT * FROM audit_logs_auditlog`	활동 로그 기록	☐

📸 화면 캡처 목록

웹 UI 캡처

홈페이지 - http://localhost:8000/
로그인 페이지 - http://localhost:8000/accounts/login/
로그인 성공 후 대시보드 - http://localhost:8000/dashboard/
인증 이력 페이지 - http://localhost:8000/auth/history/
Django Admin 메인 - http://localhost:8000/admin/
Admin 사용자 목록 - http://localhost:8000/admin/accounts/user/
Admin 인증 트랜잭션 목록 - http://localhost:8000/admin/auth_transactions/authtransaction/

터미널 캡처

MySQL 테이블 목록 - SHOW TABLES;
뷰 목록 - SHOW FULL TABLES WHERE Table_type = 'VIEW';
마스킹 뷰 결과 - SELECT * FROM v_user_masked;
역할 목록 - SELECT * FROM accounts_userrole;
감사 로그 - SELECT * FROM audit_logs_auditlog LIMIT 10;
EXPLAIN ANALYZE 튜닝 전 - Table scan
EXPLAIN ANALYZE 튜닝 후 - Index range scan
동시성 테스트 - 터미널 1, 2 동시 실행 결과

📝 보고서 작성 가이드

문서 구조 예시

1. 프로젝트 개요
   1.1 목적
   1.2 기술 스택
   1.3 시스템 구성도

2. 데이터베이스 설계
   2.1 ERD (EER 다이어그램)
   2.2 테이블 명세서
   2.3 인덱스 설계
   2.4 제약조건

3. 보안/개인정보 설계
   3.1 암호화 정책 (AES-256-GCM, bcrypt)
   3.2 마스킹 정책 (뷰 활용)
   3.3 RBAC 설계
   3.4 감사 로그 정책

4. SQL 구현
   4.1 DDL 스키마
   4.2 뷰 (View)
   4.3 트리거 (Trigger)
   4.4 저장 프로시저 (Stored Procedure)

5. 테스트 결과
   5.1 기능 테스트 (CRUD)
   5.2 동시성 테스트 (SELECT FOR UPDATE)
   5.3 보안 테스트

6. 성능 튜닝
   6.1 튜닝 전 분석 (EXPLAIN)
   6.2 튜닝 후 분석 (인덱스 활용)
   6.3 비교 결과

7. 결론 및 향후 계획

보고서 작성 팁

캡처 이미지 삽입 시: 명령어와 결과를 함께 캡처
EXPLAIN 결과: Table scan vs Index range scan 차이점 강조
동시성 테스트: 두 터미널의 시간 순서대로 설명
RBAC: 권한 매트릭스 표 형태로 정리
감사 로그: 어떤 이벤트가 기록되는지 구체적으로 명시

🔐 계정 정보 요약

구분	사용자명	비밀번호	용도
MySQL	`idp_user`	`IdP_Secure_2025!`	DB 접속
Django Admin	`admin`	`admin123!`	관리자
Test User	`testuser1`	`testuser123!`	테스트 (PIN: 234567)

⚠️ 자주 발생하는 문제

뷰가 없다는 오류

# 오류: Table 'idp_database.v_user_masked' doesn't exist
# 해결: SUBMISSION_GUIDE.md의 5단계 실행

mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
CREATE OR REPLACE VIEW v_user_masked AS
SELECT id, username,
    CONCAT(LEFT(email, 3), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked,
    CONCAT(LEFT(phone_number, 3), '****', RIGHT(phone_number, 4)) AS phone_masked,
    is_active, is_staff, is_superuser, date_joined, last_login,
    '***ENCRYPTED***' AS ci_status, '***ENCRYPTED***' AS di_status
FROM accounts_user;
"

역할 데이터가 없는 오류

# 역할 생성
mysql -u idp_user -p'IdP_Secure_2025!' idp_database -e "
INSERT IGNORE INTO accounts_userrole (role_name, description, permissions, created_at)
VALUES 
    ('SUPER_ADMIN', 'Super Administrator', '{\"all\": true}', NOW(6)),
    ('SERVICE_ADMIN', 'Service Administrator', '{\"service_manage\": true}', NOW(6)),
    ('AUDITOR', 'Auditor', '{\"audit_read\": true}', NOW(6)),
    ('USER', 'Regular User', '{\"self_manage\": true}', NOW(6));
"

EXPLAIN에서 Table scan이 더 빠르게 나오는 경우

# 원인: 데이터가 너무 적음 (1~2건)
# 해결: 10만 건 이상 데이터 생성
CALL sp_generate_sample_data(100000);

작성일: 2025-12-22
프로젝트: IdP Backend System (Simple-ID)
데이터베이스: MySQL 8.0
프레임워크: Django 5.2.7

Name		Name	Last commit message	Last commit date
Latest commit History 6 Commits
accounts		accounts
audit_logs		audit_logs
auth_transactions		auth_transactions
docs		docs
favicon		favicon
idp_backend		idp_backend
scripts		scripts
services		services
sql_scripts		sql_scripts
static		static
templates		templates
.env		.env
.gitignore		.gitignore
.python-version		.python-version
LOGIN_ACCOUNTS.md		LOGIN_ACCOUNTS.md
README.md		README.md
SUBMISSION_GUIDE.md		SUBMISSION_GUIDE.md
db.sqlite3		db.sqlite3
generate_sql_files.sh		generate_sql_files.sh
guideline.md		guideline.md
idp_database_backup.sql		idp_database_backup.sql
manage.py		manage.py
pyproject.toml		pyproject.toml
requirements.txt		requirements.txt
test_api_flow.sh		test_api_flow.sh
test_auth_flow.sh		test_auth_flow.sh
test_scenarios.sql		test_scenarios.sql
test_web_ui.sh		test_web_ui.sh
uv.lock		uv.lock

Folders and files

Latest commit

History

Repository files navigation

📋 IdP Backend System - 과제 제출 가이드

📦 제출물 목록 (총 6가지)

🔧 사전 준비

MySQL 접속 정보

Django 서버 시작

📁 제출물 1: ERD(EER) 다이어그램

생성 방법 (MySQL Workbench)

CLI로 ERD 정보 추출 (대안)

📸 캡처 대상

📁 제출물 2: 백업(SQL) 파일

2-0. 백업 전 사전 적용 (⚠️ 필수!)

현재 적용 상태 확인

누락된 경우 적용하기

1) 뷰 생성 (3개)

2) 트리거 생성 (3개)

3) 프로시저 생성 (5개)

4) 역할 데이터 (4개)

2-1. 전체 백업 생성 (mysqldump)

2-2. 개별 항목 확인 명령어

DDL 스키마 확인

뷰(View) 확인

인덱스 확인

제약조건 확인

트리거 확인

저장 프로시저 확인

원본 데이터 확인

📸 캡처 대상

📁 제출물 3: 테스트 시나리오 SQL (동시성 1건 포함)

3-1. 기본 CRUD 테스트

3-2. 복잡한 쿼리 테스트

3-3. 동시성 테스트 (SELECT FOR UPDATE) ⭐ 필수

터미널 준비

[터미널 1] 먼저 실행

[터미널 2] 터미널 1의 SELECT FOR UPDATE 직후 실행

예상 결과

정리

📸 캡처 대상

📁 제출물 4: 보안/개인정보 설계서

4-1. 개인정보 마스킹 확인

4-2. 암호화된 CI/DI 필드 확인

4-3. 비밀번호/PIN 해시 확인

4-4. RBAC(역할 기반 접근 제어) 표

역할 목록

사용자-역할 매핑

권한 JSON 추출

4-5. 감사 로그 테이블 샘플

사용자별 활동 요약

의심 활동 탐지 (실패 3회 이상)

📸 캡처 대상

📁 제출물 5: 성능 보고서 (튜닝 전후 비교)

5-0. 테스트 데이터 생성 (필수!)

5-1. 튜닝 전: DATE 함수 사용 (인덱스 사용 불가)

5-2. 튜닝 후: 범위 조건 사용 (인덱스 사용 가능)

5-3. 비교 요약표

5-4. 인덱스 목록 확인

5-5. 복합 인덱스 활용 테스트

📸 캡처 대상

📁 제출물 6: 데모 체크리스트 + 화면 캡처

체크리스트

서버 환경

데이터베이스

웹 UI

API

보안 기능

📸 화면 캡처 목록

웹 UI 캡처

터미널 캡처

📝 보고서 작성 가이드

문서 구조 예시

보고서 작성 팁

🔐 계정 정보 요약

⚠️ 자주 발생하는 문제

뷰가 없다는 오류

역할 데이터가 없는 오류

EXPLAIN에서 Table scan이 더 빠르게 나오는 경우

About

Packages