spring security使用数据库资源

国内对权限系统的基本要求是将用户权限和被保护资源都放在数据库里进行管理，在这点上Spring Security并没有给出官方的解决方案，为此我们需要对Spring Security进行扩展。、

数据库表结构

这次我们使用五张表，user用户表，role角色表，resc资源表相互独立，它们通过各自之间的连接表实现多对多关系。我们自己定义的表结构。

-- 资源
create table resc(
    id bigint,
    name varchar(50),
    res_type varchar(50),
    res_string varchar(200),
    priority integer,
    descn varchar(200)
);
alter table resc add constraint pk_resc primary key(id);
alter table resc alter column id bigint generated by default as identity(start with 1);

-- 角色
create table role(
    id bigint,
    name varchar(50),
    descn varchar(200)
);
alter table role add constraint pk_role primary key(id);
alter table role alter column id bigint generated by default as identity(start with 1);

-- 用户
create table user(
    id bigint,
    username varchar(50),
    password varchar(50),
    status integer,
    descn varchar(200)
);
alter table user add constraint pk_user primary key(id);
alter table user alter column id bigint generated by default as identity(start with 1);

-- 资源角色连接表
create table resc_role(
    resc_id bigint,
    role_id bigint
);
alter table resc_role add constraint pk_resc_role primary key(resc_id, role_id);
alter table resc_role add constraint fk_resc_role_resc foreign key(resc_id) references resc(id);
alter table resc_role add constraint fk_resc_role_role foreign key(role_id) references role(id);

-- 用户角色连接表
create table user_role(
    user_id bigint,
    role_id bigint
);
alter table user_role add constraint pk_user_role primary key(user_id, role_id);
alter table user_role add constraint fk_user_role_user foreign key(user_id) references user(id);
alter table user_role add constraint fk_user_role_role foreign key(role_id) references role(id);

ER图如下表示

图 5.1. 数据库表关系

我们在已有表结构中插入一些数据。

insert into user(id,username,password,status,descn) values(1,'admin','admin',1,'管理员');
insert into user(id,username,password,status,descn) values(2,'user','user',1,'用户');

insert into role(id,name,descn) values(1,'ROLE_ADMIN','管理员角色');
insert into role(id,name,descn) values(2,'ROLE_USER','用户角色');

insert into resc(id,name,res_type,res_string,priority,descn) values(1,'','URL','/admin.jsp',1,'');
insert into resc(id,name,res_type,res_string,priority,descn) values(2,'','URL','/**',2,'');

insert into resc_role(resc_id,role_id) values(1,1);
insert into resc_role(resc_id,role_id) values(2,1);
insert into resc_role(resc_id,role_id) values(2,2);

insert into user_role(user_id,role_id) values(1,1);
insert into user_role(user_id,role_id) values(1,2);
insert into user_role(user_id,role_id) values(2,2);

Spring Security没有提供从数据库获得获取资源信息的方法，实际上Spring Security甚至没有为我们留一个半个的扩展接口，所以我们这次要费点儿脑筋了。

首先，要搞清楚需要提供何种类型的数据，然后，寻找可以让我们编写的代码替换原有功能的切入点，实现了以上两步之后，就可以宣布大功告成了。

1.

从配置文件上可以看到，Spring Security所需的数据应该是一系列URL网址和访问这些网址所需的权限：

<intercept-url pattern="/login.jsp" access="IS_AUTHENTICATED_ANONYMOUSLY" />
<intercept-url pattern="/admin.jsp" access="ROLE_ADMIN" />
<intercept-url pattern="/**" access="ROLE_USER" />

select re.res_string,r.name
    from role r
    join resc_role rr
      on r.id=rr.role_id
    join resc re
      on re.id=rr.resc_id
order by re.priority

完整代码

package com.family168.springsecuritybook.ch005;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.FactoryBean;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;

import org.springframework.security.access.ConfigAttribute;
import org.springframework.security.access.ConfigAttributeEditor;
import org.springframework.security.web.access.intercept.DefaultFilterInvocationSecurityMetadataSource;
import org.springframework.security.web.access.intercept.FilterInvocationSecurityMetadataSource;
import org.springframework.security.web.access.intercept.RequestKey;
import org.springframework.security.web.util.AntPathRequestMatcher;
import org.springframework.security.web.util.RequestMatcher;


public class JdbcFilterInvocationDefinitionSourceFactoryBean
    extends JdbcDaoSupport implements FactoryBean {
    private String resourceQuery;

    public boolean isSingleton() {
        return true;
    }

    public Class getObjectType() {
        return FilterInvocationSecurityMetadataSource.class;
    }

    public Object getObject() {
        return new DefaultFilterInvocationSecurityMetadataSource(this
            .buildRequestMap());
    }

    protected Map<String, String> findResources() {
        ResourceMapping resourceMapping = new ResourceMapping(getDataSource(),
                resourceQuery);

        Map<String, String> resourceMap = new LinkedHashMap<String, String>();

        for (Resource resource : (List<Resource>) resourceMapping.execute()) {
            String url = resource.getUrl();
            String role = resource.getRole();

            if (resourceMap.containsKey(url)) {
                String value = resourceMap.get(url);
                resourceMap.put(url, value + "," + role);
            } else {
                resourceMap.put(url, role);
            }
        }

        return resourceMap;
    }

    protected LinkedHashMap<RequestMatcher, Collection<ConfigAttribute>> buildRequestMap() {
        LinkedHashMap<RequestMatcher, Collection<ConfigAttribute>> requestMap =
            null;
        requestMap = new LinkedHashMap<RequestMatcher, Collection<ConfigAttribute>>();

        ConfigAttributeEditor editor = new ConfigAttributeEditor();

        Map<String, String> resourceMap = this.findResources();

        for (Map.Entry<String, String> entry : resourceMap.entrySet()) {
            String key = entry.getKey();
            editor.setAsText(entry.getValue());
            requestMap.put(new AntPathRequestMatcher(key),
                (Collection<ConfigAttribute>) editor.getValue());
        }

        return requestMap;
    }

    public void setResourceQuery(String resourceQuery) {
        this.resourceQuery = resourceQuery;
    }

    private class Resource {
        private String url;
        private String role;

        public Resource(String url, String role) {
            this.url = url;
            this.role = role;
        }

        public String getUrl() {
            return url;
        }

        public String getRole() {
            return role;
        }
    }

    private class ResourceMapping extends MappingSqlQuery {
        protected ResourceMapping(DataSource dataSource,
            String resourceQuery) {
            super(dataSource, resourceQuery);
            compile();
        }

        protected Object mapRow(ResultSet rs, int rownum)
            throws SQLException {
            String url = rs.getString(1);
            String role = rs.getString(2);
            Resource resource = new Resource(url, role);

            return resource;
        }
    }
}

完整的配置文件为

<?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">
        <custom-filter ref="filterSecurityInterceptor" before="FILTER_SECURITY_INTERCEPTOR" />
    </http>

    <authentication-manager>
        <authentication-provider>
            <jdbc-user-service data-source-ref="dataSource"
                users-by-username-query="select username,password,status as enabled
                                           from user
                                          where username=?"
                authorities-by-username-query="select u.username,r.name as authority
                                                 from user u
                                                 join user_role ur
                                                   on u.id=ur.user_id
                                                 join role r
                                                   on r.id=ur.role_id
                                                where u.username=?"/>
        </authentication-provider>
    </authentication-manager>

    <beans:bean id="filterSecurityInterceptor"
        class="org.springframework.security.web.access.intercept.FilterSecurityInterceptor" autowire="byType">
        <beans:property name="securityMetadataSource" ref="filterInvocationSecurityMetadataSource" />
        <beans:property name="authenticationManager" ref="org.springframework.security.authenticationManager"/>
    </beans:bean>

    <beans:bean id="filterInvocationSecurityMetadataSource"
        class="com.family168.springsecuritybook.ch005.JdbcFilterInvocationDefinitionSourceFactoryBean">
        <beans:property name="dataSource" ref="dataSource"/>
        <beans:property name="resourceQuery" value="
            select re.res_string,r.name
              from role r
              join resc_role rr
                on r.id=rr.role_id
              join resc re
                on re.id=rr.resc_id
          order by re.priority
        "/>
    </beans:bean>

    <beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <beans:property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
        <beans:property name="url" value="jdbc:hsqldb:res:/hsqldb/test"/>
        <beans:property name="username" value="sa"/>
        <beans:property name="password" value=""/>
    </beans:bean>
</beans:beans>

目前存在的问题是，系统会在初始化时一次将所有资源加载到内存中，即使在数据库中修改了资源信息，系统也不会再次去从数据库中读取资源信息。这就造成了每次修改完数据库后，都需要重启系统才能时资源配置生效。

解决方案是，如果数据库中的资源出现的变化，需要刷新内存中已加载的资源信息时，使用下面代码：

<%
    ApplicationContext ctx =  WebApplicationContextUtils.getWebApplicationContext(application);
    FactoryBean factoryBean = (FactoryBean) ctx.getBean("&filterInvocationSecurityMetadataSource");
    FilterInvocationSecurityMetadataSource fids = (FilterInvocationSecurityMetadataSource) factoryBean.getObject();
    FilterSecurityInterceptor filter = (FilterSecurityInterceptor) ctx.getBean("filterSecurityInterceptor");
    filter.setSecurityMetadataSource(fids);
%>