Spring security with Mysql

We are going to be using mysql for the purpose of “Spring security user storage”. And we will be having two different tables created to achieve this goal.

1. Users table – This table will be holding the user’s details

craete table users(username varchar(50) not null primary key,
password varchar(50) not null,
enabled boolean not null);

2. Authorities table – This table is going to hold the authorirties per user.

create table authorities (
username varchar(50) not null,
authority varchar(50) not null,
constraint fk_authorities_users
foreign key (username) references users(username));
create unique index ix_auth_username on
authorities(username,authority);

3. Insert into USERS table

insert into users(username,password,enabled) values
("saptarshi","password",true);

4. Insert into authorirties table

Insert into authorirties(username,authority) values
("saptarshi", "ROLE_USER);

Let’s add the below two dependencies to java project on our pom.xml file.

5. MYSQL connector java dependency

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
</dependency>

6. Spring JDBC dependency

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.1.1.RELEASE</version>
</dependency>

7. Now we should be creating a Data Source object in our security-config.xml

<b:beans xmlns="http://www.springframework.org/schema/security"
         xmlns:b="http://www.springframework.org/schema/beans"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://www.springframework.org/schema/beans 
                 http://www.springframework.org/schema/beans/spring-beans.xsd
         http://www.springframework.org/schema/security 
                 http://www.springframework.org/schema/security/spring-security.xsd">
 
    <http auto-config="true">
             <intercept-url pattern ="/**" access="ROLE_USER" />
        </http>
 
        <authentication-manager>
           <authentication-provider user-service-ref="userDetailsService" />
              <!-- Lets remove the in memory user service -->
            <!-- <user-service>
              <user name="saptarshi" password="password" authorities="ROLE_USER" />
            </user-service> 
           </authentication-provider> -->
        <authentication-manager>
     
     <!-- Adding the Data Source for local mysql --> 
       <b:bean id="dataSource" class ="org.springframework.jdbc.datasource.DriverManagerDataSource">
<b:property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<b:property name="url" value="jdbc:mysql://localhost:3306/springSecurity"/>
<b:property name="username" value="root"/>
<b:property name="password" value="root"/>
</b:bean>

<b:bean id="userDetailsService" class ="org.springframework.security.core.userDetails.jdbc.JdbcDaoImpl">
<b:property name="dataSource" ref="dataSource"/>
</b:bean>

</b:beans>

We are done modifying our changes. Now if we visit any page of our application, it will redirect us to the login page where we would need to be passing the username and password , what we have inserted as part of our step -3 , to get authenticated .