테이블

2. Authority 테이블 (권한 테이블)

-- 권한 테이블 생성
CREATE TABLE authority (
    authority_name VARCHAR(50) NOT NULL PRIMARY KEY,
    description VARCHAR(255) NULL,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    COMMENT = '사용자 권한 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 기본 권한 데이터 삽입
INSERT INTO authority (authority_name, description) VALUES 
('ROLE_USER', '일반 사용자 권한'),
('ROLE_ADMIN', '관리자 권한'),
('ROLE_MANAGER', '매니저 권한');

3. Users 테이블 (사용자 테이블)

-- 사용자 테이블 생성
CREATE TABLE users (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_enabled BOOLEAN DEFAULT TRUE,
    is_locked BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP NULL,
    
    INDEX idx_users_email (email),
    INDEX idx_users_name (name),
    INDEX idx_users_created_date (created_date),
    COMMENT = '사용자 정보 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4. User_Authority 테이블 (사용자-권한 연결 테이블)

-- 사용자-권한 연결 테이블 생성 (다대다 관계)
CREATE TABLE user_authority (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    authority_name VARCHAR(50) NOT NULL,
    granted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (authority_name) REFERENCES authority(authority_name) ON DELETE CASCADE,
    
    UNIQUE KEY uk_user_authority (user_id, authority_name),
    INDEX idx_user_authority_user_id (user_id),
    INDEX idx_user_authority_name (authority_name),
    COMMENT = '사용자-권한 연결 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

5. 테스트 사용자 데이터 삽입

-- 테스트용 사용자 생성 (비밀번호는 BCrypt로 암호화된 "password123")
INSERT INTO users (email, password, name) VALUES 
('admin@example.com', '$2a$10$rYL4UG70wpz1nRLJTsF2yutQPq/RYkSjqZ8pqzx8d3DnAHcvNw7M6', '관리자'),
('user@example.com', '$2a$10$rYL4UG70wpz1nRLJTsF2yutQPq/RYkSjqZ8pqzx8d3DnAHcvNw7M6', '일반사용자'),
('manager@example.com', '$2a$10$rYL4UG70wpz1nRLJTsF2yutQPq/RYkSjqZ8pqzx8d3DnAHcvNw7M6', '매니저');

-- 사용자별 권한 부여
INSERT INTO user_authority (user_id, authority_name) VALUES 
(1, 'ROLE_ADMIN'),
(1, 'ROLE_USER'),
(2, 'ROLE_USER'),
(3, 'ROLE_MANAGER'),
(3, 'ROLE_USER');

6. 추가 테이블들 (옵션)
로그인 이력 테이블

-- 로그인 이력 테이블 (선택사항)
CREATE TABLE login_history (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    is_success BOOLEAN DEFAULT TRUE,
    failure_reason VARCHAR(255) NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_login_history_user_id (user_id),
    INDEX idx_login_history_time (login_time),
    COMMENT = '로그인 이력 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

JWT 토큰 블랙리스트 테이블

-- JWT 토큰 블랙리스트 테이블 (로그아웃된 토큰 관리)
CREATE TABLE jwt_blacklist (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    token_id VARCHAR(255) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    expired_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_jwt_blacklist_token_id (token_id),
    INDEX idx_jwt_blacklist_user_id (user_id),
    INDEX idx_jwt_blacklist_expired_at (expired_at),
    COMMENT = 'JWT 토큰 블랙리스트 테이블'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

7. 사용자 및 권한 부여

-- MariaDB 사용자 생성 및 권한 부여
CREATE USER IF NOT EXISTS 'jwt_user'@'localhost' IDENTIFIED BY 'jwt_password123!';
CREATE USER IF NOT EXISTS 'jwt_user'@'%' IDENTIFIED BY 'jwt_password123!';

-- 데이터베이스 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON jwt_auth_db.* TO 'jwt_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON jwt_auth_db.* TO 'jwt_user'@'%';

-- 권한 적용
FLUSH PRIVILEGES;

8. 테이블 정보 확인 쿼리

-- 생성된 테이블들 확인
SHOW TABLES;

-- 테이블 구조 확인
DESCRIBE users;
DESCRIBE authority;
DESCRIBE user_authority;

-- 테이블 생성 쿼리 확인
SHOW CREATE TABLE users;
SHOW CREATE TABLE authority;
SHOW CREATE TABLE user_authority;

-- 데이터 확인
SELECT u.id, u.email, u.name, u.created_date, 
       GROUP_CONCAT(ua.authority_name) as authorities
FROM users u
LEFT JOIN user_authority ua ON u.id = ua.user_id
GROUP BY u.id, u.email, u.name, u.created_date;