Spring Security 學習之數據庫認證

一、前言
數據庫認證（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 SQL
public 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 SQL
public 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/jdbc/spring-jdbc-3.2.xsd
http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.2.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context 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-service
data-source-ref="dataSource"
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" />
</security:authentication-provider>
</security:authentication-manager>
<jdbc:embedded-database id="dataSource">
<jdbc:script location="classpath:security-schema.sql" />
<jdbc:script location="classpath:users.sql" />
</jdbc:embedded-database>
</beans>

embedded-database標簽：Spring默認使用hsql，如果使用其他內存數據庫，如Derby，需要指定。

jdbc-user-service標簽：注入基於JDBC的UserDetailsService實現，默認即JdbcUserDetailsManager。

group-authorities-by-username-query標簽：覆蓋默認用戶組權限查詢SQL。

authentication-provider標簽：如果沒有設置ref屬性引用其他bean時，默認使用DaoAuthenticationProvider， DaoAuthenticationProvider會調用UserDetailsService接口獲取用戶信息，並在登錄時驗證用戶密碼。

三、後語
本文提供一個基本實現參考供下載。

Spring Security 學習之數據庫認證相關文件下載：

免費下載地址在 http://linux.linuxidc.com/

用戶名與密碼都是www.linuxidc.com

具體下載目錄在 /2014年資料/2月/28日/Spring Security 學習之數據庫認證

下載方法見 http://www.linuxidc.com/Linux/2013-07/87684.htm