In previous post related to spring 3 security demo application using default user service in configuration file, we learned about securing an application behind a login page. In that post, username and passwords were stored in application-security.xml file itself. Now its time to take these authentication parameters out of configuration and store them in database.
There are two ways of doing this i.e. using custom user service implementation or spring provided jdbc user service. In this post, I am showing the way to use second approach i.e. jdbc user service.
Sections in this post: Create tables in database Update security configuration to use jdbc user service Test the application
Background information
Apart from other inbuilt user details services inside spring container, JDBC user detail implementation is one of it. It is configured using tag “jdbc-user-service“.
Just like other spring features, this also comes with default implementation ready to use e.g.
<authentication-manager alias="authenticationManager"> <authentication-provider> <jdbc-user-service data-source-ref="dataSource" /> </authentication-provider> </authentication-manager>
Above been definition will use the default tables in configured database. It assumes to be:
create table users( username varchar_ignorecase(50) not null primary key, password varchar_ignorecase(50) not null, enabled boolean not null); create table authorities ( username varchar_ignorecase(50) not null, authority varchar_ignorecase(50) not null, constraint fk_authorities_users foreign key(username) references users(username)); create unique index ix_auth_username on authorities (username,authority);
BUT, in my example, I have created two tables according to my choice and have decided to feed data to jdbc user service using sql queries.
Create tables in database
So lets create our own tables in database:
-- ---------------------------- -- Table structure for `tbl_users` -- ---------------------------- DROP TABLE IF EXISTS `tbl_users`; CREATE TABLE `tbl_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `password` varchar(20) NOT NULL, `enabled` int(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Table for storing the role and username mapping.
-- ---------------------------- -- Table structure for `tbl_user_role` -- ---------------------------- DROP TABLE IF EXISTS `tbl_user_role`; CREATE TABLE `tbl_user_role` ( `userid` int(11) NOT NULL, `rolename` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
These tables have all required information like username, password, enabled status and assigned role. JDBC user service require these values only for a username provided as parameter.These values will be fetched with following sql queries:
SELECT USERNAME, PASSWORD, CASE ENABLED WHEN 1 THEN 'true' ELSE 'false' END 'ENABLED' FROM TBL_USERS WHERE USERNAME=?; +----------+----------+---------+ | USERNAME | PASSWORD | ENABLED | +----------+----------+---------+ | lokesh | password | true | +----------+----------+---------+
Query to select role name.
SELECT u.USERNAME, r.ROLENAME FROM TBL_USERS u, TBL_USER_ROLE r WHERE u.ID = r.USERID AND u.USERNAME=?; +----------+-----------+ | USERNAME | ROLENAME | +----------+-----------+ | lokesh | ROLE_USER | +----------+-----------+
Update security configuration to use jdbc user service
This can be done as follows:
<authentication-manager alias="authenticationManager"> <authentication-provider> <jdbc-user-service data-source-ref="dataSource" users-by-username-query=" SELECT USERNAME, PASSWORD, CASE ENABLED WHEN 1 THEN 'true' ELSE 'false' END 'ENABLED' FROM TBL_USERS WHERE USERNAME=?;" authorities-by-username-query=" SELECT u.USERNAME, r.ROLENAME FROM TBL_USERS u, TBL_USER_ROLE r WHERE u.ID = r.USERID AND u.USERNAME=?;" /> </authentication-provider> </authentication-manager>
JDBC user service implementation provide these two attributes to match username with password and then username with granted role or authority.
- users-by-username-query
- authorities-by-username-query
Test the application
Now build and run above application again in application server. It will behave exactly the way authentication/ authorization worked for xml configuration in previous tutorial.
Download sourcecode
Happy Learning !!
How do we get access to dataSource?
please add
how it authenticate or varify the password?
Here How to get the USERNAME value if user failed to login?
How to handle a case when user assigned multiple roles?
How do I get user ID ?
You can execute a sql query separately using username/email in where clause. For auth purpose, you don’t need user id. right?
what is the use of enabled
Whether user is active or not.
Excellent example, thank you. How would one do this with encryption on the password field for MSSQL server?
How about implementing Remember me with persistent tokens (persistent_logins table in database)?
Thanks
I will try
Good, thanks for the example