Spring security problem

[Ashok Pradhan]

I have created a simple Spring Security application which stores username and password in database table .
The app is not working even if I gave correct username and password .

my spring-security.xml file is as follows :

<http auto-config="true" use-expressions="true">
		<intercept-url pattern="/admin/**" access="hasRole('ADMIN')" />
		<form-login login-page="/login.html"
			authentication-failure-url="/login.html?error=true"
			default-target-url="/admin/success.html" />
		<logout invalidate-session="true" logout-success-url="/login.html"
			logout-url="/logout" />
	</http>
	<authentication-manager>
		<authentication-provider>
			<jdbc-user-service data-source-ref="dataSource"
				users-by-username-query="select username,password from users where username=?"
				authorities-by-username-query="select u.username, ur.rolename from users u, user_roles ur where u.username = ur.username and username=?" />
			<!-- <user-service> <user name=" ashok " password="admin" authorities="ADMIN" 
				/> </user-service> -->
		</authentication-provider>
	</authentication-manager>

I have configured dataSource bean in application context and it is working fine as other components using jdbc works fine.

If I use in-memory authentication then it works fine .

Actually when I gave correct username and password to login page in server console it show this message

01:10:16,357 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] (http-localhost-127.0.0.1-8080-6) Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
01:10:16,472 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] (http-localhost-127.0.0.1-8080-6) SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]


I have created two table for storing username,password and roles as USERS and USER_ROLES as follows :

CREATE TABLE "USERS"
( "USERNAME" VARCHAR2(40) NOT NULL ENABLE,
"PASSWORD" VARCHAR2(40) NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USERNAME") ENABLE
) ;
CREATE TABLE "USER_ROLES"
( "USERNAME" VARCHAR2(40) NOT NULL ENABLE,
"ROLENAME" VARCHAR2(10) NOT NULL ENABLE,
CONSTRAINT "USER_ROLES_PK" PRIMARY KEY ("USERNAME", "ROLENAME") ENABLE
) ;ALTER TABLE "USER_ROLES" ADD CONSTRAINT "USER_ROLES_FK" FOREIGN KEY ("USERNAME")
REFERENCES "USERS" ("USERNAME") ENABLE;


Please help !!

[Kathleen Angeles]

Add a third item in the 'select' statement (a boolean), to represent if the user access is enabled or not.

If your system doesnt have such field, you can hardcode it like below.

users-by-username-query="select username,password,true from users where username=?"

- k


--------------------------------------------------------------------------------------------------
[SpringSource Certified Spring Professional - Practice Tests]

[Ashok Pradhan]

Thanks Kathleen Angeles !!

I have changed the line

users-by-username-query="select username,password,true from users where username=?"

Still not working ,I have also tested using

users-by-username-query="select username,password,'true' from users where username=?"

and

users-by-username-query="select username,password,TRUE from users where username=?"

and

users-by-username-query="select username,password,'TRUE' from users where username=?"

Nothing works !!

I am using Oracle 11g XE as my database !!

[Kathleen Angeles]

Improve the query as below (where I add 'u.' before the last 'username').

query="select u.username, ur.rolename from users u, user_roles ur where u.username = ur.username and u.username=?" />  

Also, look deeper into the logs for other error messages.

In addition, when you say 'it doesnt work', what exactly happens? Is the authentication process completed and you get a invalid userid/password message? Or something else?

[Kathleen Angeles]

Adding the 'u.' I mentioned above should fix it.

I tried your query on my Oracle XE using SQL Developer, and the query is rejected (query is 'ambiguous'). Oracle rejects it. He doesnt know which 'username' you are referring to. It should be 'u.username' or 'ur.username'.

[Ashok Pradhan]

Again thanks Kathleen !!

Changed the code as you have suggested..

<jdbc-user-service data-source-ref="dataSource" users-by-username-query="select username,password,'true' from users where username=?" authorities-by-username-query="select u.username, ur.rolename from users u, user_roles ur where u.username = ur.username and u.username=?" />

After changed to this when I login using correct username and password in login page it shows me invalid username or password !!

My mappings are

@Controller
@RequestMapping(value="/admin")
public class AdminController {
	
	@RequestMapping(value="/success")
	public String getSuccessPage(){
		System.out.println("You are trying to access /admin/success.html");
		return "success";
	}

}

and

@Controller
public class LoginController {

	@RequestMapping(value="/login")
	public String getLoginPage(ModelMap model,
			@RequestParam(value = "error", required = false) boolean error) {
		System.out.println("You are trying to access /login.html");
		if(error==true){
			System.out.println("You have entered invalid username or password");
			model.addAttribute("error","you have entered username or password");
		}else{
			model.addAttribute("error", "");
		}
		return "loginpage";

	}
	
	@RequestMapping(value="/denied")
	public String getDeniedPage(){
		System.out.println("You are redirected to access /denied.html");
		return "deniedpage";
	}
}

And I have 1 row in USERS table
username|password
--------------------------
ashok |admin

and 1 row in USER_ROLES table
username|rolename
--------------------------
ashok |ADMIN


About error Log
After a fresh deploy when I login using correct username/password it shows this


19:37:31,398 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You are trying to access /login.html

19:37:40,999 INFO [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] (http-localhost-127.0.0.1-8080-2) Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
19:37:41,097 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] (http-localhost-127.0.0.1-8080-2) SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
19:37:41,218 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You are trying to access /login.html

19:37:41,219 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You have entered invalid username or password



and subsequent login attempt with correct username/password shows this in log with invalid username/password in login page

19:40:14,873 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You are trying to access /login.html
19:40:20,832 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You are trying to access /login.html
19:40:20,834 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You have entered invalid username or password



For login attempt with incorrect username/password whether it is a fresh request after deployment or subsequent login attempt
it shows invalid username or password in login page and this log message

19:41:25,877 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You are trying to access /login.html
19:41:33,321 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You are trying to access /login.html
19:41:33,322 INFO [stdout] (http-localhost-127.0.0.1-8080-2) You have entered invalid username or password



Again thanks for your kind help !!

[Kathleen Angeles]

What I mentioned in my post above somewhere, was to add 'true', without the quotes. Try that one.

select username,password,true

[Ashok Pradhan]

Tested with this still not working...


<jdbc-user-service data-source-ref="dataSource" users-by-username-query="select username,password,true from users where username=?" authorities-by-username-query="select u.username, ur.rolename from users u, user_roles ur where u.username = ur.username and u.username=?" />

I have also tried with TRUE but does not help ...

[Ashok Pradhan]

my whole project is available here

http://t2springsecurity.googlecode.com/svn/trunk/

[Kathleen Angeles]

Try execute your 2 select statements on your oracle client, eg. toad, sql developer, etc.

This is to check if your query gets what you wanted it to get.

[Kathleen Angeles]

Check also trailing spaces in your table column data on these columns - username and password.

If you have trailing spaces, you can use oracle trim() to trim the result data. E.g. 'select trim(username), trim(password)'.

[Ashok Pradhan]

When I execute these two queries in Oracle it returns the correct result

select username,password,'true' from users where username='ashok'; select u.username, ur.rolename from users u, user_roles ur where u.username = ur.username and u.username='ashok';

Without double quote int true results error

select username,password,true from users where username='ashok';

ORA-00904: "TRUE": invalid identifier


Thanks !!

[Kathleen Angeles]

Hi,

Apparently, in Oracle you have to put a numeric item on that part.

Try:

select username,password, 1 as enabled from users where

[Ashok Pradhan]

Thank you Kathleen at last it is just worked !!