Spring Security学习笔记(二)——访问数据库(一)

Bertha 。 2021-09-26 05:48 331阅读 0赞

在SpringSecurity学习笔记（一）——入门篇中我们可以看到，尽管能够按照我们的要求访问相关页面，但是有一点是完全不符合逻辑的，即相关的用户和对应密码以及权限都是写死的，这个在实际开发中是几乎不可能存在的，所以，本篇的主要需要解决的问题就是通过访问数据库获取相应的用户，密码，以及权限。

## 1.项目结构如下图： ##

![Center][]

## 2.在pom.xml中引入相应jar包 ##

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>springSecurity</groupId>
      <artifactId>core</artifactId>
      <packaging>war</packaging>
      <version>0.0.1-SNAPSHOT</version>
      <name>core Maven Webapp</name>
      <url>http://maven.apache.org</url>
      <properties>
      	  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  
          <spring.version>4.2.0.RELEASE</spring.version>  
      	  <spring.security.version>4.2.0.RELEASE</spring.security.version>  
     	  
    	  <slf4j.version>1.7.7</slf4j.version>
    	  <log4j.version>1.2.17</log4j.version>
      </properties>
      
      <dependencies>
       <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.12</version>
          <scope>test</scope>
        </dependency>
        <dependency>
    	    <groupId>org.hamcrest</groupId>
    	    <artifactId>hamcrest-core</artifactId>
    	    <version>1.3</version>
    	</dependency>
    
        
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>
    
        
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
       
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.36</version>
        </dependency>
    
        
    	<dependency>
    		<groupId>jstl</groupId>
    		<artifactId>jstl</artifactId>
    		<version>1.2</version>
    	</dependency>
    	
    	
    	<dependency>
    		<groupId>log4j</groupId>
    		<artifactId>log4j</artifactId>
    		<version>${log4j.version}</version>
    	</dependency>
    	
    	<dependency>
    		<groupId>org.slf4j</groupId>
    		<artifactId>slf4j-api</artifactId>
    		<version>${slf4j.version}</version>
    	</dependency>
    	<dependency>
    		<groupId>org.slf4j</groupId>
    		<artifactId>slf4j-log4j12</artifactId>
    		<version>${slf4j.version}</version>
    	</dependency>
    
        
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.2.3</version>
        </dependency>
        
    	  <dependency>
    	    <groupId>commons-dbcp</groupId>
    	    <artifactId>commons-dbcp</artifactId>
    	    <version>1.4</version>
    	</dependency>
    	
    	<dependency>
    	    <groupId>commons-lang</groupId>
    	    <artifactId>commons-lang</artifactId>
    	    <version>2.6</version>
    	</dependency>
    		
    		
    	<dependency>
    		<groupId>javax</groupId>
    		<artifactId>javaee-api</artifactId>
    		<version>7.0</version>
    		<scope>provided</scope>
    	</dependency>
    	
    	  
        <dependency>  
            <groupId>org.springframework.security</groupId>  
            <artifactId>spring-security-core</artifactId>  
            <version>${spring.security.version}</version>  
        </dependency>  
          
        <dependency>  
            <groupId>org.springframework.security</groupId>  
            <artifactId>spring-security-config</artifactId>  
            <version>${spring.security.version}</version>  
        </dependency>  
          
        <dependency>  
            <groupId>org.springframework.security</groupId>  
            <artifactId>spring-security-taglibs</artifactId>  
            <version>${spring.security.version}</version>  
        </dependency>  
          
        <dependency>  
            <groupId>org.springframework.security</groupId>  
            <artifactId>spring-security-web</artifactId>  
            <version>${spring.security.version}</version>  
        </dependency>  
        
      </dependencies>
      <build>
        <finalName>core</finalName>
         <resources>  
          
          <resource>  
            <directory>src/main/java</directory>  
            <includes>  
              <include>**/*.xml</include>  
            </includes>  
          </resource>
          
            
          <resource>
    			<directory>src/main/resources</directory>
    	  </resource>
        </resources> 
      </build>
    </project>

## 3.创建表格    ##

### 1.创建USERS表（红色标注为重要字段） ###

![Center 1][]

### 2.创建ROLE表 ###

![Center 2][]

### 3.创建USER\_ROLE表 ###

![Center 3][]

## 4.配置spring-security.xml ##

<?xml version="1.0" encoding="UTF-8"?>
    <beans:beans xmlns="http://www.springframework.org/schema/security"
        xmlns:beans="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/security
        http://www.springframework.org/schema/security/spring-security.xsd">
    
        <http auto-config='true'>
        	
            <intercept-url pattern="/admin.jsp" access="hasRole('ROLE_ADMIN')" />
            <intercept-url pattern="/**" access="hasRole('ROLE_USER')" />
            
        </http>
    
    	<authentication-manager>
            <authentication-provider>
    			 <jdbc-user-service data-source-ref="dataSource"
    			  users-by-username-query="SELECT USERNAME,PASSWORD,status as enabled FROM USERS WHERE USERNAME=?"
                  authorities-by-username-query="select u.username,r.role_name as authority
                                                 from USERS u
                                                 join USER_ROLE ur
                                                   on u.id=ur.user_id
                                                 join ROLE r
                                                   on r.role_id=ur.role_id
                                                where u.username=?"/>
            </authentication-provider>
        </authentication-manager>
         <beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    	    <beans:property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    	    <beans:property name="url" value="jdbc:mysql://123.207.179.XX:3306/XXXXX"/>
    	    <beans:property name="username" value="XXXXXX"/>
    	    <beans:property name="password" value="XXXXXX"/>
    	</beans:bean> 
    </beans:beans>

## 5.配置web.xml ##

<?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://java.sun.com/xml/ns/javaee"
        xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
        version="2.5">
      <display-name>spring-security测试</display-name>
     
      <context-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>
                classpath:spring-security.xml
            </param-value>
        </context-param>
       
        <context-param>
              <param-name>log4jConfigLocation</param-name>
              <param-value>classpath:log4j.properties</param-value>
         </context-param>
        
        <filter>
            <filter-name>springSecurityFilterChain</filter-name>
            <filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
        </filter>
        <filter-mapping>
            <filter-name>springSecurityFilterChain</filter-name>
            <url-pattern>/*</url-pattern>
        </filter-mapping>
        
        
        <listener>
            <listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
        </listener>
        
        <listener>
            <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
        </listener>
        <listener>
            <listener-class>org.springframework.web.context.request.RequestContextListener</listener-class>
           </listener>
           
           
          <listener>
               <listener-class>org.springframework.security.web.session.HttpSessionEventPublisher</listener-class>
           </listener>
        
        <listener>
            <listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
        </listener>
        
        
        <session-config>
            <session-timeout>15</session-timeout>
        </session-config>
        
    </web-app>

## 6.效果 ##

![Center 4][]

![Center 5][]

数据库内用户名和密码

![Center 6][]

##     ##

## 7.注意点 ##

spring-security.xml中角色必须<intercept-url pattern="/\*\*" access="hasRole('ROLE\_USER')" />，不能<intercept-url pattern="/\*\*" access="ROLE\_USER" />，如果缺少hasRole，会报权限错误

[Center]: /images/20210923/b136b6a85dde48f0b99454bc92f60850.png
[Center 1]: /images/20210923/c5b22b1c78be485c963065fdf2ae6f32.png
[Center 2]: /images/20210923/0697f799f7ac467c8159190b426442cb.png
[Center 3]: /images/20210923/08e3ba0cbc614840aa65e3f137574f17.png
[Center 4]: /images/20210923/425c8e0cc61d4e26a1ac1d898111b202.png
[Center 5]: /images/20210923/265bb4610d7c4752935abd8c9354506a.png
[Center 6]: /images/20210923/9a77adf5487041e88b2de93ffb5a5036.png