spring security使用数据库资源

发布时间：2020-12-15 01:50:53 所属栏目：大数据来源：网络整理

导读：国内对权限系统的基本要求是将用户权限和被保护资源都放在数据库里进行管理，在这点上Spring Security并没有给出官方的解决方案，为此我们需要对Spring Security进行扩展。、这次我们使用五张表，user用户表，role角色表，resc资源表相互独立，它们通过各自

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

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

--5050200200 as identity(start with 1-- 角色
create table role(
id bigint,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',1,'管理员'2,'user','用户'insert into role(id,name,descn) values(1,'ROLE_ADMIN','管理员角色');
insert into role(id,'ROLE_USER','用户角色');

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

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

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

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

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

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

完整代码

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;

</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;boolean</span><span style="color: #000000;"&gt; isSingleton() {
    </span><span style="color: #0000ff;"&gt;return</span> <span style="color: #0000ff;"&gt;true</span><span style="color: #000000;"&gt;;
}

</span><span style="color: #0000ff;"&gt;public</span><span style="color: #000000;"&gt; Class getObjectType() {
    </span><span style="color: #0000ff;"&gt;return</span> FilterInvocationSecurityMetadataSource.<span style="color: #0000ff;"&gt;class</span><span style="color: #000000;"&gt;;
}

</span><span style="color: #0000ff;"&gt;public</span><span style="color: #000000;"&gt; Object getObject() {
    </span><span style="color: #0000ff;"&gt;return</span> <span style="color: #0000ff;"&gt;new</span> DefaultFilterInvocationSecurityMetadataSource(<span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;
        .buildRequestMap());
}

</span><span style="color: #0000ff;"&gt;protected</span> Map<String,String><span style="color: #000000;"&gt; findResources() {
    ResourceMapping resourceMapping </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; ResourceMapping(getDataSource(),resourceQuery);

    Map</span><String,String> resourceMap = <span style="color: #0000ff;"&gt;new</span> LinkedHashMap<String,String><span style="color: #000000;"&gt;();

    </span><span style="color: #0000ff;"&gt;for</span> (Resource resource : (List<Resource><span style="color: #000000;"&gt;) resourceMapping.execute()) {
        String url </span>=<span style="color: #000000;"&gt; resource.getUrl();
        String role </span>=<span style="color: #000000;"&gt; resource.getRole();

        </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (resourceMap.containsKey(url)) {
            String value </span>=<span style="color: #000000;"&gt; resourceMap.get(url);
            resourceMap.put(url,value </span>+ "," +<span style="color: #000000;"&gt; role);
        } </span><span style="color: #0000ff;"&gt;else</span><span style="color: #000000;"&gt; {
            resourceMap.put(url,role);
        }
    }

    </span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; resourceMap;
}

</span><span style="color: #0000ff;"&gt;protected</span> LinkedHashMap<RequestMatcher,Collection<ConfigAttribute>><span style="color: #000000;"&gt; buildRequestMap() {
    LinkedHashMap</span><RequestMatcher,Collection<ConfigAttribute>> requestMap =
        <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
    requestMap </span>= <span style="color: #0000ff;"&gt;new</span> LinkedHashMap<RequestMatcher,Collection<ConfigAttribute>><span style="color: #000000;"&gt;();

    ConfigAttributeEditor editor </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; ConfigAttributeEditor();

    Map</span><String,String> resourceMap = <span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.findResources();

    </span><span style="color: #0000ff;"&gt;for</span> (Map.Entry<String,String><span style="color: #000000;"&gt; entry : resourceMap.entrySet()) {
        String key </span>=<span style="color: #000000;"&gt; entry.getKey();
        editor.setAsText(entry.getValue());
        requestMap.put(</span><span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; AntPathRequestMatcher(key),(Collection</span><ConfigAttribute><span style="color: #000000;"&gt;) editor.getValue());
    }

    </span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; requestMap;
}

</span><span style="color: #0000ff;"&gt;public</span> <span style="color: #0000ff;"&gt;void</span><span style="color: #000000;"&gt; setResourceQuery(String resourceQuery) {
    </span><span style="color: #0000ff;"&gt;this</span>.resourceQuery =<span style="color: #000000;"&gt; resourceQuery;
}

</span><span style="color: #0000ff;"&gt;private</span> <span style="color: #0000ff;"&gt;class</span><span style="color: #000000;"&gt; Resource {
    </span><span style="color: #0000ff;"&gt;private</span><span style="color: #000000;"&gt; String url;
    </span><span style="color: #0000ff;"&gt;private</span><span style="color: #000000;"&gt; String role;

    </span><span style="color: #0000ff;"&gt;public</span><span style="color: #000000;"&gt; Resource(String url,String role) {
        </span><span style="color: #0000ff;"&gt;this</span>.url =<span style="color: #000000;"&gt; url;
        </span><span style="color: #0000ff;"&gt;this</span>.role =<span style="color: #000000;"&gt; role;
    }

    </span><span style="color: #0000ff;"&gt;public</span><span style="color: #000000;"&gt; String getUrl() {
        </span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; url;
    }

    </span><span style="color: #0000ff;"&gt;public</span><span style="color: #000000;"&gt; String getRole() {
        </span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; role;
    }
}

</span><span style="color: #0000ff;"&gt;private</span> <span style="color: #0000ff;"&gt;class</span> ResourceMapping <span style="color: #0000ff;"&gt;extends</span><span style="color: #000000;"&gt; MappingSqlQuery {
    </span><span style="color: #0000ff;"&gt;protected</span><span style="color: #000000;"&gt; ResourceMapping(DataSource dataSource,String resourceQuery) {
        </span><span style="color: #0000ff;"&gt;super</span><span style="color: #000000;"&gt;(dataSource,resourceQuery);
        compile();
    }

    </span><span style="color: #0000ff;"&gt;protected</span> Object mapRow(ResultSet rs,<span style="color: #0000ff;"&gt;int</span><span style="color: #000000;"&gt; rownum)
        </span><span style="color: #0000ff;"&gt;throws</span><span style="color: #000000;"&gt; SQLException {
        String url </span>= rs.getString(1<span style="color: #000000;"&gt;);
        String role </span>= rs.getString(2<span style="color: #000000;"&gt;);
        Resource resource </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; Resource(url,role);

        </span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; resource;
    }
}

}

完整的配置文件为

="http://www.springframework.org/schema/beans"="http://www.w3.org/2001/XMLSchema-instance"="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http:

<http auto-config="true"&gt;
    <custom-filter ref="filterSecurityInterceptor" before="FILTER_SECURITY_INTERCEPTOR" />
</http>

<authentication-manager>
    <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource"<span style="color: #000000;"&gt;
            users</span>-by-username-query="select username,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=?"/>

<beans:bean id="filterSecurityInterceptor"
    <span style="color: #0000ff;"&gt;class</span>="org.springframework.security.web.access.intercept.FilterSecurityInterceptor" autowire="byType"&gt;
    <beans:property name="securityMetadataSource" ref="filterInvocationSecurityMetadataSource" />
    <beans:property name="authenticationManager" ref="org.springframework.security.authenticationManager"/&gt;
</beans:bean>

<beans:bean id="filterInvocationSecurityMetadataSource"
    <span style="color: #0000ff;"&gt;class</span>="com.family168.springsecuritybook.ch005.JdbcFilterInvocationDefinitionSourceFactoryBean"&gt;
    <beans:property name="dataSource" ref="dataSource"/&gt;
    <beans:property name="resourceQuery" value=<span style="color: #000000;"&gt;"

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 id="dataSource" <span style="color: #0000ff;"&gt;class</span>="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
    <beans:property name="driverClassName" value="org.hsqldb.jdbcDriver"/&gt;
    <beans:property name="url" value="jdbc:hsqldb:res:/hsqldb/test"/&gt;
    <beans:property name="username" value="sa"/&gt;
    <beans:property name="password" value=""/&gt;
</beans:bean>

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

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

<%== (FactoryBean) ctx.getBean("&filterInvocationSecurityMetadataSource"== (FilterSecurityInterceptor) ctx.getBean("filterSecurityInterceptor"%>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!