Wednesday, July 25, 2012

JAX-RS Tip of the Day: Basic Authentication with JDBC


Unless you have a public API like a weather service, or perhaps barometric pressure measurements. You will likely need some form of authentication, and authorization for your service. A tried and tested mechanism is to use JDBC Realm based authentication. In this example I will create  a set of database tables on Apache Derby, set up the security realm on GlassFish, and configure basic authentication on a RESTful web service. The majority of the work will be done using the NetBeans IDE 7.2.



The first thing we need to do is to set up our database tables which we will use for authentication. These tables can contain more information, but in my example I will keep them simple.

Creating Tables

First we will need to create a Users table which will contain our username and password. Using the sample database in NetBeans do the following:
  1. Select the Services Window, and open the Databases selection
  2. Right click on the Java DB icon, and start the server if it is not already started
  3. Right click on the sample database connection: jdbc:derby://localhost:1527/sample [app on APP] and connect.
  4. Right click on the sample connection and select Execute Command.
  5. Execute the create table commands and create index commands below.
CREATE TABLE users ( username varchar(255) NOT NULL, password varchar(255) DEFAULT NULL, PRIMARY KEY (username) );
CREATE TABLE groups ( username varchar(255) DEFAULT NULL, groupname varchar(255) DEFAULT NULL);
CREATE INDEX groups_users_idx ON groups(username ASC);
Create Tables

Add Users and Groups

We need to add at least one user and group to our table. Since I am using GlassFish as the container, I will use SHA-256 to hash my password. That way it is not visible in plain text.
  1. Right click on our new USERS table, and select View Data
  2. Click on the Insert Records Icon
  3. Add a user, and add a SHA-256 hash of the password.
    Note:An online generator can be found at SHA-256 hash calculator
  4. Repeat the same process as above to open the GROUPS table
  5. Add the username and a group called users
That completes all we need for our JDBC authentication.

GlassFish JDBC Realm

Using the NetBeans IDE perform the following:
  1. Go to the Services window and expand the Servers selection.
  2. Right click on GlassFish 3.1.2 server and select Start.
  3. Right click and select View Domain Admin Console.
  4. On the Admin console web page on the tree on the left select Configurations » server-config » Security » Realms.
  5. Add a new realm called jdbc with the following properties:
    • Name: jdbc
    • Class Name:
    • JAASContext: jdbcRealm
    • JNDI: jdbc/sample
    • User Table: users
    • User Name Column: username
    • Password Column: password
    • Group Table: groups
    • Group Table User Name Column: username
    • Group Name Column: groupname
    • Database User: app
    • Database Password: app
    • Digest Algorithm: SHA-256
    • Encoding: Hex
    • Charset: UTF-8

    Note: the parameters are case sensitive.
  6. Navigate to Configurations » server-config » Security
  7. Change the Default Realm to jdbc
  8. Check the Default Principal To Role Mapping checkbox to enabled
  9. Click Save and Restart server.
The security mapping configuration for automatic mapping makes it so that our application will not require a glassfish-web.xml file as part of our deployment.

JAX-RS Application

Finally we have completed all of the requirements on the server side for securing our applications. This security mechanism can be used by more than the application we will are preparing to deploy. We need to set up the security constraints in our web.xml file as shown below. If you have downloaded the code you can simply open it in NetBeans and examine it.


Using NetBeans, you can simply run the application and it will prompt you for an application server. Select the current GlassFish server we have set-up, and it will deploy in a few seconds. You will come to a index.jsp page.
Click on the link for the application.wadl, or navigate to http://localhost:8080/secure-jdbc-rest-service/resources/example and you will be prompted to login. Once you login, you should get this message from the REST service.
This is an example message


peraxel said...

Thanks for a good article!
I've been using the standard JDBC Realm based authentication in a couple of projects, but I think it has a major drawback.. It's vulnerable due to the fact that it doesn't support individual salting of the passwords.
Do you know of any alternatives that support individual salting? I've come across 'Flexible JDBC Realm' ( which looks promising.

John Yeary said...

My friend Markus Eisle has posted a great blog post on using salted passwords.

I told him that I wished we would have chatted before I posted this. I would have included his information.

Unknown said...

Hi, I am new to REST.
Thanks for this article, but I can't get right result after I did all steps word by word...seems the app did not run...Could you help me?

adrielzera said...

is it possible with tomcat instead?

John Yeary said...

A similar process would work on Tomcat, but you need to do a lot more configuration. You would need to set-up the database configuration, and security configuration on Tomcat. Additionally, you would need to make sure that JAX-RS is installed so that it works on Tomcat.

Again, the same idea would work on Tomcat, you are just have to do a lot more foot work.

Popular Posts