Spring Security笔记：使用数据库进行用户认证(form login using database)

在前一节，学习了如何自定义登录页，但是用户名、密码仍然是配置在xml中的，这样显然太非主流，本节将学习如何把用户名/密码/角色存储在db中，通过db来实现用户认证

一、项目结构

与前面的示例相比，因为要连接db，所以多出了一个spring-database.xml用来定义数据库连接，此外，为了演示登录用户权限不足的场景，加了一个页面403.jsp，用来统一显示权限不足的提示信息

二、数据库表结构(oracle环境)

create table T_USERS
(
  d_username VARCHAR2(50) not null,
  d_password VARCHAR2(60),
  d_enabled  NUMBER(1)
);
alter table T_USERS
  add constraint PK_USERS_USERNAME primary key (D_USERNAME) ;
  
create table T_USER_ROLES
(
  d_user_role_id NUMBER(10) not null,
  d_username     VARCHAR2(50),
  d_role         VARCHAR2(50)
);
alter table T_USER_ROLES
  add constraint PK_USER_ROLES primary key (D_USER_ROLE_ID);
alter table T_USER_ROLES
  add constraint IDX_UNI_ROLE_USERNAME unique (D_USERNAME, D_ROLE);

这里创建了二张表，一张用来保存用户名/密码，另一张用来保存用户所属的权限角色，表名和字段名无所谓，可以随便改，但是用户表中，必须要有"用户名/密码/帐号的有效状态"这三列信息，权限角色表必须要有“用户名/权限角色”这二列信息

再insert几条测试数据

insert into T_USERS (D_USERNAME, D_PASSWORD, D_ENABLED)
values ('YJMYZZ', '123456', 1);

insert into T_USERS (D_USERNAME, D_PASSWORD, D_ENABLED)
values ('MIKE', 'MIKE123', 1); 

insert into T_USER_ROLES (D_USER_ROLE_ID, D_USERNAME, D_ROLE)
values (1, 'MIKE', 'POWER');

insert into T_USER_ROLES (D_USER_ROLE_ID, D_USERNAME, D_ROLE)
values (2, 'YJMYZZ', 'ADMIN');

insert into T_USER_ROLES (D_USER_ROLE_ID, D_USERNAME, D_ROLE)
values (3, 'YJMYZZ', 'POWER');

这里插入了二个用户YJMYZZ/MIKE，而且MIKE属于POWER组，YJMYZZ同时属于POWER\ADMIN二个权限组

三、spring-security.xml

<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-3.0.xsd
    http://www.springframework.org/schema/security
    http://www.springframework.org/schema/security/spring-security-3.2.xsd">

    <http auto-config="true" use-expressions="true">
        <intercept-url pattern="/admin**" access="hasRole('ADMIN')" />
        <!-- access denied page -->
        <access-denied-handler error-page="/403" />
        <form-login login-page="/login" default-target-url="/welcome"
            authentication-failure-url="/login?error" username-parameter="username"
            password-parameter="password" />
        <logout logout-success-url="/login?logout" />
        <!-- enable csrf protection -->
        <csrf />
    </http>

    <!-- Select users and user_roles from database -->
    <authentication-manager>
        <authentication-provider>
            <jdbc-user-service data-source-ref="dataSource"
                users-by-username-query="select d_username username,d_password password, d_enabled enabled from t_users where d_username=?"
                authorities-by-username-query="select d_username username, d_role role from t_user_roles where d_username=?  " />
        </authentication-provider>
    </authentication-manager>

</beans:beans>

注意第9行，这里使用了一个el表达式，目的是/admin开头的url，必须有ADMIN角色的登录用户才可访问

第11行，表示如果登录用户权限不够，将跳转到/403这个url

24,25这二行，指定了查询用户/角色的sql语句，注意：虽然前面提到了用户/角色这二张表的表名/字段名可以随便写，但是写sql时，用户名的别名必须是username，密码列的别名必须是password，帐号有效状态的别名必须是enabled，而权限角色列的别名必须是role

23行指定了db数据源，它的详细定义在 spring-database.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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@172.21.***.***:1521:orcl" />
        <property name="username" value="***" />
        <property name="password" value="***" />
    </bean>
</beans>

本文使用的是oracle数据库，如果是其它数据库，请自行调整上面的内容

四、Controller 

package com.cnblogs.yjmyzz;

import org.springframework.security.authentication.AnonymousAuthenticationToken;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class HelloController {

    @RequestMapping(value = { "/", "/welcome" }, method = RequestMethod.GET)
    public ModelAndView welcome() {

        ModelAndView model = new ModelAndView();
        model.addObject("title",
                "Spring Security Login Form - Database Authentication");
        model.addObject("message", "This is default page!");
        model.setViewName("hello");
        return model;

    }

    @RequestMapping(value = "/admin", method = RequestMethod.GET)
    public ModelAndView admin() {

        ModelAndView model = new ModelAndView();
        model.addObject("title",
                "Spring Security Login Form - Database Authentication");
        model.addObject("message", "This page is for ROLE_ADMIN only!");
        model.setViewName("admin");
        return model;

    }

    @RequestMapping(value = "/login", method = RequestMethod.GET)
    public ModelAndView login(
            @RequestParam(value = "error", required = false) String error,
            @RequestParam(value = "logout", required = false) String logout) {

        ModelAndView model = new ModelAndView();
        if (error != null) {
            model.addObject("error", "Invalid username and password!");
        }

        if (logout != null) {
            model.addObject("msg", "You've been logged out successfully.");
        }
        model.setViewName("login");

        return model;

    }

    // for 403 access denied page
    @RequestMapping(value = "/403", method = RequestMethod.GET)
    public ModelAndView accesssDenied() {

        ModelAndView model = new ModelAndView();

        // check if user is login
        Authentication auth = SecurityContextHolder.getContext()
                .getAuthentication();
        if (!(auth instanceof AnonymousAuthenticationToken)) {
            UserDetails userDetail = (UserDetails) auth.getPrincipal();
            model.addObject("username", userDetail.getUsername());
        }

        model.setViewName("comm/403");
        return model;

    }

}

66-71行演示了如何在服务端判断一个用户是否已经登录

五、视图页面

hello.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="sec"
    uri="http://www.springframework.org/security/tags"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>${title}</title>
</head>
<body>
    <h1>Title : ${title}</h1>
    <h1>Message : ${message}</h1>
    <sec:authorize access="hasRole('POWER')">
        <!-- For login user -->
        <c:url value="/j_spring_security_logout" var="logoutUrl" />
        <form action="${logoutUrl}" method="post" id="logoutForm">
            <input type="hidden" name="${_csrf.parameterName}"
                value="${_csrf.token}" />
        </form>
        <script>
            function formSubmit() {
                document.getElementById("logoutForm").submit();
            }
        </script>

        <c:if test="${pageContext.request.userPrincipal.name != null}">
            <h2>
                User : ${pageContext.request.userPrincipal.name} | <a
                    href="javascript:formSubmit()"> Logout</a> | <a href="admin">admin</a>
            </h2>
        </c:if>
    </sec:authorize>

    <sec:authorize access="isAnonymous()">
        <br />
        <h2>
            <a href="login">login</a>
        </h2>
    </sec:authorize>

</body>
</html>

注意一下：14、27、35这三行，它们演示了如何在jsp端判断用户具有的角色权限、是否已登录等用法

403.jsp

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<body>
    <h1>HTTP Status 403 - Access is denied</h1>

    <c:choose>
        <c:when test="${empty username}">
            <h2>You do not have permission to access this page!</h2>
        </c:when>
        <c:otherwise>
            <h2>
                Username : ${username} <br /> You do not have permission to access
                this page!
            </h2>
        </c:otherwise>
    </c:choose>

</body>
</html>

admin.jsp

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@page session="true"%>
<html>
<body>
    <h1>Title : ${title}</h1>
    <h1>Message : ${message}</h1>

    <c:url value="/j_spring_security_logout" var="logoutUrl" />
    <form action="${logoutUrl}" method="post" id="logoutForm">
        <input type="hidden" name="${_csrf.parameterName}"
            value="${_csrf.token}" />
    </form>
    <script>
        function formSubmit() {
            document.getElementById("logoutForm").submit();
        }
    </script>

    <c:if test="${pageContext.request.userPrincipal.name != null}">
        <h2>
            Welcome : ${pageContext.request.userPrincipal.name} | <a
                href="javascript:formSubmit()"> Logout</a> | <a href="welcome">welcome</a>
        </h2>
    </c:if>

</body>
</html>

因为在xml中已经配置了/admin开头的请求url，必须具有ADMIN角色权限，所以admin.jsp端反而不用任何额外的判断了

文中示例源代码下载：SpringSecurity-LoginForm-Database-XML.zip

参考文章： Spring Security Form Login Using Database