show databases;
create database if not exists isetdx_happyfriday;
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin1234';
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin1234';
GRANT ALL PRIVILEGES ON isetdx_happyfriday.* TO 'admin'@'localhost';
GRANT ALL PRIVILEGES ON isetdx_happyfriday.* TO 'admin'@'%';
FLUSH PRIVILEGES;
drop table authority;
CREATE TABLE authority (
authority_name VARCHAR(50) NOT NULL,
description VARCHAR(255) NULL,
create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ( authority_name )
) COMMENT = '사용자 권한 테이블', ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO authority (authority_name, description) VALUES
('ROLE_USER', '일반 사용자 권한'),
('ROLE_ADMIN', '관리자 권한'),
('ROLE_MANAGER', '매니저 권한');
commit;
select * from authority;
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,
create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_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 (create_date)
) COMMENT = '사용자 정보 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
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', '매니저');
select * from users;
drop table 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;
INSERT INTO user_authority (user_id, authority_name) VALUES
(1, 'ROLE_ADMIN'),
(1, 'ROLE_USER'),
(2, 'ROLE_USER'),
(3, 'ROLE_MANAGER'),
(3, 'ROLE_USER');
select * from user_authority;
-- 로그인 이력 테이블 (선택사항)
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 토큰 블랙리스트 테이블 (로그아웃된 토큰 관리)
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;
-- 생성된 테이블들 확인
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.create_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.create_date;