spring security com firebird

Respostas a este tópico

Permalink Responder até Carlos Augusto de Oliveira em 1 julho 2011 at 16:30

Olá Antonio, desculpe pela demora em responder, usei firebird por vários anos inclusive com spring security (acegi security na época), username e password são palavras reservadas no fb, vc tem que colocar entre aspas duplas no SQL se quiser usa-las.

Criei uma tabela de usuários para teste:

 

CREATE TABLE USUARIOS (
    ID         INTEGER NOT NULL,
    LOGIN      VARCHAR(30) NOT NULL,
    SENHA      VARCHAR(10) NOT NULL,
    PERMISSAO  VARCHAR(40) NOT NULL
);

 

ID    LOGIN    SENHA    PERMISSAO
1    teste    senha    admin

 

E um programinha de testes que funcionou corretamente:

 

test-context.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.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc">


    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="url"
            value="jdbc:firebirdsql:localhost:C://Projetos//workspaces//teste spring//DB//teste.fdb" />
        <property name="driverClassName" value="org.firebirdsql.jdbc.FBDriver" />
        <property name="username" value="SYSDBA" />
        <property name="password" value="masterkey" />
    </bean>

</beans>

 

import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.authentication.BadCredentialsException;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.authentication.dao.DaoAuthenticationProvider;
import org.springframework.security.core.Authentication;
import org.springframework.security.provisioning.JdbcUserDetailsManager;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@ContextConfiguration("/test-context.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class SecurityTest {

    private static final String USERS_BY_USERNAME_QUERY = "SELECT login as \"username\", senha as \"password\", 1 as enabled FROM usuarios WHERE login=lower(?)";

    private static final String AUTHORITIES_BY_USERNAME_QUERY = "SELECT login as \"username\", permissao as authorities FROM usuarios WHERE login=?";

    @Autowired
    private DataSource dataSource;

    @Test
    public void testAuthFail() {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        JdbcUserDetailsManager userDetailsService = new JdbcUserDetailsManager();
        userDetailsService.setDataSource(dataSource);
        userDetailsService
                .setAuthoritiesByUsernameQuery(AUTHORITIES_BY_USERNAME_QUERY);
        userDetailsService.setUsersByUsernameQuery(USERS_BY_USERNAME_QUERY);
        provider.setUserDetailsService(userDetailsService);

        UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(
                "teste", "errada");

        try {
            provider.authenticate(authenticationToken);
            fail("Expected BadCredenialsException");
        } catch (BadCredentialsException expected) {
            assertTrue(true);
        }
    }

    @Test
    public void testAuthOk() {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        JdbcUserDetailsManager userDetailsService = new JdbcUserDetailsManager();
        userDetailsService.setDataSource(dataSource);
        userDetailsService
                .setAuthoritiesByUsernameQuery(AUTHORITIES_BY_USERNAME_QUERY);
        userDetailsService.setUsersByUsernameQuery(USERS_BY_USERNAME_QUERY);
        provider.setUserDetailsService(userDetailsService);

        UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(
                "teste", "senha");
        Authentication auth = provider.authenticate(authenticationToken);

        assertTrue(auth.getAuthorities().toString().equals("[admin]"));

    }
}

 

Qualquer dúvida poste aqui,

abraços.

 

 

• ▶ Responder

Permalink Responder até Antonio Alves em 2 julho 2011 at 14:36

Fiz pelo seu método e funcionou.

Obrigado pela ajuda.

Carlos Augusto de Oliveira disse:

Olá Antonio, desculpe pela demora em responder, usei firebird por vários anos inclusive com spring security (acegi security na época), username e password são palavras reservadas no fb, vc tem que colocar entre aspas duplas no SQL se quiser usa-las.

Criei uma tabela de usuários para teste:

 

CREATE TABLE USUARIOS (
    ID         INTEGER NOT NULL,
    LOGIN      VARCHAR(30) NOT NULL,
    SENHA      VARCHAR(10) NOT NULL,
    PERMISSAO  VARCHAR(40) NOT NULL
);

 

ID    LOGIN    SENHA    PERMISSAO
1    teste    senha    admin

 

E um programinha de testes que funcionou corretamente:

 

test-context.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.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc">


    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="url"
            value="jdbc:firebirdsql:localhost:C://Projetos//workspaces//teste spring//DB//teste.fdb" />
        <property name="driverClassName" value="org.firebirdsql.jdbc.FBDriver" />
        <property name="username" value="SYSDBA" />
        <property name="password" value="masterkey" />
    </bean>

</beans>

 

import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.authentication.BadCredentialsException;
import org.springframework.security.authentication.UsernamePasswordAuthenticationToken;
import org.springframework.security.authentication.dao.DaoAuthenticationProvider;
import org.springframework.security.core.Authentication;
import org.springframework.security.provisioning.JdbcUserDetailsManager;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@ContextConfiguration("/test-context.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class SecurityTest {

    private static final String USERS_BY_USERNAME_QUERY = "SELECT login as \"username\", senha as \"password\", 1 as enabled FROM usuarios WHERE login=lower(?)";

    private static final String AUTHORITIES_BY_USERNAME_QUERY = "SELECT login as \"username\", permissao as authorities FROM usuarios WHERE login=?";

    @Autowired
    private DataSource dataSource;

    @Test
    public void testAuthFail() {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        JdbcUserDetailsManager userDetailsService = new JdbcUserDetailsManager();
        userDetailsService.setDataSource(dataSource);
        userDetailsService
                .setAuthoritiesByUsernameQuery(AUTHORITIES_BY_USERNAME_QUERY);
        userDetailsService.setUsersByUsernameQuery(USERS_BY_USERNAME_QUERY);
        provider.setUserDetailsService(userDetailsService);

        UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(
                "teste", "errada");

        try {
            provider.authenticate(authenticationToken);
            fail("Expected BadCredenialsException");
        } catch (BadCredentialsException expected) {
            assertTrue(true);
        }
    }

    @Test
    public void testAuthOk() {
        DaoAuthenticationProvider provider = new DaoAuthenticationProvider();
        JdbcUserDetailsManager userDetailsService = new JdbcUserDetailsManager();
        userDetailsService.setDataSource(dataSource);
        userDetailsService
                .setAuthoritiesByUsernameQuery(AUTHORITIES_BY_USERNAME_QUERY);
        userDetailsService.setUsersByUsernameQuery(USERS_BY_USERNAME_QUERY);
        provider.setUserDetailsService(userDetailsService);

        UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(
                "teste", "senha");
        Authentication auth = provider.authenticate(authenticationToken);

        assertTrue(auth.getAuthorities().toString().equals("[admin]"));

    }
}

 

Qualquer dúvida poste aqui,

abraços.

 

 

• ▶ Responder

▶ Responder à discussão