Using custom tables for Spring Security authentication and authorization

It is not must to use database tables specified in the Spring Security Specification. We can also use our own custom database tables to store user authentication and authorization and use those tables in Spring Security for user authentication and authorizations with some restrictions shown bellow:

  • Your database tables should have enough columns to specify user authentication and role.
  • You have to specify the sql query for which will be used to get user details from your database tables.

We will modify our Using database tables for user authentication and authorization example and use our own tables instead of Spring Security Specific database tables. So, if you have not read the above tutorial, please read it or if you have prior knowledge of setting up database authentication in Spring Security, you can go ahead.

Tools used :

  • Spring MVC 3.0.3
  • Spring JDBC 3.0.5
  • Spring Security 3.0.5
  • Eclipse Indigo 3.7
  • Tomcat 6
  • Jdk 1.6
  • MySQL 5.1 Database and Driver jar

In this tutorial, we will use our own tables in Spring Security and authenticate the user.

Custom Table Definations

In this example we will use following tables in our database to authenticate the user:


create table user_deatils (
    username varchar(50) primary key,
    password varchar(50) not null,
    name varchar(300) not null,
    address varchar(1000),
    enabled boolean not null
) engine = InnoDb;

create table user_roles(
    role_id integer primary key,
    role varchar(50) not null,
) engine = InnoDb;

create table users_role_map (
    username varchar(50) not null,
    role_id integer not null,
    foreign key (username) references users (username),
    foreign key (role_id) references user_roles(role_id)
) engine = InnoDb;

For creating a user detail and role of the user in the database use following MySQL DML insert command :


 insert into user_deatils values('admin','admin','Jency Marker','23 Street, Washington',1);
 insert into user_roles values(1,'ROLE_ADMIN');
 insert into users_role_map values('admin',1);

This will create a user with username and password as “admin” and with role “ROLE_ADMIN” in the custom database tables.

Modifying Spring Security Configuration File

We have to change our Spring Security Configuration file, so that Spring Security uses our custom tables. For this we have to specify two extra attributes in <jdbc-user-service/> tag as follows:


<authentication-manager>
    <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource" users-by-username-query="select username,password, enabled
        from user_deatils where username=?" authorities-by-username-query="select ud.username, ur.role
        from user_deatils ud, user_roles ur,users_role_map urm
        where ud.username = urm.username and urm.role_id = ur.role_id and ud.username = ? "/>
    </authentication-provider>
</authentication-manager>

Here, there are two attributes that we have specified in <jdbc-user-service/> tag, both of them are actually specifying SQL queries that will be used by Spring Security to authenticate the user:

  • users-by-username-query : This attribute will hold the query according to your custom tables that will select username, password and enabled properties of the user and will take username as parameter.
  • authorities-by-username-query : This attribute will hold the SQL query according to your custom tables that will select username and role of the user and accept username as parameter .

That’s all for using custom database table for user authentication and authorization.

Deploy the war file in Tomcat 6 server and hit the url in browser, you will get following login page for authentication:

Custom Login Form in Spring Security

Custom Login Form in Spring Security

Now, login with username and password as “admin”, you will be directed to the secure page:

Spring Security Secure Page with Logout option

Spring Security Secure Page with Logout option

You can download the source of the example from following links:

Source: Download

WAR (With libs) :Download

Related Posts:

Leave a comment ?

7 Comments.

  1. You need “values” in this query:

    insert into users_role_map(‘admin’,1);

    😉

  2. hii Rahul, this is good article
    but when i’m try your project…, i have little problem. it can’t run because i was wrong when import lib[.jar], can you help me what .jar can be import

    thx
    agung

  3. this my lib/jar :

    aopalliance-1.0.jar
    aspectjrt-1.6.8.jar
    aspectjweaver-1.6.8.jar
    cglib-nodep-2.2.jar
    commons-beanutils-1.8.3.jar
    commons-collections-3.2.1.jar
    commons-digester-2.1.jar
    commons-logging-1.1.1.jar
    jackson-core-asl-1.5.3.jar
    jackson-mapper-asl-1.5.3.jar
    jstl-1.2.jar
    log4j-1.2.16.jar
    mysql-connector-java-5.0.8-bin.jar
    slf4j-api-1.5.10.jar
    slf4j-log4j12-1.5.10.jar
    spring-aop-3.0.3.RELEASE.jar
    spring-asm-3.0.3.RELEASE.jar
    spring-beans-3.0.3.RELEASE.jar
    spring-context-3.0.3.RELEASE.jar
    spring-context-support-3.0.3.RELEASE.jar
    spring-core-3.0.3.RELEASE.jar
    spring-expression-3.0.3.RELEASE.jar
    spring-security-acl-3.0.1.RELEASE.jar
    spring-security-acl-3.0.1.RELEASE-sources.jar
    spring-security-acl-3.0.5.RELEASE.jar
    spring-security-cas-client-3.0.1.RELEASE.jar
    spring-security-cas-client-3.0.1.RELEASE-sources.jar
    spring-security-config-3.0.1.RELEASE.jar
    spring-security-config-3.0.1.RELEASE-sources.jar
    spring-security-config-3.0.5.RELEASE.jar
    spring-security-core-3.0.1.RELEASE.jar
    spring-security-core-3.0.1.RELEASE-sources.jar
    spring-security-core-3.0.5.RELEASE.jar
    spring-security-ldap-3.0.1.RELEASE.jar
    spring-security-ldap-3.0.1.RELEASE-sources.jar
    spring-security-openid-3.0.1.RELEASE.jar
    spring-security-openid-3.0.1.RELEASE-sources.jar
    spring-security-samples-contacts-3.0.1.RELEASE-sources.jar
    spring-security-samples-tutorial-3.0.1.RELEASE-sources.jar
    spring-security-taglibs-3.0.1.RELEASE.jar
    spring-security-taglibs-3.0.1.RELEASE-sources.jar
    spring-security-taglibs-3.0.5.RELEASE.jar
    spring-security-web-3.0.1.RELEASE.jar
    spring-security-web-3.0.1.RELEASE-sources.jar
    spring-security-web-3.0.5.RELEASE.jar
    spring-tx-3.0.5.RELEASE.jar
    spring-web-3.0.3.RELEASE.jar
    spring-webmvc-3.0.3.RELEASE.jar

  4. hii Rahul

    thx for you reply, & source .war[with libs]
    this is very help #niceTutorial brother

    thx regards
    agungdmt

  5. hi rahul while creating tables in database i am getting the following error .

    ERROR 1005 (HY000): Can’t create table ‘.\jpa\users_role_map.frm’ (errno: 150).i am not getting why this error showing first 2 tables are created
    thanks in advance

Leave a Comment Cancel reply

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>