Spring Security with Custom Tables

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 User authentication and authorization in Spring Security using MySQL tables 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.

Download Source Code

click here to download the source code