spring security authentication from mysql database

Question

I'm trying to make spring authenticate users from my mysql database. It's working fine for users in memory. I followed every tutorial about this and still isn't working, I don't understand why, since it requires a very basic config.

My 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.1.xsd">

<http security="none" pattern="/javax.faces.resource/**" />
<http security="none" pattern="/static/**"/>
<http auto-config="true" use-expressions="true"
                         access-denied-page="/public/login.xhtml">

    <intercept-url pattern="/public/**" access="permitAll"/>
    <intercept-url pattern="/secure/adm.xhtml" access="hasRole('ROLE_ADMIN')"/>
    <intercept-url pattern="/secure/**" access="hasRole('ROLE_USER')"/>
    <intercept-url pattern="/login.xhtml" access="permitAll"/>
    <intercept-url pattern="/**" access="hasRole('ROLE_USER')"/>
    <form-login login-page="/public/login.xhtml"
                authentication-failure-url="/public/login.xhtml?erro=true"
                default-target-url="/secure/secure.xhtml"
                username-parameter="usuario"
                password-parameter="senha"
                /> <!--login-processing-url-->
    <logout/>
    <session-management invalid-session-url="/timeout.jsp">
        <concurrency-control max-sessions="1" error-if-maximum-exceeded="true" />
    </session-management>
</http>


<beans:bean id="dataSource"
            class="org.springframework.jdbc.datasource.DriverManagerDataSource" >

    <beans:property name="url" value="jdbc:mysql://localhost:3306/gde" />
    <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <beans:property name="username" value="root" />
    <beans:property name="password" value="" />
</beans:bean>

<authentication-manager alias="authenticationManager">
    <authentication-provider>

        <user-service>
            <user name="a" password="b" authorities="ROLE_USER"/>
            <user name="b" password="a" authorities="ROLE_ADMIN"/> 
        </user-service>

        <jdbc-user-service data-source-ref="dataSource"
                           users-by-username-query="SELECT USUARIO as username, SENHA as password, ISATIVO as enabled FROM usuario WHERE USUARIO=?"

                           authorities-by-username-query="SELECT USUARIO_USUARIO as username, AUTORIZACOES_TIPO as authority FROM usuario_tipo_usuario WHERE USUARIO_USUARIO=?"
        />
    </authentication-provider>
</authentication-manager>
</beans:beans>

For a/b and b/a it authenticates just fine.

try keeping only the database user service (remove the in memory `user-service`) and make sure the user exists on the database — bluefoot, Nov 22 '12 at 13:01
What does the debug log say when you try to authenticate as a user from the database. Have you verified that the queries work as you expect if you run them manually? Please post the relevant output. — Shaun the Sheep, Nov 22 '12 at 13:59
Thank you both! I don't exactly understand why, but your tip solved the problem bluefoot. And yes Luke, I had them tested. You guys think you could help me solve this "http://stackoverflow.com/questions/13481299/how-to-validate-a-login-inside-a-bean-using-spring-security"? Just give it a read when you can, I appreciate. — victor, Nov 22 '12 at 15:04

score 0 · Accepted Answer · answered Nov 22 '12 at 18:38

0

Officially answering: I just had to remove the in memory users and to get it working, although I don't really understand why. Thank you, bluefoot and Luke Taylor.

answered Nov 22 '12 at 18:38

victor

1,532
1
13
32

spring security authentication from mysql database

1 Answers1