一、前言
数据库认证（Database-provided authentication）：应用通过数据库获取用户认证信息。
认证提供者（Authentication Provider）：Spring提供了一系列认证方式，如LDAP、OpenID等，对应的认证服务都需要实现AuthenticationProvider接口，开发人员可以自定义AuthenticationProvider实现。
二、Spring配置
数据库认证本质上与其它认证方式没有区别，区别就是获取用户及其权限信息的方式不同，数据库认证需要从数据库读取用户信息，Spring已经预定义了用户安全的相关表结构和操作实现，请参考下面两个类的源码，这些默认实现能满足大部分应用需求，当然也很容易自己定制。
org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl
org.springframework.security.provisioning.JdbcUserDetailsManager
对于获取用户、用户组、权限的查询都提供了默认的SQL，所以这也不需要开发者定义，但需要了解Spring的默认实现，，如：
public class JdbcDaoImpl extends JdbcDaoSupport implements UserDetailsService {//~ Static fields/initializers =====================================================================================public static final String DEF_USERS_BY_USERNAME_QUERY =”select username,password,enabled ” +”from users ” +”where username = ?”;public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY =”select username,authority ” +”from authorities ” +”where username = ?”;public static final String DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY =”select g.id, g.group_name, ga.authority ” +”from groups g, group_members gm, group_authorities ga ” +”where gm.username = ? ” +”and g.id = ga.group_id ” +”and g.id = gm.group_id”;
我们可以看到Spring尽一切可能减少开发者的工作，但如果需求不能满足，开发者需要自定义表结构并覆盖查询SQL定义。
public class JdbcUserDetailsManager extends JdbcDaoImpl implements UserDetailsManager, GroupManager {//~ Static fields/initializers =====================================================================================// UserDetailsManager SQLpublic static final String DEF_CREATE_USER_SQL =”insert into users (username, password, enabled) values (?,?,?)”;public static final String DEF_DELETE_USER_SQL =”delete from users where username = ?”;public static final String DEF_UPDATE_USER_SQL =”update users set password = ?, enabled = ? where username = ?”;public static final String DEF_INSERT_AUTHORITY_SQL =”insert into authorities (username, authority) values (?,?)”;public static final String DEF_DELETE_USER_AUTHORITIES_SQL =”delete from authorities where username = ?”;public static final String DEF_USER_EXISTS_SQL =”select username from users where username = ?”;public static final String DEF_CHANGE_PASSWORD_SQL =”update users set password = ? where username = ?”;// GroupManager SQLpublic static final String DEF_FIND_GROUPS_SQL =”select group_name from groups”;public static final String DEF_FIND_USERS_IN_GROUP_SQL =”select username from group_members gm, groups g ” +”where gm.group_id = g.id” +” and g.group_name = ?”;public static final String DEF_INSERT_GROUP_SQL =”insert into groups (group_name) values (?)”;public static final String DEF_FIND_GROUP_ID_SQL =”select id from groups where group_name = ?”;public static final String DEF_INSERT_GROUP_AUTHORITY_SQL =”insert into group_authorities (group_id, authority) values (?,?)”;public static final String DEF_DELETE_GROUP_SQL =”delete from groups where id = ?”;public static final String DEF_DELETE_GROUP_AUTHORITIES_SQL =”delete from group_authorities where group_id = ?”;public static final String DEF_DELETE_GROUP_MEMBERS_SQL =”delete from group_members where group_id = ?”;public static final String DEF_RENAME_GROUP_SQL =”update groups set group_name = ? where group_name = ?”;public static final String DEF_INSERT_GROUP_MEMBER_SQL =”insert into group_members (group_id, username) values (?,?)”;public static final String DEF_DELETE_GROUP_MEMBER_SQL =”delete from group_members where group_id = ? and username = ?”;public static final String DEF_GROUP_AUTHORITIES_QUERY_SQL =”select g.id, g.group_name, ga.authority ” +”from groups g, group_authorities ga ” +”where g.group_name = ? ” +”and g.id = ga.group_id “;public static final String DEF_DELETE_GROUP_AUTHORITY_SQL =”delete from group_authorities where group_id = ? and authority = ?”;
数据库安全认证配置示例：
<?xml version=”1.0″ encoding=”UTF-8″?><beans xmlns=”http://www.springframework.org/schema/beans”xmlns:security=”http://www.springframework.org/schema/security”xmlns:jdbc=”http://www.springframework.org/schema/jdbc” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”xmlns:context=”http://www.springframework.org/schema/context”xsi:schemaLocation=”http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/security/spring-security-3.2.xsd http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context/spring-context.xsd”><context:component-scan base-package=”com.stevex.demo” /><security:http auto-config=”true”><security:intercept-url pattern=”/admin” access=”ROLE_ADMIN” /><security:intercept-url pattern=”/*” access=”ROLE_USER” /><security:form-login /></security:http><security:authentication-manager><security:authentication-provider><security:jdbc-user-servicedata-source-ref=”dataSource”group-authorities-by-username-query=”select g.id, g.group_name, ga.authorityfrom groups g, group_members gm, group_authorities gawhere gm.username = ? and g.id = ga.group_id and g.id = gm.group_id” /></security:authentication-provider></security:authentication-manager><jdbc:embedded-database><jdbc:script location=”classpath:security-schema.sql” /><jdbc:script location=”classpath:users.sql” /></jdbc:embedded-database></beans>
三、后语
本文提供一个基本实现参考供下载。
本文出自 “力量来源于赤诚的爱！” 博客，请务必保留此出处
先知三日，富贵十年。
