웹 사이트 권한 관리 DB 설계: 상세설계 및 워크플로우

목표

1. 소스코드에서는 권한의 종류만 상수로 정의하고, 권한은 추가만 가능하도록 설계합니다. (수정 및 삭제는 불가능합니다.)
2. 동적으로 권한을 추가할 수 있는 유연한 확장성을 확보합니다.
3. 불필요한 공간 낭비를 최소화합니다.
4. 관리자와 개발자가 쉽게 이해하고 유지보수할 수 있는 구조를 만듭니다.

데이터베이스 설계

권한 관리 시스템의 핵심은 효율적인 데이터베이스 설계에 있습니다. 다음은 각 테이블의 상세 구조와 그 역할에 대한 설명입니다.

테이블 구조

테이블 이름	컬럼	설명
Permission	idx (PK), name, description	개별 권한을 정의합니다. 예: 읽기, 쓰기, 삭제 등
Role	idx (PK), name, description	권한들의 집합을 정의합니다. 예: 편집자, 관리자 등
Permission_Role	permission_idx (FK), role_idx (FK)	권한과 역할을 연결하는 다대다 관계 테이블입니다.
Group	idx (PK), name, description	사용자 그룹을 정의합니다. 예: 일반 사용자, VIP 사용자 등
Group_Role	group_idx (FK), role_idx (FK)	그룹과 역할을 연결하는 다대다 관계 테이블입니다.
User	idx (PK), username, email, group_idx (FK)	개별 사용자 정보를 저장합니다.

이 설계를 통해 권한, 역할, 그룹 간의 복잡한 관계를 효과적으로 관리할 수 있습니다.
예를 들어, '편집자' 역할에 '글쓰기', '수정', '삭제' 권한을 부여하고, 이 '편집자' 역할을 'VIP 사용자' 그룹에 할당할 수 있습니다.

샘플 데이터

각 테이블의 샘플 데이터를 살펴보겠습니다. 이 데이터는 시스템의 작동 방식을 이해하는 데 도움이 될 것입니다.

Permission 테이블

idx	name	description
1	read_post	게시글 읽기 권한
2	write_post	게시글 작성 권한
3	edit_post	게시글 수정 권한
4	delete_post	게시글 삭제 권한
5	upload_file	파일 업로드 권한

Role 테이블

idx	name	description
1	viewer	읽기 전용 사용자
2	contributor	글 작성 가능 사용자
3	editor	글 수정 가능 사용자
4	admin	관리자

Permission_Role 테이블

permission_idx	role_idx
1	1
1	2
1	3
1	4
2	2
2	3
2	4
3	3
3	4
4	4
5	2
5	3
5	4

Group 테이블

idx	name	description
1	basic_users	기본 사용자 그룹
2	premium_users	프리미엄 사용자 그룹
3	moderators	중재자 그룹
4	administrators	관리자 그룹

Group_Role 테이블

group_idx	role_idx
1	1
2	2
3	3
4	4

User 테이블

idx	username	email	group_idx
1	john_doe	john@example.com	1
2	jane_smith	jane@example.com	2
3	mod_alice	alice@example.com	3
4	admin_bob	bob@example.com	4

이 샘플 데이터를 바탕으로, 시스템이 어떻게 작동하는지 예를 들어 설명해 드리겠습니다:

1. john_doe는 basic_users 그룹에 속해 있어 viewer 역할을 가지며, 게시글을 읽을 수만 있습니다.
2. jane_smith는 premium_users 그룹에 속해 있어 contributor 역할을 가지며, 게시글을 읽고 작성할 수 있습니다.
3. mod_alice는 moderators 그룹에 속해 있어 editor 역할을 가지며, 게시글을 읽고, 작성하고, 수정할 수 있습니다.
4. admin_bob은 administrators 그룹에 속해 있어 admin 역할을 가지며, 모든 권한을 가집니다.

트리거 설계

권한이 없는 액션이 발생할 때 에러를 발생시키는 트리거를 구현하여 데이터베이스 레벨에서 추가적인 보안을 확보할 수 있습니다. 다음은 MariaDB를 기준으로 작성된 트리거 예시입니다:

DELIMITER //

CREATE FUNCTION check_user_permission(user_idx INT, required_permission VARCHAR(50)) 
RETURNS BOOLEAN
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE user_group_id INT;
    DECLARE has_permission BOOLEAN;
    
    -- 사용자의 그룹 ID 조회
    SELECT group_idx INTO user_group_id FROM User WHERE idx = user_idx;

    -- 사용자가 해당 액션을 수행할 권한이 있는지 확인
    SELECT EXISTS (
        SELECT 1
        FROM Group_Role gr
        JOIN Permission_Role rp ON gr.role_idx = rp.role_idx
        JOIN Permission p ON rp.permission_idx = p.idx
        WHERE gr.group_idx = user_group_id AND p.name = required_permission
    ) INTO has_permission;

    RETURN has_permission;
END //

CREATE TRIGGER check_write_post_permission
BEFORE INSERT ON Post
FOR EACH ROW
BEGIN
    IF NOT check_user_permission(NEW.user_idx, 'write_post') THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'User does not have permission to write post';
    END IF;
END //

CREATE TRIGGER check_edit_post_permission
BEFORE UPDATE ON Post
FOR EACH ROW
BEGIN
    IF NOT check_user_permission(NEW.user_idx, 'edit_post') THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'User does not have permission to edit post';
    END IF;
END //

CREATE TRIGGER check_delete_post_permission
BEFORE DELETE ON Post
FOR EACH ROW
BEGIN
    IF NOT check_user_permission(OLD.user_idx, 'delete_post') THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'User does not have permission to delete post';
    END IF;
END //

DELIMITER ;

이 트리거 설계는 다음과 같이 작동합니다:

1. check_user_permission() 함수는 사용자 ID와 필요한 권한을 인자로 받아 해당 사용자가 권한을 가지고 있는지 확인합니다.
2. 각 트리거(INSERT, UPDATE, DELETE)는 해당하는 작업을 수행하기 전에 check_user_permission() 함수를 호출하여 권한을 확인합니다.
3. 권한이 없으면 SIGNAL 문을 사용하여 사용자 정의 에러를 발생시킵니다.

주요 워크플로우

1. 새 사용자 등록 워크플로우

1. 사용자가 등록 양식을 작성합니다.
2. 시스템은 기본 사용자 그룹이 존재하는지 확인합니다.
3. 기본 그룹이 없다면 생성합니다. 이는 시스템 초기 설정 시 자동으로 처리될 수 있습니다.
4. 사용자에게 기본 그룹을 할당합니다. 이를 통해 모든 새 사용자는 최소한의 기본 권한을 가지게 됩니다.
5. 사용자 정보를 데이터베이스에 저장합니다.

이 과정을 통해 새로운 사용자가 등록될 때마다 일관된 초기 권한 설정이 가능해집니다.

2. 권한 확인 워크플로우

1. 사용자가 특정 작업을 요청합니다. (예: 게시글 수정)
2. 시스템은 사용자의 ID와 요청된 작업을 확인합니다.
3. 사용자가 속한 그룹을 데이터베이스에서 조회합니다.
4. 해당 그룹에 할당된 역할들을 확인합니다.
5. 각 역할에 포함된 권한들을 확인합니다.
6. 요청된 작업에 대한 권한이 있으면 작업을 허용하고, 없으면 거부합니다.

이 과정을 통해 모든 사용자 작업에 대해 실시간으로 권한을 검증할 수 있습니다.

3. 관리자의 권한 수정 워크플로우

1. 관리자가 시스템에 로그인합니다. 이 단계에서 관리자의 권한도 확인됩니다.
2. 권한을 수정할 대상 사용자를 선택합니다.
3. 새로운 권한 세트가 필요한지 확인합니다. 이는 기존 그룹으로 충분한지 아니면 새 그룹이 필요한지를 판단하는 과정입니다.
4. 새 그룹이 필요하면 생성하고, 아니면 기존 그룹을 선택합니다. 새 그룹 생성 시 필요한 역할들도 함께 생성할 수 있습니다.
5. 선택된 그룹에 필요한 역할을 할당합니다. 이 단계에서 기존 역할을 사용하거나 새 역할을 만들 수 있습니다.
6. 대상 사용자에게 선택된 그룹을 할당합니다.

이 워크플로우를 통해 관리자는 유연하게 사용자의 권한을 관리할 수 있습니다.

4. 결제 시스템(포인트 구매)과 권한 변경 워크플로우

1. 사용자가 시스템에 로그인합니다.
2. 사용자가 구매할 포인트 패키지를 선택합니다.
3. 결제 정보를 입력합니다.
4. 결제가 성공적으로 처리되었는지 확인합니다.
5. 결제가 성공한 경우:
   a. 사용자의 포인트를 증가시킵니다.
   b. 사용자의 누적 포인트를 확인합니다.
   c. 누적 포인트가 프리미엄 사용자 기준을 충족하는지 확인합니다.
   d. 기준을 충족하면 사용자의 그룹을 'premium_users'로 변경합니다.
   e. 그룹 변경에 따라 사용자의 권한이 자동으로 업데이트됩니다.
6. 결제가 실패한 경우, 오류 메시지를 표시합니다.

이 워크플로우에서, 예를 들어 일반 사용자가 포인트를 구매하여 프리미엄 사용자 기준을 충족하면, 그의 그룹이 'basic_users'에서 'premium_users'로 변경됩니다. 이에 따라 사용자는 'viewer' 역할에서 'contributor' 역할로 자동 변경되어, 게시글 작성 권한을 얻게 됩니다.

구현 시 주의사항:

• 결제 처리는 보안에 특히 주의를 기울여야 합니다.
• 포인트 증가와 그룹 변경은 하나의 트랜잭션으로 처리하여 데이터 일관성을 유지해야 합니다.
• 그룹 변경 시 관련된 모든 권한 테이블이 올바르게 업데이트되어야 합니다.
• 권한 변경에 대한 상세한 로그를 유지하여 문제 발생 시 추적이 가능하도록 해야 합니다.

이 네 가지 워크플로우를 통해 사용자 등록부터 권한 관리, 그리고 결제 시스템과의 연동까지 전체적인 시스템의 작동 방식을 이해할 수 있습니다. 각 워크플로우는 서로 연결되어 있으며, 전체 시스템의 유연성과 확장성을 보장합니다.

장점

1. 유연성: 새로운 권한, 역할, 그룹을 쉽게 추가할 수 있습니다. 시스템의 확장에 따라 권한 구조를 조정할 수 있습니다.
2. 확장성: 프로젝트의 성장에 따라 권한 시스템을 쉽게 확장할 수 있습니다. 새로운 기능이 추가되어도 기존 구조를 크게 변경하지 않고 권한을 관리할 수 있습니다.
3. 관리 용이성: 그룹을 통한 효율적인 권한 관리가 가능합니다. 많은 수의 사용자를 그룹으로 묶어 한 번에 권한을 부여하거나 수정할 수 있습니다.
4. 코드 분리: 권한 로직을 데이터베이스에서 관리하여 애플리케이션 코드의 복잡성을 줄일 수 있습니다. 이는 유지보수성을 높이고 버그 발생 가능성을 줄입니다.
5. 세분화된 제어: 권한을 세밀하게 제어할 수 있어, 각 사용자나 그룹에 대해 정확히 필요한 만큼의 권한만 부여할 수 있습니다.

주의사항

• 이 설계는 모든 상황에 적합하지 않을 수 있습니다. 프로젝트의 규모와 복잡성에 따라 적절히 조정이 필요할 수 있습니다.
• 프로젝트의 특성과 요구사항에 따라 적절히 수정하여 적용해야 합니다. 예를 들어, 특정 기능에 대한 임시 권한이 필요한 경우 추가적인 테이블이나 로직이 필요할 수 있습니다.
• 보안과 성능을 고려하여 구현해야 합니다. 권한 확인 과정이 너무 복잡하면 시스템 성능에 영향을 줄 수 있으므로, 적절한 캐싱 전략을 고려해야 합니다.
• 데이터의 일관성을 유지하기 위해 트랜잭션 관리에 주의를 기울여야 합니다. 예를 들어, 그룹 삭제 시 관련된 모든 테이블의 데이터가 정확히 삭제되어야 합니다.
• MariaDB의 트리거는 재귀적으로 동작할 수 있으므로, 무한 루프에 빠지지 않도록 주의해야 합니다.

결론

이 접근 방식을 통해 유연하고 확장 가능한 권한 관리 시스템을 구축할 수 있습니다. 소스 코드의 변경을 최소화하면서도 동적으로 권한을 관리할 수 있어, 다양한 요구사항에 대응할 수 있는 강력한 시스템을 만들 수 있습니다.

워크플로우를 통해 시스템의 작동 방식을 명확히 이해하고 구현할 수 있으며, 이는 개발 팀 전체의 이해도를 높이고 효율적인 협업을 가능하게 합니다.

하지만 이 시스템을 구현할 때는 항상 프로젝트의 특성과 요구사항을 고려해야 하며, 필요에 따라 적절히 수정하고 최적화해야 합니다. 또한 정기적인 검토와 업데이트를 통해 시스템이 프로젝트의 성장과 변화에 계속해서 적합하도록 유지해야 합니다.