Spring security 从数据库查询权限验证

1. 定义相关表

-- 用户表, username and password 为验证条件
create table users(
    username varchar(50) not null primary key,
    password varchar(50) not null,
    enabled int not null
);
-- 权限验证表
create table authorities (
    username varchar(50) not null,
    authority varchar(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
--  创建索引
create unique index ix_auth_username on authorities (username,authority);
-- 插入测试数据
insert into users(username,password,enabled) values('admin','admin',true);
insert into users(username,password,enabled) values('user','user',true);
insert into users(username,password,enabled) values('scott','user',false);

insert into authorities(username,authority) values('admin','ROLE_ADMIN');
insert into authorities(username,authority) values('admin','ROLE_USER');
insert into authorities(username,authority) values('user','ROLE_USER');
-- 查询
select * from users

 2. 配置 security 配置文件

<?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-3.0.xsd
                        http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.1.xsd">
	<!-- http请求映射配置 -->
	<http auto-config="true">
		<intercept-url pattern="/admin.jsp" access="ROLE_ADMIN"/>
		<intercept-url pattern="/user.jsp" access="ROLE_USER"/>
	</http>
	
	<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:///privilege"/>
		<beans:property name="username" value="root"/>
		<beans:property name="password" value="admin"/>
	</beans:bean>
	
	<!-- 权限认证管理 -->
    <authentication-manager>
        <authentication-provider>
        	<!-- 权限从数据库中查询出来，需要数据源 -->
        	<jdbc-user-service data-source-ref="dataSource"/>
        </authentication-provider>
    </authentication-manager>
</beans:beans>