Secure Your Mule Application With Spring JDBC

There are several ways to secure your Mule applications. Spring security is one of the ways to make your application safe and secure. The implementation of Spring security is also easy in Mule applications, as Mule is built on the top of Spring.

In this article, we will demonstrate the use of Spring security in our Mule application where the credentials will be taken from the database for authentication.

To start with, we will be using MS-SQL database to perform our database authentication and we need to create tables to store our users and user roles details.

With this following script, we will able to create our users and user_roles tables and insert the detail in them:

if not exists (select * from sysobjects where and )

CREATE TABLE users (

user_id INTEGER IDENTITY(1,1)NOT NULL PRIMARY KEY,

user_name VARCHAR(50) NOT NULL,

password VARCHAR(50) NOT NULL,

enabled BIT

)

if not exists (select * from sysobjects where and )

CREATE TABLE user_roles (

  user_role_id INTEGER IDENTITY(1,1)NOT NULL PRIMARY KEY,

  user_id INTEGER NOT NULL,

  authority VARCHAR(45) NOT NULL,

  FOREIGN KEY (user_id) REFERENCES users (user_id)

)

/* insert into users */

INSERT INTO users (user_name, password, enabled)

VALUES ('Anirban', 'pass', 1);

INSERT INTO users (user_name, password, enabled)

VALUES ('Ajit', '124pass', 1);



/* insert into user_roles */

INSERT INTO user_roles (user_id, authority)

VALUES (1, 'ROLE_ADMIN');

INSERT INTO user_roles (user_id, authority)

VALUES (1, 'ROLE_USER');


INSERT INTO user_roles (user_id, authority)

VALUES (2, 'ROLE_USER');

Once the tables are created and data are stored in MS-SQL database table, we are able to authenticate our application with the database details.

So, we will be creating a Java class in our Mule application that will run the above script and create these database tables in our MS-SQL database and insert the values at the start of the application:

package com.dao.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class DBInitialization implements InitializingBean {

private static final Logger LOG = LogManager.getLogger(DBInitialization.class);

public void afterPropertiesSet() throws Exception {

String dbURL = System.getProperty("url");
Connection conn = null;
Class.forName(System.getProperty("driverName")).newInstance();
// Get a connection
conn = DriverManager.getConnection(dbURL);
Statement stmt = conn.createStatement();
try {

int user=stmt.executeUpdate("if not exists (select * from sysobjects where name='users' and xtype='U')CREATE TABLE users (user_id INTEGER IDENTITY(1,1)NOT NULL PRIMARY KEY,user_name VARCHAR(50) NOT NULL,password VARCHAR(100) NOT NULL,enabled BIT)");
int user_roles=stmt.executeUpdate("if not exists (select * from sysobjects where name='user_roles' and xtype='U')CREATE TABLE user_roles (user_role_id INTEGER IDENTITY(1,1)NOT NULL PRIMARY KEY,user_id INTEGER NOT NULL,authority VARCHAR(45) NOT NULL,FOREIGN KEY (user_id) REFERENCES users (user_id))");

if(user_roles==0 && user==0)
{
LOG.info("Table does not exists, creating new table");
/* encrypted password inserted into db*/
stmt.executeUpdate("INSERT INTO users (user_name, password, enabled)VALUES ('Anirban', "+"'"+encryptPass("pass")+"'"+", 1)");
stmt.executeUpdate("INSERT INTO users (user_name, password, enabled)VALUES ('Ajit', "+"'"+encryptPass("124pass")+"'"+", 1)");

stmt.executeUpdate("INSERT INTO user_roles (user_id, authority)VALUES (1, 'ROLE_ADMIN')");
stmt.executeUpdate("INSERT INTO user_roles (user_id, authority)VALUES (1, 'ROLE_USER')");
stmt.executeUpdate("INSERT INTO user_roles (user_id, authority)VALUES (2, 'ROLE_USER')");
}
else if(user_roles==-1 && user==-1) 
{
LOG.error("Table already exists.. Skipping the db initialization stage");
}

} 
catch (java.sql.SQLException sqle) {
sqle.printStackTrace();
throw sqle;
}
}

public static String encryptPass(String password) throws Exception {
  String cryptedPassword = new BCryptPasswordEncoder().encode(password);
  return cryptedPassword;
 }

}

Please note that it’s always a good practice to externalize the SQL scripts in the Java class like I have done here to make it easier for the readers to understand.

Also, plain text for passwords should not be used; we should always use an encrypted password. That's the reason I have converted the plain text password into bcrypt encoded hash values here.

For more information on bcrypt encryption please refer to the following link: Password encryption.

We can now create a Mule application that will use this Spring JDBC security to authenticate users based on their credentials and roles.

Our Mule application will be as follows:

 <spring:beans>  
    <spring:bean id="dbinitialization" name="dbinit" class="com.dao.db.DBInitialization"/>
    <spring:bean id="DB_Source" name="DB_Source" class="org.enhydra.jdbc.standard.StandardDataSource">
            <spring:property name="url" value="${url}"/>
            <spring:property name="driverName" value="${driverName}"/>
        </spring:bean>

    <ss:authentication-manager  alias="authenticationManager">
        <ss:authentication-provider>
        <ss:password-encoder ref="encoder" />
       <ss:jdbc-user-service data-source-ref="DB_Source" users-by-username-query="select user_name, password, enabled from users where user_name = ?"
              authorities-by-username-query="select u.user_name, ur.authority from users u, user_roles ur where u.user_id = ur.user_id and u.user_name = ?" />
       </ss:authentication-provider>
    </ss:authentication-manager>

    <spring:bean id="encoder" class="org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder"/>

 </spring:beans>

<mule-ss:security-manager>
      <mule-ss:delegate-security-provider name="jdbc-provider" delegate-ref="authenticationManager"/>
</mule-ss:security-manager>

    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" doc:name="HTTP Listener Configuration"/>

   <flow name="DatabaseSecurityFlow">

        <http:listener config-ref="HTTP_Listener_Configuration" path="/test" doc:name="HTTP"/>

        <http:basic-security-filter realm="mule-realm" securityProviders="jdbc-provider"/>    

        <mule-ss:authorization-filter requiredAuthorities="ROLE_ADMIN"/>

        <set-payload value="Your credentials are correct!" doc:name="Set Payload"/>

        <logger message="Passed the security!" level="INFO" doc:name="Logger"/>

      </flow>

That’s it and we are done!

As we can see here in our flow, we have used:

<mule-ss:authorization-filter requiredAuthorities="ROLE_ADMIN"/> 

Which gives access privilege to the users which have role as ROLE_ADMIN.   

Also, we can see that the Java class will be creating the database tables at the time of the application's start and will insert the values in the table. If the database table already exists, it will simply skip the steps.   

Testing Our Application

When the application is deployed in the Mule server, for the first time the Java class will create the required database tables and insert the data if the tables do not exist.

If the tables are already there in the database and the values are already initialized in the table, then it will just skip the step.

Once the application gets deployed, we can see the table’s details in the MS-SQL database as below:

users

user_roles

We can see here, there are 2 users in users table: Anirban and Ajit

And the table user_roles contains the roles of each users. Here the user Anirban has both the roles:  ROLE_ADMIN and ROLE_USER so having both the privileges.

We will be using the REST Client to test our application with the URL: http://localhost:8081/test

And we need to set the credentials as follows:

We can see above, we are setting the credentials as Ajit. After we hit the service, we will get the response as follows:

We can see the Authentication failed and the reason in our Mule flow is that we have given access privilege to the users who have role as ROLE_ADMIN.     

Since user Ajit has a role of ROLE_USER, he is not privileged to access the application.   

Now, we will test the application again with the credential of user Anirban who has both the role as ROLE_ADMIN as well as of ROLE_USER: 

And this time, we will get the following response and will be:

So you can see that since this user has a role of ROLE_ADMIN, he will have the access of the application.

With this, we can secure the application based on user credential and their roles.

Conclusion

With the above use case, we can realize easily that it’s very easy to implement the Spring JDBC security with our Mule application. The only thing we need to do to create the Database table and store the user and their role information in the respective table so that the application authentication can be done against those values. So, here in the application we already had created a Java class that will do the task for us and initialize the tables with all the required values at the start of the application.