国内对权限系统的基本要求是将用户权限和被保护资源都放在数据库里进行管理，在这点上Spring Security并没有给出官方的解决方案，为此我们需要对Spring Security进行扩展。、
数据库表结构
这次我们使用五张表，user用户表，role角色表，resc资源表相互独立，它们通过各自之间的连接表实现多对多关系。我们自己定义的表结构。
create table role(
id bigint,descn varchar(200<span style="color: #000000;">)
);
alter table role add constraint pk_role primary key(id);
alter table role alter column id bigint generated by <span style="color: #0000ff;">default as identity(start with 1<span style="color: #000000;">); --<span style="color: #000000;"> 用户
create table user(
id bigint,username varchar(50<span style="color: #000000;">),password varchar(50<span style="color: #000000;">),status integer,descn varchar(200<span style="color: #000000;">)
);
alter table user add constraint pk_user primary key(id);
alter table user alter column id bigint generated by <span style="color: #0000ff;">default as identity(start with 1<span style="color: #000000;">);
--<span style="color: #000000;"> 资源角色连接表
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);
--<span style="color: #000000;"> 用户角色连接表
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 role(id,'ROLE_USER','用户角色'<span style="color: #000000;">);
insert into resc(id,res_type,res_string,priority,'','URL','/admin.jsp',''<span style="color: #000000;">);
insert into resc(id,'/**',2,''<span style="color: #000000;">);
insert into resc_role(resc_id,role_id) values(
1,1<span style="color: #000000;">);insert into resc_role(resc_id,role_id) values(2,2<span style="color: #000000;">);
insert into user_role(user_id,1<span style="color: #000000;">);
insert into user_role(user_id,2<span style="color: #000000;">);
insert into user_role(user_id,2);
Spring Security没有提供从数据库获得获取资源信息的方法，实际上Spring Security甚至没有为我们留一个半个的扩展接口，所以我们这次要费点儿脑筋了。
首先，要搞清楚需要提供何种类型的数据，然后，寻找可以让我们编写的代码替换原有功能的切入点，实现了以上两步之后，就可以宣布大功告成了。
1.
从配置文件上可以看到，Spring Security所需的数据应该是一系列URL网址和访问这些网址所需的权限：
完整代码
<span style="color: #0000ff;">import<span style="color: #000000;"> java.sql.sqlException;
<span style="color: #0000ff;">import<span style="color: #000000;"> java.util.Collection;
<span style="color: #0000ff;">import<span style="color: #000000;"> java.util.LinkedHashMap;
<span style="color: #0000ff;">import<span style="color: #000000;"> java.util.List;
<span style="color: #0000ff;">import<span style="color: #000000;"> java.util.Map;
<span style="color: #0000ff;">import<span style="color: #000000;"> javax.sql.DataSource;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.beans.factory.factorybean;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.jdbc.core.support.JdbcDaoSupport;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.jdbc.object.MappingsqlQuery;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.access.ConfigAttribute;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.access.ConfigAttributeEditor;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.web.access.intercept.DefaultFilterInvocationSecurityMetadataSource;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.web.access.intercept.FilterInvocationSecurityMetadataSource;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.web.access.intercept.RequestKey;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.web.util.AntPathRequestMatcher;
<span style="color: #0000ff;">import<span style="color: #000000;"> org.springframework.security.web.util.RequestMatcher;
<span style="color: #0000ff;">public <span style="color: #0000ff;">class<span style="color: #000000;"> JdbcFilterInvocationDefinitionSourcefactorybean
<span style="color: #0000ff;">extends JdbcDaoSupport <span style="color: #0000ff;">implements<span style="color: #000000;"> factorybean {
<span style="color: #0000ff;">private<span style="color: #000000;"> String resourceQuery;
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">boolean</span><span style="color: #000000;"> isSingleton() {
</span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">true</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Class getObjectType() {
</span><span style="color: #0000ff;">return</span> FilterInvocationSecurity<a href="https://www.jb51.cc/tag/Meta/" target="_blank" class="keywords">Meta</a>dataSource.<span style="color: #0000ff;">class</span><span style="color: #000000;">;
}
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Object getObject() {
</span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">new</span> DefaultFilterInvocationSecurity<a href="https://www.jb51.cc/tag/Meta/" target="_blank" class="keywords">Meta</a>dataSource(<span style="color: #0000ff;">this</span><span style="color: #000000;">
.buildRequestMap());
}
</span><span style="color: #0000ff;">protected</span> Map<String,String><span style="color: #000000;"> findResources() {
ResourceMapping resourceMapping </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> ResourceMapping(getDataSource(),resourceQuery);
Map</span><String,String> resourceMap = <span style="color: #0000ff;">new</span> LinkedHashMap<String,String><span style="color: #000000;">();
</span><span style="color: #0000ff;">for</span> (Resource resource : (List<Resource><span style="color: #000000;">) resourceMapping.execute()) {
String url </span>=<span style="color: #000000;"> resource.getUrl();
String role </span>=<span style="color: #000000;"> resource.getRole();
</span><span style="color: #0000ff;">if</span><span style="color: #000000;"> (resourceMap.containsKey(url)) {
String value </span>=<span style="color: #000000;"> resourceMap.get(url);
resourceMap.put(url,value </span>+ "," +<span style="color: #000000;"> role);
} </span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
resourceMap.put(url,role);
}
}
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resourceMap;
}
</span><span style="color: #0000ff;">protected</span> LinkedHashMap<RequestMatcher,Collection<ConfigAttribute>><span style="color: #000000;"> buildRequestMap() {
LinkedHashMap</span><RequestMatcher,Collection<ConfigAttribute>> requestMap =
<span style="color: #0000ff;">null</span><span style="color: #000000;">;
requestMap </span>= <span style="color: #0000ff;">new</span> LinkedHashMap<RequestMatcher,Collection<ConfigAttribute>><span style="color: #000000;">();
ConfigAttributeEditor editor </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> ConfigAttributeEditor();
Map</span><String,String> resourceMap = <span style="color: #0000ff;">this</span><span style="color: #000000;">.findResources();
</span><span style="color: #0000ff;">for</span> (Map.Entry<String,String><span style="color: #000000;"> entry : resourceMap.entrySet()) {
String key </span>=<span style="color: #000000;"> entry.getKey();
editor.setAsText(entry.getValue());
requestMap.put(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> AntPathRequestMatcher(key),(Collection</span><ConfigAttribute><span style="color: #000000;">) editor.getValue());
}
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> requestMap;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setResourceQuery(String resourceQuery) {
</span><span style="color: #0000ff;">this</span>.resourceQuery =<span style="color: #000000;"> resourceQuery;
}
</span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> Resource {
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String url;
</span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String role;
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> Resource(String url,String role) {
</span><span style="color: #0000ff;">this</span>.url =<span style="color: #000000;"> url;
</span><span style="color: #0000ff;">this</span>.role =<span style="color: #000000;"> role;
}
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getUrl() {
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> url;
}
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getRole() {
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> role;
}
}
</span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">class</span> ResourceMapping <span style="color: #0000ff;">extends</span><span style="color: #000000;"> Mapping<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Query {
</span><span style="color: #0000ff;">protected</span><span style="color: #000000;"> ResourceMapping(DataSource dataSource,String resourceQuery) {
</span><span style="color: #0000ff;">super</span><span style="color: #000000;">(dataSource,resourceQuery);
compile();
}
</span><span style="color: #0000ff;">protected</span> Object mapRow(ResultSet rs,<span style="color: #0000ff;">int</span><span style="color: #000000;"> rownum)
</span><span style="color: #0000ff;">throws</span><span style="color: #000000;"> <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Exception {
String url </span>= rs.getString(1<span style="color: #000000;">);
String role </span>= rs.getString(2<span style="color: #000000;">);
Resource resource </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> Resource(url,role);
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resource;
}
}}
完整的配置文件为
<http auto-config="true">
<custom-filter ref="filterSecurityInterceptor" before="FILTER_SECURITY_INTERCEPTOR" />
</http>
<authentication-manager>
<authentication-provider>
<jdbc-user-service data-source-ref="dataSource"<span style="color: #000000;">
users</span>-by-username-query="select username,status as enabled<span style="color: #000000;"> from user
where username
authorities-by-username-query="select u.username,r.name as authority
<span style="color: #000000;"> from user u
join user_role ur
on u.id=<span style="color: #000000;">ur.user_id
join role r
on r.id=<span style="color: #000000;">ur.role_id
where u.username=?"/>
<beans:bean id="filterSecurityInterceptor"
<span style="color: #0000ff;">class</span>="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="filterInvocationSecurity<a href="https://www.jb51.cc/tag/Meta/" target="_blank" class="keywords">Meta</a>dataSource"
<span style="color: #0000ff;">class</span>="com.family168.springsecuritybook.ch005.JdbcFilterInvocationDefinitionSource<a href="https://www.jb51.cc/tag/factorybean/" target="_blank" class="keywords">factorybean</a>">
<beans:property name="dataSource" ref="dataSource"/>
<beans:property name="resourceQuery" value=<span style="color: #000000;">"from role r
join resc_role rr
on r.id=<span style="color: #000000;">rr.role_id
join resc re
on re.id=<span style="color: #000000;">rr.resc_id
order by re.priority
"/>
<beans:bean id="dataSource" <span style="color: #0000ff;">class</span>="org.springframework.jdbc.datasource.DriverManagerDataSource">
<beans:property name="driverClassName" value="org.h<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>db.jdbcDriver"/>
<beans:property name="url" value="jdbc:h<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>db:res:/h<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>db/test"/>
<beans:property name="username" value="sa"/>
<beans:property name="password" value=""/>
</beans:bean>目前存在的问题是，系统会在初始化时一次将所有资源加载到内存中，即使在数据库中修改了资源信息，系统也不会再次去从数据库中读取资源信息。这就造成了每次修改完数据库后，都需要重启系统才能时资源配置生效。
解决方案是，如果数据库中的资源出现的变化，需要刷新内存中已加载的资源信息时，使用下面代码：
