04. spring security DB를 사용한 로그인 인증 & 권한 처리

이번 포스팅은 MY-SQL을 이용해 간단히 로그인 인증 & 권한 처리 하는법을 알아 보겠다.

우선 pom.xml에 mysql관련 라이브러리를 등록한다.

 
<!-- mysql -->
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.22</version>
 <type>jar</type>
 <scope>compile</scope>
</dependency>
 
<dependency>
 <groupId>commons-collections</groupId>
 <artifactId>commons-collections</artifactId>
 <version>3.2.1</version>
</dependency>
 
<dependency>
 <groupId>commons-io</groupId>
 <artifactId>commons-io</artifactId>
 <version>2.4</version>
</dependency> 

 

1.mysql에 테이블을 만든다.

 

예제들을 보면 테이블을 사용자 테이블과 권한테이블 두개로 나눠서 하지만 그냥 간단한 테스트기 때문에 테이블 하나로 사용하겠다.

  
CREATE TABLE `user` (
  `EMAIL` varchar(255) NOT NULL,
  `PASSWD` varchar(255) NOT NULL,
  `ENABLED` int(1) NOT NULL DEFAULT '1',
  `AUTHORITY` varchar(20) NOT NULL DEFAULT 'ROLE_USER',
  PRIMARY KEY (`EMAIL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `user` VALUES ('guest','guest',1,'ROLE_USER'),('niee','zzzz',1,'ROLE_ADMIN'),('test','test',1,'ROLE_USER');

 

2.spring에 DB설정을 한다.

예제 소스는 META-INF폴더를 이용한 jndi를 사용

META-INF를 이용한 jndi 사용 방법 : http://blog.naver.com/niee/220081996796

 

3.appServlet/datasource-context.xml추가

 
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
 xmlns:tx="http://www.springframework.org/schema/tx" xmlns:p="http://www.springframework.org/schema/p"
 xmlns:aop="http://www.springframework.org/schema/aop"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-3.1.xsd
   http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd 
   http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.1.xsd">
 
 
 <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
  <property name="jndiName" value="study/security"/>
  <property name="resourceRef" value="true"></property>
 </bean>
 
</beans> 

 

4.web.xml의 context-param에 datasource-context.xml등록

 
<context-param>
 <param-name>contextConfigLocation</param-name>
 <param-value>
  /WEB-INF/spring/root-context.xml
  /WEB-INF/spring/appServlet/datasource-context.xml
  /WEB-INF/spring/security/security-context.xml
 </param-value>
</context-param> 

 

5.security-context.xml 다음 부분 추가

 
<jdbc-user-service data-source-ref="dataSource" id="userService"
 users-by-username-query="SELECT EMAIL as username, PASSWD as password,ENABLE D as enabled​ FROM user WHERE EMAIL=?"
 authorities-by-username-query="SELECT EMAIL as username, AUTHORITY AS authority 
          FROM user u
          WHERE EMAIL=?"/>
 
<authentication-manager>
 <authentication-provider user-service-ref="userService">
 </authentication-provider>
</authentication-manager> 

 

6.서버 재시작 후 테스트

 

예제 주소 : https://github.com/ParkMinKyu/security

 

예제 사용시 META-INF의 context.xml 수정 후 사용

 

------------------------------------------------------

디비를 사용하여 인증을 할때는 

users-by-username-query="SELECT EMAIL as username, PASSWD as password,ENABLED as enabled​ FROM user WHERE EMAIL=?"

 

authorities-by-username-query="SELECT EMAIL as username, AUTHORITY AS authority 

FROM user u

WHERE EMAIL=? 

 

부분에서 보듯이 사용자를 인증하는 users-by-username-query​의 

EMAIL as username, PASSWD as password,ENABLED as enabled

칼럼과

 

권한을 가져오는 ​authorities-by-username-query의​ EMAIL as username, AUTHORITY AS authority

부분의 칼럼명만 맞추어 주면 된다. ​