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>

• 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');

• spring에 DB설정을 한다.

• 예제 소스는 META-INF폴더를 이용한 jndi를 사용 META-INF를 이용한 jndi 사용 방법 :http://blog.naver.com/niee/220081996796

• 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>

• 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>

• 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>

• 서버 재시작 후 테스트

• 예제 주소 : 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 부분의 칼럼명만 맞추어 주면 된다. ​