메뉴기반 권한 관리 DB 스키마와 쿼리

메뉴 기반 권한 관리 DB 스키마를 만들고, 몇 가지 기본 쿼리를 작성해 보겠습니다. 이 스키마는 사용자, 역할, 메뉴, 권한 등을 효율적으로 관리하고, 권한에 따른 접근 제어를 구현할 수 있도록 설계되어 있습니다.

1. 스키마 정의

Users 테이블

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Roles 테이블

CREATE TABLE Roles (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255)
);

User_Roles 테이블 (다대다 관계: 사용자와 역할)

CREATE TABLE User_Roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES Roles(role_id) ON DELETE CASCADE
);

Menus 테이블

CREATE TABLE Menus (
    menu_id INT PRIMARY KEY AUTO_INCREMENT,
    menu_name VARCHAR(100) NOT NULL,
    parent_menu_id INT,  
    url VARCHAR(255),
    order_index INT,
    FOREIGN KEY (parent_menu_id) REFERENCES Menus(menu_id) ON DELETE SET NULL
);

Permissions 테이블

CREATE TABLE Permissions (
    permission_id INT PRIMARY KEY AUTO_INCREMENT,
    permission_name VARCHAR(50) NOT NULL,
    description VARCHAR(255)
);

Role_Permissions 테이블 (다대다 관계: 역할과 권한)

CREATE TABLE Role_Permissions (
    role_id INT,
    permission_id INT,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES Roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES Permissions(permission_id) ON DELETE CASCADE
);

Menu_Permissions 테이블 (다대다 관계: 메뉴와 권한)

CREATE TABLE Menu_Permissions (
    menu_id INT,
    permission_id INT,
    PRIMARY KEY (menu_id, permission_id),
    FOREIGN KEY (menu_id) REFERENCES Menus(menu_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES Permissions(permission_id) ON DELETE CASCADE
);

2. 쿼리 예시

1) 사용자를 추가하기

INSERT INTO Users (username, password, email) VALUES ('johndoe', 'hashed_password', 'johndoe@example.com');

2) 역할(Role)을 추가하기

INSERT INTO Roles (role_name, description) VALUES ('Admin', 'Administrator with full access');

3) 사용자에게 역할을 할당하기

INSERT INTO User_Roles (user_id, role_id) VALUES (1, 1);

4) 메뉴(Menu)를 추가하기

INSERT INTO Menus (menu_name, parent_menu_id, url, order_index) VALUES ('Dashboard', NULL, '/dashboard', 1);

5) 권한(Permission)을 추가하기

INSERT INTO Permissions (permission_name, description) VALUES ('View Dashboard', 'Permission to view the dashboard');

6) 역할(Role)에 권한을 할당하기

INSERT INTO Role_Permissions (role_id, permission_id) VALUES (1, 1);

7) 메뉴(Menu)에 권한을 할당하기

INSERT INTO Menu_Permissions (menu_id, permission_id) VALUES (1, 1);

8) 사용자의 특정 메뉴 접근 권한 확인하기

특정 사용자가 특정 메뉴에 접근 가능한지 확인하려면, 사용자 ID와 메뉴 ID를 기준으로 권한을 확인하는 쿼리를 사용합니다.

SELECT COUNT(*)
FROM User_Roles ur
JOIN Role_Permissions rp ON ur.role_id = rp.role_id
JOIN Menu_Permissions mp ON rp.permission_id = mp.permission_id
WHERE ur.user_id = 1
AND mp.menu_id = 1;

결과가 0보다 크면 접근 권한이 있는 것이고, 0이면 접근 권한이 없는 것입니다.

9) 사용자가 접근 가능한 모든 메뉴 목록 조회하기

SELECT DISTINCT m.menu_name, m.url
FROM Users u
JOIN User_Roles ur ON u.user_id = ur.user_id
JOIN Role_Permissions rp ON ur.role_id = rp.role_id
JOIN Menu_Permissions mp ON rp.permission_id = mp.permission_id
JOIN Menus m ON mp.menu_id = m.menu_id
WHERE u.user_id = 1
ORDER BY m.order_index;

위 쿼리는 user_id = 1 사용자가 접근 가능한 메뉴를 조회하여, 메뉴의 정렬 순서(order_index)에 따라 정렬하여 반환합니다.