javaspringhibernatespring-securityspring-orm

spring security sql-error-codes.xml , no exception trace is found


I have configured spring security with my application,but got stuck at one place with following INFO : org.springframework.beans.factory.xml.XmlBeanDefinitionReader-Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]. I had google but unable to find cause

my code :-

<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.2.xsd">

    <http security="none" pattern="/login"></http>
    <http auto-config="false" use-expressions="true">
        <intercept-url pattern="/*" access="hasRole('ROLE_USER')" />

        <!-- login-processing-url="" -->
        <form-login login-page="/login" default-target-url="/" authentication-failure-url="/login?error" username-parameter="username" password-parameter="password" />
        <logout logout-success-url="/login?logout" invalidate-session="true" />
    </http>

    <!-- <authentication-manager>
        <authentication-provider>
            <jdbc-user-service data-source-ref="dataSource" />
        </authentication-provider>
    </authentication-manager> -->


    <authentication-manager>
        <authentication-provider>
            <jdbc-user-service data-source-ref="dataSource"
                users-by-username-query="SELECT USERNAME,PASSWORD,ENABLED from SCOTT.TBL_USERS WHERE USERNAME=?;"
authorities-by-username-query="SELECT u.USERNAME, r.ROLENAME FROM SCOTT.TBL_USERS u,SCOTT.TBL_USER_ROLE r WHERE u.USERNAME = r.USERNAME 
    AND u.USERNAME=?;" />
        </authentication-provider>
    </authentication-manager>

     <beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <beans:property name="url" value="jdbc:oracle:thin:@192.168.6.147:1521:orcl" />
        <beans:property name="username" value="SCOTT" />
        <beans:property name="password" value="IPORTMAN" />
    </beans:bean> 
</beans:beans>

I had created my own tables :- CREATE TABLE "SCOTT"."USERS" ( "USERNAME" VARCHAR2(50) NOT NULL PRIMARY KEY, "PASSWORD" VARCHAR2(50) NOT NULL, "ENABLED" NUMBER(22 , 0) NOT NULL );

and

create table "SCOTT"."TBL_USER_ROLE" ( username varchar(50) not null, authority varchar(50) not null, constraint fk_authorities_users foreign key(username) references "SCOTT"."TBL_USERS"(username)); how can i trace the issue,why my login always fails, My login page appears i have given username and password but its just pop in console sql-error-codes.xml.


Solution

  • when we are using Oracle database then the table structure is like below
    CREATE TABLE users (username VARCHAR(50) PRIMARY KEY, password VARCHAR(50) NOT NULL, enabled VARCHAR(50) 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);
    
    the problem was in table structure.