前两个有关Spring Security的小demo实现都是通过applicationContext.xml配置文件进行权限控制的。没有使用数据库，在真正的项目用户名密码全是存在数据库里面的，今天使Spring Security通过连接数据库达到权限控制的目的。但是如果你没有通过网上的资料和例子，实现权限控制的数据表该怎么创建的，该有哪些字段呢？或许你查过来网上的很多资料知道数据库中表和字段该如何创建。现在作为一个未知者来看看为什么网上例子数据表的结构是那么定义的。怎么才能知道的，作为程序开发的第一反应就是看源码，但是处于刚刚入门级别该怎么看源代码呢。其实除了看源码还有其他方式，现在就来看看吧。
实现与数据库连接那么首先就需要创建数据源，创建数据源配置文件applicationContext-dao.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="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-2.0.xsd"> <!-- 建立数据源,使用c3p0连接池 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass"> <value>com.mysql.jdbc.Driver</value> </property> <property name="jdbcUrl"> <value>jdbc:mysql://localhost:3306/s2</value> </property> <property name="user"> <value>root</value> </property> <property name="password"> <value>root</value> </property> <property name="initialPoolSize"> <value>10</value> </property> <property name="minPoolSize"> <value>5</value> </property> <property name="maxPoolSize"> <value>30</value> </property> <property name="acquireIncrement"> <value>5</value> </property> <property name="maxIdleTime"> <value>10</value> </property> <property name="maxStatements"> <value>0</value> </property> </bean> </beans>
通过C3P0连接池创建数据连接，数据源的名字dataSource.
修改配置文件 applicationContext.xml
<?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.0.xsd">
<!-- 指定被拒绝的页面 -->
<http auto-config='true' access-denied-page="/accessDenied.jsp">
<!--
login-page表示用户登陆时显示我们自定义的login.jsp
authentication-failure-url表示用户登陆失败时，跳转到哪个页面，并添加一个error=true参数作为登陆失败的标示
default-target-url表示登陆成功时，跳转到哪个页面
-->
<form-login login-page="/login.jsp"
authentication-failure-url="/login.jsp?error=true"
default-target-url="/index.jsp"
/>
<!--登录页面不进行过滤，后面加一个*那是因为请求页面后面会带参数-->
<intercept-url pattern="/login.jsp*" filters="none"/>
<intercept-url pattern="/admin.jsp" access="ROLE_ADMIN" />
<intercept-url pattern="/**" access="ROLE_USER" />
<!-- 检测失效的sessionId,超时时定位到另外一个URL -->
<session-management invalid-session-url="/sessionTimeout.jsp" >
<!--
防止第二次登录
如果想让第一次登录失效第二次登录启用则不要配置error-if-maximum-exceeded="true"
-->
<concurrency-control max-sessions="1" error-if-maximum-exceeded="true"/>
</session-management>
</http>
<authentication-manager>
<authentication-provider>
<!-- 加密用户的密码 -->
<password-encoder hash="md5"/>
<jdbc-user-service data-source-ref="dataSource"/>
</authentication-provider>
</authentication-manager>
<!-- 国际化 -->
<beans:bean id="messageSource" class="org.springframework.context.support.ReloadableResourceBundleMessageSource">
<!-- 如果不加载自己的国际化文件，去加载 Security 内部的国际化文件classpath:org/springframework/security/messages_zh_CN -->
<beans:property name="basename" value="classpath:messages_zh_CN"/>
</beans:bean>
</beans:beans>
与之前不同的是
<jdbc-user-service data-source-ref="dataSource"/>
启动服务运行程序，进行登录会在页面上打印出如下信息：
PreparedStatementCallback; bad SQL grammar [select username,password,enabled from users where username = ?]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 's2.users' doesn't exist
翻译一下上面的错误信息：登录执行查询的时候去查询 users 表，但是库中没有这个表，从上面的查询语句可以看出 users 表的结构（三个字段username，password，enabled ）；按照上面的结构创建数据表 users，为表中添加数据（admin,21232f297a57a5a743894a0e4a801fc3,1;user,ee11cbb19052e40b07aac0ca060c23ee,1）继续运行程序。输入正确的用户名密码进行登录，页面上再次打印出信息：
PreparedStatementCallback; bad SQL grammar [select username,authority from authorities where username = ?]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 's2.authorities' doesn't exist
意思大概是根据这个用户名去查询权限，但是数据库中不存在这个名为authorities的权限表，从错误的信息中可以看出authorities表中存在两个字段（username，authority），按照要求再去创建authorities数据表，并未表中添加数据（admin,ROLE_ADMIN;admin,ROLE_USER;user,ROLE_USER）。再次运行程序，成功了！所以不难看出在Spring Security源码中导数据库中查询用户名密码，及其用户对应的权限是其固定写在代码里面的，这就是为什么网上的一些小的demo中数据库为什么这么创建的原因。下面给出MySQL创建这两张的SQL语句及其插入数据的相关语句：
create table users(
username varchar(50) not null primary key,
password varchar(50) not null,
enabled boolean 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 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');
另外说明：
一定要让程序启动的时候加载创建的applicationContext-dao.xml文件，所以注意一下web.xml中加载Spring配置文件的方法：
<context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:applicationContext*.xml</param-value> </context-param>
当然如果你一定想要知道为什么要这么定义数据库结构才不会报错那么最直观的就是查看源代码，请下载Spring Security的源码找到 org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl 你便会一目了然。
/* Copyright 2004, 2005, 2006 Acegi Technology Pty Limited
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.security.core.userdetails.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.springframework.context.ApplicationContextException;
import org.springframework.context.support.MessageSourceAccessor;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.SpringSecurityMessageSource;
import org.springframework.security.core.authority.AuthorityUtils;
import org.springframework.security.core.authority.GrantedAuthorityImpl;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.util.Assert;
/**
* <tt>UserDetailsServiceRetrieves</tt> implementation which retrieves the user details
* (username, password, enabled flag, and authorities) from a database using JDBC queries.
*
* <h3>Default Schema</h3>
* A default database schema is assumed, with two tables "users" and "authorities".
*
* <h4>The Users table</h4>
*
* This table contains the login name, password and enabled status of the user.
*
* <table>
* <tr><th>Column</th></tr>
* <tr><td>username</td></tr>
* <tr><td>password</td></tr>
* <tr><td>enabled</td></tr>
* </table>
*
* <h4>The Authorities Table</h4>
*
* <table>
* <tr><th>Column</th></tr>
* <tr><td>username</td></tr>
* <tr><td>authority</td></tr>
* </table>
*
* If you are using an existing schema you will have to set the queries <tt>usersByUsernameQuery</tt> and
* <tt>authoritiesByUsernameQuery</tt> to match your database setup
* (see {@link #DEF_USERS_BY_USERNAME_QUERY} and {@link #DEF_AUTHORITIES_BY_USERNAME_QUERY}).
*
* <p>
* In order to minimise backward compatibility issues, this implementation doesn't recognise the expiration of user
* accounts or the expiration of user credentials. However, it does recognise and honour the user enabled/disabled
* column. This should map to a <tt>boolean</tt> type in the result set (the SQL type will depend on the
* database you are using). All the other columns map to <tt>String</tt>s.
*
* <h3>Group Support</h3>
* Support for group-based authorities can be enabled by setting the <tt>enableGroups</tt> property to <tt>true</tt>
* (you may also then wish to set <tt>enableAuthorities</tt> to <tt>false</tt> to disable loading of authorities
* directly). With this approach, authorities are allocated to groups and a user's authorities are determined based
* on the groups they are a member of. The net result is the same (a UserDetails containing a set of
* <tt>GrantedAuthority</tt>s is loaded), but the different persistence strategy may be more suitable for the
* administration of some applications.
* <p>
* When groups are being used, the tables "groups", "group_members" and "group_authorities" are used. See
* {@link #DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY} for the default query which is used to load the group authorities.
* Again you can customize this by setting the <tt>groupAuthoritiesByUsernameQuery</tt> property, but the format of
* the rows returned should match the default.
*
* @author Ben Alex
* @author colin sampaleanu
* @author Luke Taylor
*/
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";
//~ Instance fields ================================================================================================
protected MessageSourceAccessor messages = SpringSecurityMessageSource.getAccessor();
private String authoritiesByUsernameQuery;
private String groupAuthoritiesByUsernameQuery;
private String usersByUsernameQuery;
private String rolePrefix = "";
private boolean usernameBasedPrimaryKey = true;
private boolean enableAuthorities = true;
private boolean enableGroups;
//~ Constructors ===================================================================================================
public JdbcDaoImpl() {
usersByUsernameQuery = DEF_USERS_BY_USERNAME_QUERY;
authoritiesByUsernameQuery = DEF_AUTHORITIES_BY_USERNAME_QUERY;
groupAuthoritiesByUsernameQuery = DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY;
}
//~ Methods ========================================================================================================
/**
* Allows subclasses to add their own granted authorities to the list to be returned in the <tt>UserDetails</tt>.
*
* @param username the username, for use by finder methods
* @param authorities the current granted authorities, as populated from the <code>authoritiesByUsername</code>
* mapping
*/
protected void addCustomAuthorities(String username, List<GrantedAuthority> authorities) {}
public String getUsersByUsernameQuery() {
return usersByUsernameQuery;
}
protected void initDao() throws ApplicationContextException {
Assert.isTrue(enableAuthorities || enableGroups, "Use of either authorities or groups must be enabled");
}
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException, DataAccessException {
List<UserDetails> users = loadUsersByUsername(username);
if (users.size() == 0) {
throw new UsernameNotFoundException(
messages.getMessage("JdbcDaoImpl.notFound", new Object[]{username}, "Username {0} not found"), username);
}
UserDetails user = users.get(0); // contains no GrantedAuthority[]
Set<GrantedAuthority> dbAuthsSet = new HashSet<GrantedAuthority>();
if (enableAuthorities) {
dbAuthsSet.addAll(loadUserAuthorities(user.getUsername()));
}
if (enableGroups) {
dbAuthsSet.addAll(loadGroupAuthorities(user.getUsername()));
}
List<GrantedAuthority> dbAuths = new ArrayList<GrantedAuthority>(dbAuthsSet);
addCustomAuthorities(user.getUsername(), dbAuths);
if (dbAuths.size() == 0) {
throw new UsernameNotFoundException(
messages.getMessage("JdbcDaoImpl.noAuthority",
new Object[] {username}, "User {0} has no GrantedAuthority"), username);
}
return createUserDetails(username, user, dbAuths);
}
/**
* Executes the SQL <tt>usersByUsernameQuery</tt> and returns a list of UserDetails objects.
* There should normally only be one matching user.
*/
protected List<UserDetails> loadUsersByUsername(String username) {
return getJdbcTemplate().query(usersByUsernameQuery, new String[] {username}, new RowMapper<UserDetails>() {
public UserDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
String username = rs.getString(1);
String password = rs.getString(2);
boolean enabled = rs.getBoolean(3);
return new User(username, password, enabled, true, true, true, AuthorityUtils.NO_AUTHORITIES);
}
});
}
/**
* Loads authorities by executing the SQL from <tt>authoritiesByUsernameQuery</tt>.
*
* @return a list of GrantedAuthority objects for the user
*/
protected List<GrantedAuthority> loadUserAuthorities(String username) {
return getJdbcTemplate().query(authoritiesByUsernameQuery, new String[] {username}, new RowMapper<GrantedAuthority>() {
public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
String roleName = rolePrefix + rs.getString(2);
GrantedAuthorityImpl authority = new GrantedAuthorityImpl(roleName);
return authority;
}
});
}
/**
* Loads authorities by executing the SQL from <tt>groupAuthoritiesByUsernameQuery</tt>.
*
* @return a list of GrantedAuthority objects for the user
*/
protected List<GrantedAuthority> loadGroupAuthorities(String username) {
return getJdbcTemplate().query(groupAuthoritiesByUsernameQuery, new String[] {username}, new RowMapper<GrantedAuthority>() {
public GrantedAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
String roleName = getRolePrefix() + rs.getString(3);
GrantedAuthorityImpl authority = new GrantedAuthorityImpl(roleName);
return authority;
}
});
}
/**
* Can be overridden to customize the creation of the final UserDetailsObject which is
* returned by the <tt>loadUserByUsername</tt> method.
*
* @param username the name originally passed to loadUserByUsername
* @param userFromUserQuery the object returned from the execution of the
* @param combinedAuthorities the combined array of authorities from all the authority loading queries.
* @return the final UserDetails which should be used in the system.
*/
protected UserDetails createUserDetails(String username, UserDetails userFromUserQuery,
List<GrantedAuthority> combinedAuthorities) {
String returnUsername = userFromUserQuery.getUsername();
if (!usernameBasedPrimaryKey) {
returnUsername = username;
}
return new User(returnUsername, userFromUserQuery.getPassword(), userFromUserQuery.isEnabled(),
true, true, true, combinedAuthorities);
}
/**
* Allows the default query string used to retrieve authorities based on username to be overridden, if
* default table or column names need to be changed. The default query is {@link
* #DEF_AUTHORITIES_BY_USERNAME_QUERY}; when modifying this query, ensure that all returned columns are mapped
* back to the same column names as in the default query.
*
* @param queryString The SQL query string to set
*/
public void setAuthoritiesByUsernameQuery(String queryString) {
authoritiesByUsernameQuery = queryString;
}
protected String getAuthoritiesByUsernameQuery() {
return authoritiesByUsernameQuery;
}
/**
* Allows the default query string used to retrieve group authorities based on username to be overridden, if
* default table or column names need to be changed. The default query is {@link
* #DEF_GROUP_AUTHORITIES_BY_USERNAME_QUERY}; when modifying this query, ensure that all returned columns are mapped
* back to the same column names as in the default query.
*
* @param queryString The SQL query string to set
*/
public void setGroupAuthoritiesByUsernameQuery(String queryString) {
groupAuthoritiesByUsernameQuery = queryString;
}
/**
* Allows a default role prefix to be specified. If this is set to a non-empty value, then it is
* automatically prepended to any roles read in from the db. This may for example be used to add the
* <tt>ROLE_</tt> prefix expected to exist in role names (by default) by some other Spring Security
* classes, in the case that the prefix is not already present in the db.
*
* @param rolePrefix the new prefix
*/
public void setRolePrefix(String rolePrefix) {
this.rolePrefix = rolePrefix;
}
protected String getRolePrefix() {
return rolePrefix;
}
/**
* If <code>true</code> (the default), indicates the {@link #getUsersByUsernameQuery()} returns a username
* in response to a query. If <code>false</code>, indicates that a primary key is used instead. If set to
* <code>true</code>, the class will use the database-derived username in the returned <code>UserDetails</code>.
* If <code>false</code>, the class will use the {@link #loadUserByUsername(String)} derived username in the
* returned <code>UserDetails</code>.
*
* @param usernameBasedPrimaryKey <code>true</code> if the mapping queries return the username <code>String</code>,
* or <code>false</code> if the mapping returns a database primary key.
*/
public void setUsernameBasedPrimaryKey(boolean usernameBasedPrimaryKey) {
this.usernameBasedPrimaryKey = usernameBasedPrimaryKey;
}
protected boolean isUsernameBasedPrimaryKey() {
return usernameBasedPrimaryKey;
}
/**
* Allows the default query string used to retrieve users based on username to be overridden, if default
* table or column names need to be changed. The default query is {@link #DEF_USERS_BY_USERNAME_QUERY}; when
* modifying this query, ensure that all returned columns are mapped back to the same column names as in the
* default query. If the 'enabled' column does not exist in the source database, a permanent true value for this
* column may be returned by using a query similar to
* <pre>
* "select username,password,'true' as enabled from users where username = ?"
* </pre>
*
* @param usersByUsernameQueryString The query string to set
*/
public void setUsersByUsernameQuery(String usersByUsernameQueryString) {
this.usersByUsernameQuery = usersByUsernameQueryString;
}
protected boolean getEnableAuthorities() {
return enableAuthorities;
}
/**
* Enables loading of authorities (roles) from the authorities table. Defaults to true
*/
public void setEnableAuthorities(boolean enableAuthorities) {
this.enableAuthorities = enableAuthorities;
}
protected boolean getEnableGroups() {
return enableGroups;
}
/**
* Enables support for group authorities. Defaults to false
* @param enableGroups
*/
public void setEnableGroups(boolean enableGroups) {
this.enableGroups = enableGroups;
}
}
扫一扫
8293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
