Custom Database Integration Guide Of Openfire

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.

• jdbcProvider.driver -- the class name of the JDBC driver used to connect to your custom database. The driver must also be in the Openfire classpath (for example, by placing it into the "lib/" directory of your Openfire installation. See the database guide for common driver names for major databases.
• jdbcProvider.connectionString -- the full connection string for the database. Please consult your database driver documentation for syntax. Warning: it's common for connection string to contain "&" characters. That character has special meaning in XML, so you should escape it using "&".

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.

• provider.auth.className -- set the value to org.jivesoftware.openfire.auth.JDBCAuthProvider.
• jdbcAuthProvider.passwordSQL -- the SQL String to select a user's password. The SQL statement should contain a single "?" character, which will be dynamically replaced with a username when being executed.
• jdbcAuthProvider.passwordType -- the type of the password. Valid values are
  • "plain" (the password is stored as plain text)
  • "md5" (the password is stored as a hex-encoded MD5 hash)
  • "sha1" (the password is stored as a hex-encoded SHA-1 hash)
  • "sha256" (the password is stored as a hex-encoded SHA-256 hash)
  • "sha512" (the password is stored as a hex-encoded SHA-512 hash)
  If this value is not set, the password type is assumed to be plain.

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 integrationyou must also enable authentication integration (see above). Use the following settings toenable user integration.

• provider.user.className -- set the value to org.jivesoftware.openfire.user.JDBCUserProvider.
• jdbcUserProvider.loadUserSQL -- the SQL statement to load the name and email address of a user (in that order) given a username. The SQL statement should contain a single "?" character, which will be dynamically replaced with a username when being executed.
• jdbcUserProvider.userCountSQL -- the SQL statement to load the total number of users in the database.
• jdbcUserProvider.allUsersSQL -- the SQL statement to load all usernames in the database.
• jdbcUserProvider.searchSQL -- the SQL statement fragment used to search your database for users. the statement should end with "WHERE" -- the username, name, and email fields will then be dynamically appended to the statement depending on the search. If this value is not set, searching will not be enabled.
• usernameField -- the name of the username database field, which will be used for searches.
• nameField -- the name of the name database field, which will be used for searches.
• emailField -- the name of the email database field, which will be used for searches.

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 integrationyou must also enable authentication integration; you'll also likely want to enable userintegration (see above). Use the following settings to enable group integration.

• provider.group.className -- set the value to org.jivesoftware.openfire.group.JDBCGroupProvider.
• jdbcGroupProvider.groupCountSQL -- the SQL statement to load the total number of groups in the database.
• jdbcGroupProvider.allGroupsSQL -- the SQL statement to load all groups in the database.
• jdbcGroupProvider.userGroupsSQL -- the SQL statement to load all groups for a particular user. The SQL statement should contain a single "?" character, which will be dynamically replaced with a username when being executed.
• jdbcGroupProvider.descriptionSQL -- the SQL statement to load the description of a group. The SQL statement should contain a single "?" character, which will be dynamically replaced with a group name when being executed.
• jdbcGroupProvider.loadMembersSQL -- the SQL statement to load all members in a group. The SQL statement should contain a single "?" character, which will be dynamically replaced with a group name when being executed.
• jdbcGroupProvider.loadAdminsSQL -- the SQL statement to load all administrators in a group. The SQL statement should contain a single "?" character, which will be dynamically replaced with a group name when being executed.

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 FROM myGroupUsers WHERE username=?</userGroupsSQL>
       <descriptionSQL>SELECT groupDescription FROM myGroups WHERE groupName=?</descriptionSQL>
       <loadMembersSQL>SELECT username FROM myGroupUsers WHERE groupName=? AND isAdmin='N'</loadMembersSQL>
       <loadAdminsSQL>SELECT username FROM myGroupUsers WHERE groupName=? AND isAdmin='Y'</loadAdminsSQL>
  </jdbcGroupProvider>
  ...
</jive>

MySQL

JDBC Drivers

The JDBC driver for MySQL is bundled with Openfire, so you do not need to download and install a separate driver.

In the Openfire setup tool, use the following values:

• driver: com.mysql.jdbc.Driver
• server: jdbc:mysql://[YOUR_HOST]/[DATABASE_NAME]

where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server.In many cases localhost is a suitable value for [YOUR_HOST] whenyour database is running on the same server as your webserver.

Setup Instructions

1. Make sure that you are using MySQL 4.1.18 or later (5.x recommended) ¹.
2. Create a database for the Openfire tables:
   mysqladmin create [databaseName]
   (note: "databaseName" can be something like 'openfire')
3. Import the schema file from the resources/database directory of the installation folder:
   Unix/Linux: cat openfire_mysql.sql | mysql [databaseName];
   Windows: type openfire_mysql.sql | mysql [databaseName];
4. Start the Openfire setup tool, and use the appropriate JDBC connectionsettings.

¹ Character fields larger than 255 are not supported by versions prior to MySQL 4.1.18. If you cannot upgrade MySQL to the latest version, you will then need to change the database scripts. In particular, replace VARCHAR(1024) with VARCHAR(255) in the resources/database/openfire_mysql.sql script.

Character Encoding Issues

MySQL does not have proper Unicode support, which makes supporting data in non-Western languages difficult. However, the MySQL JDBC driver has a workaround which can be enabled by adding ?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8 to the URL of the JDBC driver. You can edit the conf/openfire.xml file to add this value.

Note: If the mechanism you use to configure a JDBC URL is XML-based, you will need to use the XML character literal & to separate configuration parameters, as the ampersand is a reserved character for XML.

Further Help

If you need help setting up MySQL, refer to the following sites:

MySQL Reference Manual