spring security form based login using database not responding

[rohan chauhan]

hi all,
I'm doing spring-security form based validation using database. Its all working if i use Inmemory user n pwd.

<?xml version="1.0" encoding="UTF-8"?>

<!--
  - Sample namespace-based configuration
  -
  - $Id: applicationContext-security.xml 3019 2008-05-01 17:51:48Z luke_t $
  -->

<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-2.0.xsd
                        http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-2.0.1.xsd">


  <http access-denied-page="/accessDenied.jsp">
    <intercept-url pattern="/login.jsp" filters="none"/>  
    <intercept-url pattern="/**" access="ROLE_DEFAULT,ROLE_SUPERADMIN,ROLE_USER" />
    <form-login login-page='/login.jsp' default-target-url='/index.html' authentication-failure-url="/error.jsp"/>
	<logout logout-success-url="/logout.jsp"/>
	<!--<concurrent-session-control max-sessions="1" exception-if-maximum-exceeded="true"/>-->
  </http>
    

<!-- <authentication-provider>
        <password-encoder hash="md5"/>
        <jdbc-user-service data-source-ref="dataSource"
			    users-by-username-query="SELECT DISPLAY_NAME,PASSWORD,'true' FROM USER_ACCOUNT WHERE DISPLAY_NAME=?"/>
 </authentication-provider> -->
 
	
<authentication-provider>
<password-encoder hash="md5"/>
 <jdbc-user-service  
         data-source-ref="dataSource"  
         users-by-username-query="SELECT DISPLAY_NAME,PASSWORD,'True' FROM USER_ACCOUNT WHERE DISPLAY_NAME=?;"  
         authorities-by-username-query="select display_name,role_name from user_account u,user_role ur,role r where (r.role_id=ur.role_id) and (u.user_id=ur.user_id) AND DISPLAY_NAME=?;"/> 
		 
</authentication-provider> 

<!--	 <authentication-provider>
		<password-encoder hash="md5"/>
		<user-service>
	      <user name="Admin" password="51666c4876cfa2e88f30e11d59697413" authorities="ROLE_USER, ROLE_ADMIN" />
      	<user name="aaa" password="aaa" authorities="ROLE_USER" />
	    </user-service>
	</authentication-provider>-->

</beans:beans>

if i use last commented code then its work well. but when i use query then nothing happens. only 2 lines shown at jboss console but stil login page remains there. and not allows to me to go my actual page after right unm n pwd. and not even display below code for next any successful/wrong login entry.

14:31:18,750 INFO  [STDOUT] 14:31:18,750 INFO  [XmlBeanDefinitionReader] Loading
 XML bean definitions from class path resource [org/springframework/jdbc/support
/sql-error-codes.xml]
14:31:18,843 INFO  [STDOUT] 14:31:18,843 INFO  [SQLErrorCodesFactory] SQLErrorCo
des loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL,
Sybase]

again if i write wrong querey intensely then also no sql error shown. Whats wrong here.

[Bhukka Patel]

For your users-by-username-query and authorities-by-username-query try

    
users-by-username-query="SELECT DISPLAY_NAME AS username, PASSWORD, 'true' AS enabled FROM USER_ACCOUNT WHERE DISPLAY_NAME=?"
authorities-by-username-query="select display_name AS username, role_name AS authority from user_account u,user_role ur,role r where (r.role_id=ur.role_id) and (u.user_id=ur.user_id) AND DISPLAY_NAME=?;"

[Praveen Kondaka]

We are facing similar issue.

Is yours working? Please respond ASAP

Thanks

[Praveen Kondaka]

My applicationConext-security.xml looks like this.

    

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">

<beans>

		<!-- ****** START SPRING SECURITY(ACEGI)Configuration *******-->
		<!-- ======================== FILTER CHAIN ======================= -->		

		<bean id="dataSource" 
			class="org.springframework.jndi.JndiObjectFactoryBean">
				<property name="jndiName" ref="prop.jndi.datasource"/>
		</bean>


		<bean id="authenticationManager"
		        class="org.springframework.security.providers.ProviderManager">
			<property name="providers">
			<list>
			  <ref local="daoAuthenticationProvider"/>
			  <!-- <ref local="jdbcAuthenticationProvider"/> --> 			  
			</list>
			</property>
		</bean>
		
		  
		<bean id="dbUserService" class="org.springframework.security.userdetails.jdbc.JdbcDaoImpl">
			<property name="dataSource" ref="dataSource"/>
			<property name="usersByUsernameQuery">
				<value>
					SELECT USERID AS username, HASHPASS AS password, DECODE(active_ind,'Y','true') AS enabled
					  FROM SEC_USER
					 WHERE upper(userid)=upper(?)
				</value>
			</property>
			<property name="authoritiesByUsernameQuery">
				<value>
				SELECT DECODE(is_admin, 'Y', 'ROLE_ADMIN' ) as authority 
					FROM SEC_USER
					WHERE upper(userid)=upper(?)  					
				</value>
			</property>
		</bean>
		<bean id="daoAuthenticationProvider" class="org.springframework.security.providers.dao.DaoAuthenticationProvider">
			<property name="userDetailsService" ref="dbUserService"/>
			<property name="passwordEncoder" ref="passwordEncoder"/>
		</bean>		
		
		
		
			
		<!-- UNCOMMENT LATER - ORIGINAL LOGIN -->
		<!--   
		<bean id="daoAuthenticationProvider"
			class="org.springframework.security.providers.dao.DaoAuthenticationProvider">
			<property name="userDetailsService">
				<ref bean="memoryAuthenticationDao" />
			</property>
		</bean>				
		<bean id="memoryAuthenticationDao" class="org.springframework.security.userdetails.memory.InMemoryDaoImpl">
		    <property name="userMap">
		        <value>
		            admin=adminpassword,ROLE_ADMIN,ROLE_USER
		        </value>
		    </property>
		</bean>
		-->
		
		
		<bean id="accessDecisionManager" class="org.springframework.security.vote.AffirmativeBased">
		    <property name="allowIfAllAbstainDecisions" value="false" />		    
		    <property name="decisionVoters">
		        <list>
		           <ref local="roleVoter"/>
		        </list>
		    </property>
		</bean>
		<bean id="roleVoter" class="org.springframework.security.vote.RoleVoter"/>
		
		
		<bean id="filterChainProxy"
			class="org.springframework.security.util.FilterChainProxy">
			<property name="filterInvocationDefinitionSource">
				<value>
					<![CDATA[
						CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
						PATTERN_TYPE_APACHE_ANT
						/**=httpSessionContextIntegrationFilter,logoutFilter,authenticationProcessingFilter,securityContextHolderAwareRequestFilter,exceptionTranslationFilter,filterSecurityInterceptor,filterInvocationInterceptor
					]]>
				</value>
			</property>
		</bean>

		<bean id="httpSessionContextIntegrationFilter" class="org.springframework.security.context.HttpSessionContextIntegrationFilter" />
						
		<bean id="authenticationProcessingFilter"
		    class="org.springframework.security.ui.webapp.AuthenticationProcessingFilter">
			<property name="authenticationManager" ref="authenticationManager"/>
			<property name="authenticationFailureUrl" value="/login.htm?login_error=1"/>
			<property name="defaultTargetUrl" value="/home.htm"/>
			<property name="filterProcessesUrl" value="/j_spring_security_check"/>
			<!-- 
			<property name="maximumTries" value="3"/>
			<property name="attemptGapMinutes" value="2"/>
		  	<property name="loginType" value="IP_LOGIN_TYPE"/>
 			<property name="auditManager" ref="auditManager"/> 
 			--> 
		</bean>		
		<bean id="exceptionTranslationFilter"
			class="org.springframework.security.ui.ExceptionTranslationFilter">
			<property name="authenticationEntryPoint">
				<bean
					class="org.springframework.security.ui.webapp.AuthenticationProcessingFilterEntryPoint">
					<property name="loginFormUrl" value="/login.htm" />
					<property name="forceHttps" value="false" />
				</bean>
			</property>
			<property name="accessDeniedHandler">
				<bean
					class="org.springframework.security.ui.AccessDeniedHandlerImpl">
					<property name="errorPage" value="/accessDenied.htm" />
				</bean>
			</property>
		</bean>
		
		
		<bean id="filterSecurityInterceptor" 
			class="org.springframework.security.intercept.web.FilterSecurityInterceptor">
		    <property name="authenticationManager">
		        <ref bean="authenticationManager"/></property>
		    <property name="accessDecisionManager">
		        <ref bean="accessDecisionManager"/></property>
		    <property name="objectDefinitionSource">
				<value>
					CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
					\A/home.htm\Z=ROLE_ADMIN, ROLE_STAFF
					\A/actionAuditSearchForm.htm\Z=ROLE_ADMIN
					\A/userAddForm.htm\Z=ROLE_ADMIN
					\A/userSearchForm.htm\Z=ROLE_ADMIN
					\A/summaryReport.htm\Z=ROLE_ADMIN,ROLE_STAFF, ROLE_DETACHMENT
				</value>
		    </property>
		</bean>
		
		
		<bean id="channelProcessingFilter"
			class="org.springframework.security.securechannel.ChannelProcessingFilter">
			<property name="filterInvocationDefinitionSource">
				<value>
					CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
					PATTERN_TYPE_APACHE_ANT
					/**=REQUIRES_INSECURE_CHANNEL
				</value>
			</property>
			<property name="channelDecisionManager">
				<bean class="org.springframework.security.securechannel.ChannelDecisionManagerImpl">
					<property name="channelProcessors">
						<list>
							<bean class="org.springframework.security.securechannel.SecureChannelProcessor"/>
							<bean class="org.springframework.security.securechannel.InsecureChannelProcessor"/>
						</list>
					</property>
				</bean>
			</property>
		</bean>
		
		
		
		<bean id="logoutFilter"
			class="org.springframework.security.ui.logout.LogoutFilter">
			<constructor-arg value="/login.htm" />
			<!-- URL redirected to after logout -->
			<constructor-arg>
				<list>					
					<bean class="org.springframework.security.ui.logout.SecurityContextLogoutHandler" />
				</list>
			</constructor-arg>
		</bean>

		
		<bean id="securityContextHolderAwareRequestFilter" class="org.springframework.security.wrapper.SecurityContextHolderAwareRequestFilter" />

		<!-- 
		<bean id="rememberMeProcessingFilter"
			class="org.springframework.security.ui.rememberme.RememberMeProcessingFilter">
			<property name="authenticationManager"
				ref="authenticationManager" />
			<property name="rememberMeServices" ref="rememberMeServices" />
		</bean>
		-->

		


		 
		<bean id="filterInvocationInterceptor"
			class="org.springframework.security.intercept.web.FilterSecurityInterceptor">
			<property name="authenticationManager"
				ref="authenticationManager" />
			<property name="accessDecisionManager">
				<bean class="org.springframework.security.vote.AffirmativeBased">
					<property name="allowIfAllAbstainDecisions"
						value="false" />
					<property name="decisionVoters">
						<list>
							<bean class="org.springframework.security.vote.RoleVoter" />
							<bean
								class="org.springframework.security.vote.AuthenticatedVoter" />
						</list>
					</property>
				</bean>
			</property>
					  	
			<property name="objectDefinitionSource">
				<value>
					CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
					PATTERN_TYPE_APACHE_ANT
					/humint/login.htm=ROLE_ADMIN, ROLE_STAFF
				</value>
			</property>
			
		</bean>
		

		
		
		<!-- ****** END SPRING SECURITY Configuration *******-->
	
	
</beans>

I am getting following displayed in console. It is not showing any error or stackTrace. I am not sure what i am missing.

   

11:41:26,312 INFO  [XmlBeanDefinitionReader] Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
11:41:26,344 INFO  [SQLErrorCodesFactory] SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

 

My Login.jsp looks like this.

    

<form name="loginForm" method=post action="j_spring_security_check" >

				<table    border="0" >
				<tr>
					<td  class="humintHeader">
						<jsp:include page="screenTitle.jsp">
							<jsp:param name="title" value="Enter User ID and Password"/>
						</jsp:include>
					</td>
				</tr>
				<tr>
					<td  >
						<table    border="0">
						<tr><td  ></td></tr>

						<c:if test="${loginMap.loginOK != null && loginMap.loginOK==false}">
							<tr><td  ><span class="medCaps" style="color:red;">Invalid User ID or password. Please try again</span></td></tr>
						</c:if>

						<tr ><td ></td></tr>

						<tr>
							<td ><span class="medCaps" style="color:black;">User ID :</span></td>
							<td ></td>
							<td  ><input class="textInput" type="text" size="25" name="j_username" value="admin">
						</tr>
						<tr ><td ></td></tr>
						<tr>
							<td ><span class="medCaps" style="color:black;">Password :</span></td>
							<td ></td>
							<td  ><input class="textInput" type="password" size="25" name="j_password" value="adminpassword"></td>
						</tr>
						<tr ><td ></td></tr>
						<tr>
							<td >
								<table    border="0">
									<tr>
										<td  ><input type="checkbox" name="access" checked="YES"/></td>
										<td ></td>
										<td  class="content" ><p>By checking the checkbox I understand any HUMINT restrictions TBD.</p></td>
									</tr>
								</table>
				             </td>
				         </tr>
				         <tr ><td ></td></tr>
				         <tr>
				         	<td > </td>
				            <td   >  
				                  <button type="button" id="pushbutton1" name="login" value="Login">Login</button>
				                   
				                  <button type="button" id="pushbutton2" name="clear" value="Clear">Clear</button>
				             </td>
				         </tr>
				          <tr ><td ></td></tr>
				         </table>
				     </td>
				 </tr>
				 </table>
			</form>

I really apprecieate for quick response.

Thanks Folks

[Bhukka Patel]

For your authoritiesByUsernameQuery you also need username like the following

    
<property name="authoritiesByUsernameQuery">   
                <value>   
                SELECT userid AS username, DECODE(is_admin, 'Y', 'ROLE_ADMIN' ) as authority    
                    FROM SEC_USER   
                    WHERE upper(userid)=upper(?)                       
                </value>   
</property>

[Bhukka Patel]

Bhukka Patel wrote:For your authoritiesByUsernameQuery you also need username like the following

    
<property name="authoritiesByUsernameQuery">   
                <value>   
                SELECT userid AS username, DECODE(is_admin, 'Y', 'ROLE_ADMIN' ) as authority    
                    FROM SEC_USER   
                    WHERE upper(userid)=upper(?)                       
                </value>   
</property>

[Praveen Kondaka]

Thanks Bhukka Patel. I apprecieate your help.

Actually it was there. since i was playing with different options, i pasted wrong one.

   

<bean id="dbUserService" class="org.springframework.security.userdetails.jdbc.JdbcDaoImpl">
			<property name="dataSource" ref="dataSource"/>
			<property name="usersByUsernameQuery">
				<value>
					SELECT USERID AS username, HASHPASS AS password, DECODE(active_ind,'Y','true') AS enabled
					  FROM SEC_USER
					 WHERE upper(userid)=upper(?)
				</value>
			</property>
			<property name="authoritiesByUsernameQuery">
				<value>
				SELECT USERID AS username,DECODE(is_admin, 'Y', 'ROLE_ADMIN' ) as authority 
					FROM SEC_USER
					WHERE upper(userid)=upper(?)  					
				</value>
			</property>
		</bean>
		<bean id="daoAuthenticationProvider" class="org.springframework.security.providers.dao.DaoAuthenticationProvider">
			<property name="userDetailsService" ref="dbUserService"/>
			<property name="passwordEncoder" ref="passwordEncoder"/>
		</bean>	

Anyway, i tried with above configuration. still it is not working. same message in my app server console.

    

12:23:45,188 INFO  [XmlBeanDefinitionReader] Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
12:23:45,219 INFO  [SQLErrorCodesFactory] SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

Besides applicationConext-security.xml do i need any other configuration? Am i missing anything?

Your help is greatly apprecieated.

Cheers!!

[Bhukka Patel]

Hi Praveen,

Where is bean:passwordEncoder defined?

Does your database support DECODE function? For purpose of debugging try giving explicit role

    
            <property name="usersByUsernameQuery">   
                <value>   
                    SELECT USERID AS username, HASHPASS AS password, 'true' AS enabled   
                      FROM SEC_USER   
                     WHERE upper(userid)=upper(?)   
                </value>   
            </property>   
            <property name="authoritiesByUsernameQuery">   
                <value>   
                SELECT USERID AS username, 'ROLE_ADMIN' as authority    
                    FROM SEC_USER   
                    WHERE upper(userid)=upper(?)                       
                </value>   
            </property>   

[Praveen Kondaka]

For now i am not using any passwordEncoder. my password is plain password in database and i am using same password in in my form field.

About your other suggestion, I removed DECODE and tried

[Bhukka Patel]

Here is security config file that I use. My filters are auto configured as my security requriements are simple at this point.

    
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:security="http://www.springframework.org/schema/security"
	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.5.xsd
							http://www.springframework.org/schema/security
							http://www.springframework.org/schema/security/spring-security-2.0.4.xsd">

	<!-- The security configuration of the application. -->
	<security:http auto-config="true" access-decision-manager-ref="accessDecisionManager" access-denied-page="/accessDenied.htm">
		<security:intercept-url pattern="/login.htm*" filters="none"/>
		<security:intercept-url pattern="/search**" access="ROLE_WRITE"/>
		<security:intercept-url pattern="/**" access="ROLE_READ, ROLE_WRITE"/>
		<security:form-login login-page="/login.htm" login-processing-url="/skeletonLogin.htm" default-target-url="/index.htm" always-use-default-target="false" authentication-failure-url="/login.htm?error=true"/>
		<security:logout logout-url="/skeletonLogout.htm" invalidate-session="true" logout-success-url="/login.htm"/>
		<security:concurrent-session-control max-sessions="1" />
	</security:http>
	
	<security:authentication-provider>
		<security:password-encoder ref="passwordEncoder"> 
			<security:salt-source system-wide="someSalt"/>
		</security:password-encoder>
		<security:jdbc-user-service data-source-ref="dataSource" 
			users-by-username-query="SELECT userid AS username, passwd AS password, 1 AS enabled FROM users WHERE userid = ?"
			authorities-by-username-query="SELECT a.userid AS username, l.levname AS authority FROM access a INNER JOIN levels l ON a.utype=l.utype AND a.ulev=l.ulev WHERE a.userid = ?"
		/>

	</security:authentication-provider>

	<bean id="passwordEncoder" class="org.proprietary.springskeleton.service.PasswordEncoderImpl"/>
	<bean id="loggerListener" class="org.springframework.security.event.authentication.LoggerListener"/>

	<bean id="accessDecisionManager" class="org.springframework.security.vote.AffirmativeBased">
		<property name="decisionVoters">
			<list>
				<ref bean="roleVoter" />
				<ref bean="authenticatedVoter"/>
			</list>
		</property>
	</bean>

	<bean id="roleVoter" class="org.springframework.security.vote.RoleVoter">
	</bean>
	
	<bean id="authenticatedVoter" class="org.springframework.security.vote.AuthenticatedVoter"/>

</beans>

[Praveen Kondaka]

Thanks for sharing your config file.

How is your login.jsp/login.htm looks like. where do you submit?

Thanks

[Ngan Le]

It seems Spring Security doesn't check or do anything within the authorities-by-username-query of my security context.xml file. Do I need to explicitly tell it to check? When I intentionally give an invalid table within the users-by-username-query, I do get back errors indicating bad sql but not for the authorities-by-username-query. Did i miss anything? Thanks.

Here is the snippet code in my security context.xml:

   
<jdbc-user-service id="userService"
  		data-source-ref="dataSource" 	  									
	  	users-by-username-query="SELECT U.username, U.userPassword as password, 'true' AS enabled
	  							FROM Users U where U.userName=?" 
   		authorities-by-username-query="SELECT U.username, A.authority as authorities 
								FROM Users U, authorities A
								WHERE (U.user_id = A.user_id) and (U.username=?)" 
	 /> 
 

[Ngan Le]

Then authentication always fails regardless of whatever login/password I gave, even with the correct login/password.

[ramzi dridi]

<!-- Configure the authentication provider -->
    <security:authentication-provider>
  <security:jdbc-user-service data-source-ref="dataSource" 
                              users-by-username-query="SELECT username,password,enabled FROM utilisateur WHERE username = ?"
                              authorities-by-username-query="SELECT username,role FROM profil WHERE username = ?"/>
</security:authentication-provider>
</beans>

complete example here:
[url=
http://j2eeandroid.blogspot.com/2014/12/spring-security.html]
http://j2eeandroid.blogspot.com/2014/12/spring-security.html[/url]