[spring] security ⑤ - 비밀번호 암호화, 회원정보에 따른 권한 설정

create table users( authorities
			username varchar(50) primary key,
            password varchar(50) not null,
            enabled varchar(1) default '1'
	);

사용자 정보 저장하는 테이블

create table authorities( 
		username varchar(50),
		authority varchar(50),
        
		constraint fk_authorities_users foreign key(username) references users(username)
	);

사용자 권한 정보를 저장하는 테이블

 

authorities - constraint fk_authorities_users foreign key(username) references users(username)는 직접 걸자

workbench 테이블생성

not null

fk걸기

insert into users(username, password) values('user01','1234');
insert into users(username, password) values('member01','1234');
insert into users(username, password) values('admin01','1234');

insert into authorities(username, authority) values('user01', 'ROLE_USER');
insert into authorities(username, authority) values('member01', 'ROLE_MEMBER');
insert into authorities(username, authority) values('admin01', 'ROLE_ADMIN');
insert into authorities(username, authority) values('admin01', 'ROLE_MEMBER');

값 넣기

 

위와 같은 기능을 전산에 담아 db에 넣음

security-context

 

주석처리

pom.xml

<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>   
				
		<!-- 외부 라이브러리 추가 -->
		
		<!-- https://mvnrepository.com/artifact/org.springframework.security/spring-security-core -->
		<dependency>
		    <groupId>org.springframework.security</groupId>
		    <artifactId>spring-security-core</artifactId>
		    <version>5.0.6.RELEASE</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.springframework.security/spring-security-config -->
		<dependency>
		    <groupId>org.springframework.security</groupId>
		    <artifactId>spring-security-config</artifactId>
		    <version>5.0.6.RELEASE</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.springframework.security/spring-security-taglibs -->
		<dependency>
		    <groupId>org.springframework.security</groupId>
		    <artifactId>spring-security-taglibs</artifactId>
		    <version>5.0.6.RELEASE</version>
		</dependency>
		

		<!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<version>8.0.32</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.1</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
			<version>5.0.1</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.30</version>
			<scope>provided</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc4.1 -->
		<dependency>
			<groupId>org.bgee.log4jdbc-log4j2</groupId>
			<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
			<version>1.16</version>
		</dependency>


		<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.5</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.11.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/net.coobird/thumbnailator -->
		<dependency>
			<groupId>net.coobird</groupId>
			<artifactId>thumbnailator</artifactId>
			<version>0.4.20</version>
		</dependency>



		<!-- 외부 라이브러리 추가 -->
		
		
		
		
		     
	</dependencies>

기존 외부 라이브러리 추가

root-context

board의 root-context 복붙

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd">
	
	<!-- Root Context: defines shared resources visible to all other web components -->
	<!-- hikariCP : 연결정보 저장객체 -->
	<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
		<!-- <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" 
			/> -->
		<!-- <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springdb"/> -->
		<property name="driverClassName"
			value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy" />
		<property name="jdbcUrl"
			value="jdbc:log4jdbc:mysql://localhost:3306/springdb" />
		<property name="username" value="root" />
		<property name="password" value="1234" />
	</bean>
	<!-- hikariCP : 연결정보 저장객체 -->

	<!-- dataSource(CP): 디비연결 -->
	<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"
		destroy-method="close">

		<constructor-arg ref="hikariConfig" />
	</bean>
	<!-- dataSource(CP) : 디비연결 -->	
		
</beans>

security-context

 

db에 연결됨

main/resource - log4jdbc.log4j2.properties, logback.xml

붙여넣기

passwordEncoder가 안되서 500에러가 뜸

security/CustomNoopPasswordEncoder 생성(임시)

시큐리티 - JDBC 연결 테스트를 위한 임시 암호화 객체 (실제 암호화 동작X)

security-context

임시로 passwordEncoder를 만든거기 때문에

인증매니저로 가서

db에 담아놓기만해도 시큐리티가 알아서 조회해서 권한까지 체크한 다음에 권한이 있으면 권한에 따른 페이지 이동까지 하게됨

왜 가능하냐면,, security-context에서 각 주소별 권한을 부여했기 때문임

 

tbl_member로 이동

enabled 추가, userpw-varchar(200)으로 변경

create table tbl_member_auth(
			userid varchar(50) not null,
            auth varchar(50) not null,
		constraint fk_member_auth foreign key(userid) references tbl_member(userid)
	);

인증테이블 생성

 

tbl_member의 회원리스트 삭제

pom.xml에 spring-test 있는지 확인 후

security-context

CustomNoopPasswordEncoder는 이제 안쓰기에 주석 후 시큐리티 관련 객체 생성

 

test/MemberTest 생성 - 회원정보 비밀번호 암호화

package com.itwillbs.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;

import javax.inject.Inject;
import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.zaxxer.hikari.HikariDataSource;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(
		locations = {"file:src/main/webapp/WEB-INF/spring/*-context.xml"}
						)
public class MemberTest {
	
	private static final Logger logger = LoggerFactory.getLogger(MemberTest.class);
	
	@Inject
	private DataSource ds;
	
	@Inject
	private PasswordEncoder pwEncoder;
	
	@Test
	public void 회원정보생성_테스트() throws Exception{
		Connection con = ds.getConnection();
		String sql = "insert into tbl_member(userid,userpw,username,useremail) "
				+ "values(?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		
		for(int i=0;i<100;i++) {
			
			// 비밀번호는 모두 암호화
			pstmt.setString(2, pwEncoder.encode("1234"));
			
			if(i<80) {
				pstmt.setString(1, "user"+i);
				//pstmt.setString(2, "1234");
				pstmt.setString(3, "사용자"+i);
				pstmt.setString(4, "user"+i+"@itwill.com");
			}else if(i<90) {
				pstmt.setString(1, "member"+i);
				//pstmt.setString(2, "1234");
				pstmt.setString(3, "회원"+i);
				pstmt.setString(4, "member"+i+"@itwill.com");
			}else {
				pstmt.setString(1, "admin"+i);
				//pstmt.setString(2, "1234");
				pstmt.setString(3, "관리자"+i);
				pstmt.setString(4, "admin"+i+"@itwill.com");
			}
		}//for
		
		pstmt.executeUpdate();		
	}
	
}

 

암호화된 비밀번호가 생김

회원정보에 따른 권한 설정

// 회원 정보에 따른 권한 설정
	@Test
	public void 회원정보권한_테스트() throws Exception{
		
		Connection con = ds.getConnection();
		
		for(int i=0;i<100;i++) {
			String sql = "insert into tbl_member_auth (userid,auth) values(?,?)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			
			if(i<80) {
				pstmt.setString(1, "user"+i);
				pstmt.setString(2, "ROLE_USER");
			}else if(i<90) {
				pstmt.setString(1, "member"+i);
				pstmt.setString(2, "ROLE_MEMBER");
			}else {
				pstmt.setString(1, "admin"+i);
				pstmt.setString(2, "ROLE_ADMIN");
			}
			
			pstmt.executeUpdate();
		}//for
	}