MySQL ddl tables definitions for Spring Security

MySQL ddl tables definitions for Spring Security

Spring Security Framework can also be used to implement web security in project using database based authentications. Spring Security will use database tables to authenticate users. We can use any custom database table form authentications using Spring Security settings but Spring itself defines default table structures that can be used for authentication.

In this tutorial will will see the table structure specified by the Spring Security for MySQL database.In our next tutorial we will learn how to use these tables for Spring Security authentication with some small change in security settings. Table definitions are categorized according to the depth of the use of Spring Security Framework facilities. If you need only basic level authentication then just create the tables that are used for authentications. Spring Security has three levels of usability, according to which tables are categorized in three levels.

Following are the three categories and their respective ddl commands for tables in MySQL server syntax :

Tables for User Schema

Following tables are needed for the user authentication and role based authorization of the users:

create table users (

    username varchar(50) not null primary key,

    password varchar(50) not null,

    enabled boolean not null

) engine = InnoDb;

create table authorities (

    username varchar(50) not null,

    authority varchar(50) not null,

    foreign key (username) references users (username),

    unique index authorities_idx_1 (username, authority)

) engine = InnoDb;

If you create these two tables in MySQL database and configure this database with Spring Security, it will automatically use these tables for authentication and role determination purpose.

Spring Security will use “USERS” table for authentication and “AUTHORITIES” table for determination of role of the user for role based access control.

Table for Remember me facility

For using database used “Remember Me” facility in Spring Security, you have to create following tables :

create table persistent_logins (

    username varchar(64) not null,

    series varchar(64) primary key,

    token varchar(64) not null,

    last_used timestamp not null

) engine = InnoDb;

“persistent_logins” will store entries for the users who have used remember me option during login.

Tables for Roll based Group Schema

You can also use group based authorization that is provided by Spring Security using following tables:

create table groups (

    id bigint unsigned not null auto_increment primary key,

    group_name varchar(50) not null

) engine = InnoDb;

create table group_authorities (

    group_id bigint unsigned not null,

    authority varchar(50) not null,

    foreign key (group_id) references groups (id)

) engine = InnoDb;

create table group_members (

    id bigint unsigned not null auto_increment primary key,

    username varchar(50) not null,

    group_id bigint unsigned not null,

    foreign key (group_id) references groups (id)

) engine = InnoDb;

These tables are used by the Spring to maintain role based groups for the application. Any page can be defined accessible for a particular group.