DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • The Ultimate API Development Guide: Strategy, Tools, Best Practices
  • Automating the Migration From JS to TS for the ZK Framework
  • Zero Trust Network for Microservices With Istio
  • Generics in Java and Their Implementation

Trending

  • The Ultimate API Development Guide: Strategy, Tools, Best Practices
  • Automating the Migration From JS to TS for the ZK Framework
  • Zero Trust Network for Microservices With Istio
  • Generics in Java and Their Implementation
  1. DZone
  2. Data Engineering
  3. Databases
  4. Secure Your Mule Application With Spring JDBC

Secure Your Mule Application With Spring JDBC

Take advantage of Mule and Spring being so tightly integrated and secure your application with the Spring JDBC.

Anirban Sen Chowdhary user avatar by
Anirban Sen Chowdhary
·
Dec. 20, 16 · Tutorial
Like (2)
Save
Tweet
Share
41.67K Views

Join the DZone community and get the full member experience.

Join For Free

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

Image title

user_roles

Image title

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

Image title

And we need to set the credentials as follows:

Image title

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

Image title

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: 

Image title

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

Image title

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.

application Spring Security Database Spring Framework

Opinions expressed by DZone contributors are their own.

Trending

  • The Ultimate API Development Guide: Strategy, Tools, Best Practices
  • Automating the Migration From JS to TS for the ZK Framework
  • Zero Trust Network for Microservices With Istio
  • Generics in Java and Their Implementation

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: