- 浏览: 272756 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
zhuzhuhenzhencheng:
密码是什么啊
Ext表格(Grid)上面的悬浮提示 -
鹿惊_:
确实如雪中送炭般温暖!
Ext扩展整理后吐血奉献 -
ortega1_2_3:
该版本貌似有bug,当sockIOPool的自平衡线程self ...
Java MemCached Window简单实现 -
q6952592:
好。解决了我的兼容模式下出现的问题。
Ext表格(Grid)上面的悬浮提示 -
fei33423:
请参考 fei33423的文章 java中直接调用groovy ...
Groovy应用(Java与Groovy间相互调用)
前两个有关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; } }
发表评论
-
Spring Security自定义数据表完整实现
2012-04-18 22:42 2978创建MySQL数据表的语句: SET FOREIGN_KEY ... -
Spring Security逐步深入
2012-04-01 22:48 1658上面的一个 Spring Security 的 d ... -
Spring Security再次体验
2012-03-29 23:26 4718上一个例子只是初探了一下Spring Security的用法, ... -
Spring Security初体验
2012-03-27 22:17 1445初体验,从天开始研究一下Spring Security这个权限 ...
相关推荐
Spring Security 演讲PPT(演讲嘉宾:张明星) WebSphere技术专家沙龙在广州圆满举办,WSC超级版主Fastzch(张明星)担任本次沙龙的演讲嘉宾,他给广州的WebSphere技术专家带来了以“Spring Security ”为主题的...
Spring Security三份资料,实战Spring Security 3.x.pdf;Spring Security 3.pdf;Spring Security使用手册.pdf
spring security spring security 中文文档
Spring Security in Action
springsecurity是一个功能强大且高度可定制的身份验证和访问控制框架。springsecurity是一个专注于为Java应用程序提供身份验证和授权的框架。与所有Spring项目一样,Spring安全性的真正威力在于它可以很容易地扩展以...
spring security2.5 jar 和spring security2.5 整合必须的jar包
很多独立软件供应商,因为灵活的身份验证模式二选择Spring Security。这样做允许他们快速的集成到他们的终端客户需求的解决方案而不用进行大量工程或者改变客户的环境。如果上面的验证机制不符合你的需求,Spring ...
三更springsecurity学习笔记
SpringSecurity框架的权限认证流程原理,请求到来时SpringSecurity如果调用层层过滤器来完成认证;
Spring Security:spring家族一员。是一个能够为基于Spring的企业应用系统提供声明式的安全访问控制解决方案的安全框架。它提供了一组可以在Spring应用上下文中配置的Bean,充分利用了Spring IoC,DI(控制反转...
该资源是基本Spring Security实战七篇文档中组织的源码,详情如下: ssecurity项目是Spring Security实战(一和二)的源码; ssecurity-db项目是Spring Security实战(三)的源码; ssceurity-page项目是Spring ...
1.本项目为SpringCloud Gateway的微服务框架,整合了SpringSecurity,微服务间使用Redis来获取登陆的用户信息。 2.由于Gat
Spring Security 3.pdf Spring Security 3.pdf Spring Security 3.pdf Spring Security 3.pdf
SpringSecurity学习总结源代码
Spring Security。 官网 Spring Security API(Spring Security 开发文档).CHM
SpringSecurity入门到进阶到高级,是我们老师给我们讲课用的,我们都照着配就没有问题,可以跑通,
spring security3 中文版本
Spring Security 2.0.x 参考手册 中文版
SpringSecurity课程文档下载 pdf 教学
教程视频:spring提供的安全权限框架,Spring Security、Spring Social 、Spring Security OAuth