[필기노트] PROCEDURE, 인증메일 구성방법

CREATE TABLE Auths(
    authId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email varchar(255) NOT NULL UNIQUE,
    authData varchar(20) NOT NULL,
    isAuth tinyint NOT NULL DEFAULT 0,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW()
);

 

### PROCEDURE 생성

DROP PROCEDURE IF EXISTS SP_Auths_INSERT ; 

DELIMITER $$
CREATE PROCEDURE SP_Auths_INSERT
(
 IN auth_email varchar(255),
 IN auth_authData varchar(20) 
)
BEGIN
    IF EXISTS (SELECT email FROM Auths WHERE email = auth_email) THEN
        UPDATE Auths SET authData = auth_authData, updatedAt = NOW()
        WHERE email = auth_email;
    ELSE 
        INSERT INTO Auths (email, authData) VALUES (auth_email, auth_authData);
    END IF;
    
END $$

DELIMITER ;

# CALL SP_Auths_INSERT("kimmm@naver.com", "3333");
# 프로시저를 사용하는 명령어 

 

-- 메일 인증의 구성 방법
1. 인증메일을 수신할 이메일을 입력받는다
2. 임의의 난수 6자리를 생성한다.
3. 인증메일을 발송한다.

발송한 인증메일을 어떤방식으로 관리할까?
 - 인증메일을 관리하는 DB Table을 생성해 email, 인증번호를 저장한다.
 - 그 이후 인증메일을 수신하는 API를 생성해 email과 authNumber를 입력받아 authId를 반환한다.
  - 인증에 성공하였을 경우 Auths 테이블에 isAuth를 1로 변경한다.
  - 만약 Auths 테이블의 createdAt이 5분이 지나있다면 인증에 실패하도록 설정한다.
  - 인증에 성공하였어도 createdAt이 30분이 지나있다면 비밀번호 변경에 실패하도록 설정한다.
    - 너무 오랜시간동안 인증을 방치하고 있어도 악용될 요지가 있다.
 - 그 후 password, confirm 데이터와 authId를 받아 변경하도록 설정한다.
 - 인증 메일을 보낼 때 동일한 email이 존재한다면 삭제한 이후 DB에 추가하도록 트리거를 생성한다.



 TODO 만약 회원가입의 이메일 인증을 사용하기 위해서는 email에 Foreign key 를 사용할 수 없다. 어떻게 해결해야하나
 - 모든 데이터를 email을 기준으로 작성한다.