MySql 계정생성 및 권한 부여

// 데이터베이스 생성

CREATE USER 'auth'@'localhost' IDENTIFIED BY '123';

 

// 사용자 계정 생성

CREATE DATABASE IF NOT EXISTS auth;

 

// 권한 부여

GRANT ALL PRIVILEGES ON `auth`.* TO 'auth'@'localhost';

 

// 새로고침

FLUSH PRIVILEGES;

 

// 유저테이블 생성

CREATE TABLE user
(
    user_key          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id           VARCHAR(255) NOT NULL UNIQUE,
    user_password     VARCHAR(255) NOT NULL,
    name         VARCHAR(255) NOT NULL,
    phone_number      VARCHAR(255) NULL,
    accession_date    DATE         NOT NULL,
    withdrawal_date   DATE NULL,
    gender            ENUM('M', 'F') NULL,
    user_status       ENUM('Y', 'N') NOT NULL DEFAULT 'Y',
    is_deleted        TINYINT(1) NOT NULL DEFAULT 0,
    password_recovery DATE         NOT NULL,
    role              VARCHAR(255) NULL
);

 

// user insert문

INSERT INTO user (
    user_id,
    user_password,
    name,
    phone_number,
    accession_date,
    withdrawal_date,
    gender,
    user_status,
    is_deleted,
    password_recovery,
    role
) VALUES (
    '123@gmail.com',
    '$2a$10$RY7OlfTyyx1WGKLygKEwAuHXOVkWjDTEuN1TpeLg1E6HDlU/SIMpe',
    '양준우',
    '010-1234-5678',
    NOW(),
    NULL,
    'M',
    'Y',
    0,
    NOW(),
    'User'
);

 

//  이메일 인증 테이블

CREATE TABLE EMAIL_AUTH
(
    email_auth_key BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    auth_email     VARCHAR(255) NOT NULL,
    auth_code      VARCHAR(255) NOT NULL,
    auth_status    ENUM('Y','N') NOT NULL DEFAULT 'N',
    auth_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

인텔리제이 데이터베이스 연결

 

// board 테이블 생성

CREATE TABLE board (
    board_key BIGINT AUTO_INCREMENT PRIMARY KEY,
    board_title VARCHAR(255) NOT NULL,
    board_content TEXT NOT NULL,
    board_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    view_count INT NOT NULL DEFAULT 0,
    board_status ENUM('ACTIVE', 'INACTIVE', 'DELETED') NOT NULL
);