Spring Security - 5. mybatis를 이용하여 기존테이블을 통한 인증/권한 처리

lectureNote/SPRING

Spring Security - 5. mybatis를 이용하여 기존테이블을 통한 인증/권한 처리

shiherlis 2022. 12. 21. 10:53

1. DB 생성

-- 시큐리티 권한 테이블
create table user_auth
(
	user_id varchar(16) not null,
	auth varchar(50) not null
);
-- 시큐리티 권한테이블 fk생성 
alter table user_auth add FOREIGN KEY(user_id) REFERENCES tb_user(user_id) ON DELETE CASCADE;

권한테이블에 아이디에 맞는 권한을 설정해 넣어준다.
ex )

-- 관리자
INSERT INTO public.user_auth
(user_id, auth)
VALUES('admin', 'ROLE_ADMIN');

2. Mybatis 설정

1) Mapper 생성

src/main/resources/member/memberMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <mapper namespace="kr.co.springsecurity.member.memberMapper">
  
	<resultMap type="MemberDto" id="memberMap">
        <id property="user_id" column="user_id"/>
        <result property="user_id" column="user_id"/>
        <result property="user_name" column="user_name"/>
        <result property="user_pw" column="user_pw"/>
        <result property="user_gender" column="user_gender"/>
        <result property="user_birth_date" column="user_birth_date"/>
        <result property="user_email" column="user_email"/>
        <result property="user_phone_number" column="user_phone_number"/>
        <result property="user_postcode" column="user_postcode"/>
        <result property="user_rNameAddr" column="user_rnameaddr"/>
        <result property="user_detailAddr" column="user_detailaddr"/>
        <result property="user_regdate" column="user_regdate"/>
        <result property="user_grade" column="user_grade"/>
        <result property="user_social_type" column="user_social_type"/>
        <collection property="authList" resultMap="authMap"></collection>
     </resultMap>
     
     <resultMap type="AuthDto" id="authMap">
        <result property="user_id" column="user_id"/>
        <result property="auth" column="auth"/>
     </resultMap>
     
    <select id="read" resultMap="memberMap">
   	select tb_user.user_id, user_pw, user_name, user_gender, user_email, user_phone_number, user_postcode, 
           user_rNameAddr, user_detailAddr, user_detailAddr, user_detailAddr, user_regdate, user_grade, user_social_type, user_auth.auth
   	from tb_user LEFT OUTER JOIN user_auth on tb_user.user_id = user_auth.user_id
   	where tb_user.user_id = #{user_id}
  </select> 
 </mapper>

Member 객체를 가져오는 경우에 tb_user와 user_auth 테이블을 조인하여 처리할 수 있도록
ResultMap으로 묶어서 사용

2) mybatis-config 설정

<configuration>
	<typeAliases>
		<typeAlias alias="MemberDto" type="kr.co.springsecurity.member.MemberDto" />
		<typeAlias alias="AuthDto" type="kr.co.springsecurity.member.AuthDto" />
	</typeAliases>
</configuration>

alias사용을 위해 mybatis-config 에서 alias를 정의해 줌

3) root-context

<!-- Root Context: defines shared resources visible to all other web components -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="org.postgresql.Driver" />
		<property name="url" value="jdbc:postgresql://localhost:5432/ycc" />
		<property name="username" value="postgres" />
		<property name="password"  value="0111" />
	</bean>
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="configLocation" value="classpath:mybatis-config.xml" />
	<property name="mapperLocations" value="classpath:mapper/*Mapper.xml" />
	</bean>
	
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sqlSessionFactory" />
	</bean>
	<context:component-scan base-package="kr.co.springsecurity.**" />
	</beans>

DB 연결을 위한 빈 등록
sqlSessionFactory/sqlSession 빈 등록

3. 데이터 연결을 위한 클래스 설정

1) MemberDto / AuthDto

import java.util.Date;
import java.util.List;

import org.springframework.security.core.userdetails.User.UserBuilder;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
public class MemberDto {
	private String user_id;
	private String user_name;
	private String user_pw;
	private String user_gender;
	private String birthYear;
	private String birthMonth;
	private String birthDay;
	private Date user_birth_date;
	private String user_email;
	private String user_phone_number;
	private String user_postcode;
	private String user_rNameAddr;
	private String user_detailAddr;
	private Date user_regdate;
	private String user_grade;
	private String user_social_type;

	private List<AuthDto> authList;

}

https://javaiary.tistory.com/48

lombok 과 어노테이션

@AllArgsConstructor : 모든 변수가 포함된 생성자 @NoArgsConstructor : 변수가 없는 생성자. 기본 생성자 @Builder : 원하는 변수만을 넣어 만든 생성자 @Getter : private 변수를 받아올 수 있게 해주는 getter생성 @

javaiary.tistory.com

import lombok.Data;

@Data
public class AuthDto {

	private String user_id;
	private String auth;
}

MemberDto의 경우 기존에 있던 DB에 맞추어 생성해 놓은 클래스를 그대로 사용하였다.

2) MemberMapper Interface/Impl

import org.springframework.stereotype.Repository;

@Repository
public interface MemberMapperInterface {

	public MemberDto read(String user_id) throws Exception;
	
}

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class MemberMapperImpl implements MemberMapperInterface {

	@Autowired
	private SqlSession session;
	private static String namespace = "kr.co.springsecurity.member.memberMapper.";

	@Override
	public MemberDto read(String user_id) throws Exception {
		MemberDto dto = null;
		try {
			System.out.println("read");
			dto = session.selectOne(namespace + "read", user_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("dto at impl : " + dto);

		return session.selectOne(namespace + "read", user_id);
	}

}

read 쿼리문 실행을 위해 메서드를 호출할 수 있는 클래스 생성
인터페이스에 정의해주고 Impl클래스에서 구현해줌
두 군데 모두 @Repository 어노테이션을 빼먹지 않도록 주의할 것

3) CustomUserDetailsService 클래스

@Log4j
public class CustomUserDetailsService implements UserDetailsService{

	@Setter(onMethod_ = { @Autowired })
	private MemberMapperInterface memberMapper;

	@Override
	public UserDetails loadUserByUsername(String user_id) throws UsernameNotFoundException {
		
		log.warn("Load User By UserName : " + user_id);
		return null;
	}
	
	
}

4) CustomUser 클래스

@Getter
public class CustomUser extends User{
	public CustomUser(String username, String password, Collection<? extends GrantedAuthority> auth) {
		super(username, password, auth);
	}

	private static final long serialVersiounUID =1L;
	
	private MemberDto member;
	
	public CustomUser(MemberDto dto) {
		
		    super(dto.getUser_id(), dto.getUser_pw(), 
		    	dto.getAuthList().stream().map(auth 
		    			-> new SimpleGrantedAuthority(auth.getAuth())).collect(Collectors.toList()));
		
		    this.member = dto;
		  }

org.springframework.security.core.userdetails.User클래스를 상속받기 때문에 부모클래스의 생성자를 호출해야만 정상적인 객체 생성 가능
MemberDto를 파라미터로 전달하여 User 클래스에 맞게 생성자 호출
AuthDto 인스턴스는 GrantedAuthority객체로 변환해야 하므로 stream()과 map()을 이용해서 처리

5) security-context.xml

<bean id="bcryptPasswordEncoder" class="org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder" />

 <security:authentication-manager>
	      <security:authentication-provider user-service-ref="customUserDetailsService">

	      <!-- <security:user-service> 
	        <security:user name="member" password="{noop}member" authorities="ROLE_MEMBER"/>
	        <security:user name="admin" password="{noop}admin" authorities="ROLE_MEMBER, ROLE_ADMIN"/>
	      </security:user-service> -->
	    
     	  <security:password-encoder ref="bcryptPasswordEncoder" />
	    
	    </security:authentication-provider>
  </security:authentication-manager>

bcryptPasswordEncoder 빈 등록

6) CustomUserDeailsService 클래스 (UserDetailsService 구현)

@Log4j
public class CustomUserDetailsService implements UserDetailsService{

	@Setter(onMethod_ = { @Autowired })
	private MemberMapperInterface memberMapper;

	@Override
	public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
		
		log.warn("Load User By UserName : " + userName);
	    MemberDto dto = memberMapper.read(userName);
	    
	        log.warn("queried by member mapper: " + dto);
	    
	        return dto == null ? null : new CustomUser(dto);
	      }
	}

내부적으로 MemberMapper를 이용해서 MemberDto를 조회하여
MemberDto의 인스턴스를 얻을 수 있다면 CustomUser타입의 객체로 변환하여 반환

4. 확인

1) test/java

MemberInjectionTest

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import kr.co.springsecurity.member.MemberDto;
import kr.co.springsecurity.member.MemberMapperInterface;
import lombok.Setter;
import lombok.extern.log4j.Log4j;

@RunWith(SpringRunner.class)
@ContextConfiguration({ "file:src/main/webapp/WEB-INF/spring/root-context.xml" })
@Log4j
public class MemberInjectionTest {

	@Setter(onMethod_ = @Autowired)
	private MemberMapperInterface dao;

	@Test
	public void testRead() throws Exception {
		MemberDto memberDto = dao.read("admin");
		
		System.out.println(memberDto);
		log.info(memberDto);

		memberDto.getAuthList().forEach(authdto -> log.info(authdto));

	}
}

test 클래스를 통해 DB와의 연결이 잘 되었는지 확인하였음

2) 로그인 확인

2-1) 회원 데이터 인서트(test/java)

test/java 를 이용하여 회원테이블에 회원 삽입
test/java 를 이용하여 권한테이블에 권한 설정(삽입)

MemberTest

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.ParseException;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import lombok.Setter;
import lombok.extern.log4j.Log4j;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"file:src/main/webapp/WEB-INF/spring/root-context.xml",
                  "file:src/main/webapp/WEB-INF/spring/security-context.xml"})

@Log4j
public class MemberTest {
   
   @Setter(onMethod_ = @Autowired)
   private PasswordEncoder pwencoder;

   @Setter(onMethod_ = @Autowired)
   private DataSource ds;
   
   @Test
   public void testInsertMember() throws ParseException {
      
      //not null 컬럼만 insert 
      String sql =  "insert into tb_user(user_id, user_pw, user_name, user_gender, "
            + "user_birth_date,user_email, user_phone_number,user_postcode, user_rNameAddr, user_regdate ,user_grade) "
            + "values (?,?,?,?,?,?,?,?,?,?,?)";
      
   

        
      for(int i = 0; i<= 20; i++) {
         
         Connection con = null;
         PreparedStatement pstmt = null;
         
         try {
            con =   ds.getConnection();
            pstmt = con.prepareStatement(sql);
         
            pstmt.setString(2, pwencoder.encode("pw" + i));
            
            //user_birth_date String -> Date로 형변환
            Date dateStr = Date.valueOf(1995 +"-"+ 02 +"-"+ 22);
//            System.out.println(dateStr);
//            System.out.println(dateStr.getClass().getName());
            
            
            if (i<10) {
               pstmt.setString(1, "user"+i);
               pstmt.setString(3, "회원"+i);
               pstmt.setString(4, "F");
               pstmt.setDate(5, dateStr);
               pstmt.setString(6, "user"+i+"@google.com");
               pstmt.setString(7, "01012345678");
               pstmt.setString(8, "06611");
               pstmt.setString(9, "서울특별시 서초구 서초대로77길 55");
               pstmt.setDate(10, new Date(System.currentTimeMillis()));
               pstmt.setString(11, "일반회원");
            } else if (i < 15){
               pstmt.setString(1, "Inst"+i);
               pstmt.setString(3, "강사"+i);
               pstmt.setString(4, "F");
               pstmt.setDate(5,  dateStr);
               pstmt.setString(6, "user"+i+"@google.com");
               pstmt.setString(7, "01012345678");
               pstmt.setString(8, "06611");
               pstmt.setString(9, "서울특별시 서초구 서초대로77길 55");
               pstmt.setDate(10, new Date(System.currentTimeMillis()));
               pstmt.setString(11, "강사");
            } else {
               pstmt.setString(1, "admin"+i);
               pstmt.setString(3, "관리자"+i);
               pstmt.setString(4, "F");
               pstmt.setDate(5, dateStr);
               pstmt.setString(6, "user"+i+"@google.com");
               pstmt.setString(7, "01012345678");
               pstmt.setString(8, "06611");
               pstmt.setString(9, "서울특별시 서초구 서초대로77길 55");
               pstmt.setDate(10, new Date(System.currentTimeMillis()));
               pstmt.setString(11, "관리자");
            }
            
            pstmt.executeUpdate();
            
         }catch(Exception e) {
            e.printStackTrace();
         }finally {
            if(pstmt != null) {
               try {
                  pstmt.close();
               }catch (Exception e) {}
            }if(con != null) {
               try {
                  con.close();
               }catch (Exception e) {}
            }
         }
         
      }
   }
}

AuthTest

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.ParseException;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import lombok.Setter;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"file:src/main/webapp/WEB-INF/spring/root-context.xml",
                  "file:src/main/webapp/WEB-INF/spring/security-context.xml"})
public class AuthTest {
   
   @Setter(onMethod_ = @Autowired)
   private DataSource ds;
   
   @Test
   public void testInsertAuth() throws ParseException {
      
      
      String sql =  "insert into user_auth(user_id, auth) values (?,?)";
      
      
      for(int i = 0; i<= 20; i++) {
         
         Connection con = null;
         PreparedStatement pstmt = null;
         
         try {
            con =   ds.getConnection();
            pstmt = con.prepareStatement(sql);
         
            if (i<10) {
               pstmt.setString(1, "user"+i);
               pstmt.setString(2, "ROLE_MEMBER");
               
            } else if (i < 15){
               pstmt.setString(1, "Inst"+i);
               pstmt.setString(2, "ROLE_INST");
               
            } else {
               pstmt.setString(1, "admin"+i);
               pstmt.setString(2, "ROLE_ADMIN");
               
            }
            
            pstmt.executeUpdate();
            
         }catch(Exception e) {
            e.printStackTrace();
         }finally {
            if(pstmt != null) {
               try {
                  pstmt.close();
               }catch (Exception e) {}
            }if(con != null) {
               try {
                  con.close();
               }catch (Exception e) {}
            }
         }
         
      }
   }


   }

2-2) 로그인 확인

user 테이블에서 encoding된 비밀번호들을 볼 수 있음
따라서 비밀번호 컬럼의 속성을 varchar(100) 으로 변경해줌

5. 오류의 경우

1) pw인코딩 오류 : BCryptPasswordEncoder

org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder - Encoded password does not look like BCrypt

DB에 pw가 인코딩 되지 않은 상태로 존재할 때 발생
pw인코딩을 해줄 수 있는 방식으로 인서트 하거나 인코딩해 주는 것으로 해결 가능

2) 권한 설정 오류 : InternalAuthenticationServiceException

org.springframework.security.authentication.InternalAuthenticationServiceException: A granted authority textual representation is required

권한이 없는 회원정보로 로그인 시도했을 때 발생
권한테이블에서 권한 지정으로 해결 가능

# 코드로 배우는 웹 프로젝트 참고

저작자표시 변경금지 (새창열림)