« Back to documentation index

Introduction

This document provides instructions for integrating Openfire authentication, users, and groups with your custom database tables. This is useful when your users already have accounts in an external system and you do not wish to duplicate those accounts in Openfire. If your user information is available via an LDAP directory rather than custom database tables, see the LDAP guide.

Simple integration with a custom database lets users authenticate using their existing username and password. Optionally, you can configure Openfire to load user profile and group information from your custom database. Any group in Openfire can be designated as a shared group, which means that you can pre-populate user's rosters using groups.

Background

The integration requires that you enter customized database queries to access your database. You'll need to be familiar with your database table structure and simple SQL. Your custom database can be a different database on a different server from the Openfire database -- you'll enter database connection information as part of the configuration.

Configuration

In order to configure your server to integrate with your custom database tables:

  1. Stop Openfire.
  2. Edit conf/openfire.xml in your Openfire installation folder as described below using your favorite editor.
  3. Restart Openfire.

Database Connection Settings

You must specify the connection string for your database as well as the JDBC driver.

Below is a sample config file section (note: the "..." sections in the examples indicate areas where the rest of the config file would exist): 

<jive>
  ...
  <jdbcProvider>
    <driver>com.mysql.jdbc.Driver</driver>
    <connectionString>jdbc:mysql://localhost/dbname?user=username&amp;password=secret</connectionString>
  </jdbcProvider>
  ...
</jive>

Authentication Integration

The simplest possible integration with a custom external database is authentication integration. Use the following settings to enable authentication integration.

Below is a sample config file section: 

<jive>
  ...
  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
  </provider>
  <jdbcAuthProvider>
     <passwordSQL>SELECT password FROM user_account WHERE username=?</passwordSQL>
     <passwordType>plain</passwordType>
   </jdbcAuthProvider>
   ...
  </jive>

You'll most likely want to change which usernames are authorized to login to the admin console. By default, only the user with username "admin" is allowed to login. However, you may have different users in your LDAP directory that you'd like to be administrators. The list of authorized usernames is controlled via the admin.authorizedUsernames property. For example, to let the usersnames "joe" and "jane" login to the admin console:

    <jive>
      ...
      <admin>
        ...
        <authorizedUsernames>joe, jane</authorizedUsernames>
      </admin>

      ...
    </jive>

User Integration

Optionally, Openfire can load user data from your custom database. If you enable user integration you must also enable authentication integration (see above). Use the following settings to enable user integration.

Below is a sample config file section. Note that the single provider section must include all providers that should be configured: 

<jive>
  ...
  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>
  </provider>
  <jdbcAuthProvider>
     <passwordSQL>SELECT password FROM user_account WHERE username=?</passwordSQL>
     <passwordType>plain</passwordType>
  </jdbcAuthProvider>
  <jdbcUserProvider>
     <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL>
     <userCountSQL>SELECT COUNT(*) FROM myUser</userCountSQL>
     <allUsersSQL>SELECT username FROM myUser</allUsersSQL>
     <searchSQL>SELECT username FROM myUser WHERE</searchSQL>
     <usernameField>username</usernameField>
     <nameField>name</nameField>
     <emailField>email</emailField>
  </jdbcUserProvider>
   ...
 </jive>

Group Integration

Openfire can load group data from your custom database. If you enable group integration you must also enable authentication integration; you'll also likely want to enable user integration (see above). Use the following settings to enable group integration.

Below is a sample config file section. Note that the single provider section must include all providers that should be configured: 

<jive>
  ...
  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>
    <group>
      <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>
    </group>
  </provider>
  <jdbcAuthProvider>
     <passwordSQL>SELECT password FROM user_account WHERE username=?</passwordSQL>
     <passwordType>plain</passwordType>
  </jdbcAuthProvider>
  <jdbcUserProvider>
     <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL>
     <userCountSQL>SELECT COUNT(*) FROM myUser</userCountSQL>
     <allUsersSQL>SELECT username FROM myUser</allUsersSQL>
     <searchSQL>SELECT username FROM myUser WHERE</searchSQL>
     <usernameField>username</usernameField>
     <nameField>name</nameField>
     <emailField>email</emailField>
  </jdbcUserProvider>
  <jdbcGroupProvider>
       <groupCountSQL>SELECT count(*) FROM myGroups</groupCountSQL>
       <allGroupsSQL>SELECT groupName FROM myGroups</allGroupsSQL>
       <userGroupsSQL>SELECT groupName FORM myGroupUsers WHERE username=?</userGroupsSQL>
       <descriptionSQL>SELECT groupDescription FROM myGroups WHERE groupName=?</descriptionSQL>
       <loadMembersSQL>SELECT username FORM myGroupUsers WHERE groupName=? AND isAdmin='N'</loadMembersSQL>
       <loadAdminsSQL>SELECT username FORM myGroupUsers WHERE groupName=? AND isAdmin='Y'</loadAdminsSQL>
  </jdbcGroupProvider>
  ...
</jive>