Sun OpenSSO Express 8 includes the option to use MySQL as the OpenSSO user data store.

Caution Using MySQL as the OpenSSO user data store is an early access (EA) feature of OpenSSO Express 8. This feature has not undergone extensive testing by Sun and therefore is recommended only for prototype, proof of concept (POC), or developer deployments that have a small number of users. This feature is not currently recommended for production deployments.

Before You Begin

You should be familiar with:

• OpenSSO Enterprise or Express, including using the OpenSSO Administration Console.
• MySQL, including tasks such as creating tables and inserting data into tables.

Requirements for your deployment include:

• MySQL must be installed, running, and accessible to OpenSSO Express 8.
• The JDBC connector for MySQL must be installed and available to the opensso.war file at runtime.

Optional: Adding OpenSSO Users to a MySQL Database

This task is option. If you do not already have a MySQL database with an existing table with users data, perform this task to add an OpenSSO users table and users to your database.

This task uses the following files to create and populate OpenSSO users in your MySQL database. You can download these files from this article and then edit them to meet your needs.

• CreateUsersTable.sql creates a new table named opensso_users in a MySQL database. The columns in the opensso_users table correspond to the default user attributes when you create the Database Repository (Early Access) user data store in the OpenSSO Console.

  # Create the opensso_users table to configure MySQL as the OpenSSO user data store.
  
  DROP TABLE IF EXISTS opensso_users;
  
  CREATE TABLE opensso_users (
   user_pk mediumint(8) unsigned NOT NULL auto_increment,
   uid varchar(60) NOT NULL,
   givenname varchar(60) NOT NULL,
   sn varchar(60) NOT NULL,
   cn varchar(60) NOT NULL,
   userpassword varchar(60) NOT NULL,
   ChangePassword varchar(60) default NULL,
   mail varchar(60) default NULL,
   employeenumber varchar(60) default NULL,
   telephonenumber varchar(60) default NULL,
   manager varchar(60) default NULL,
   postaladdress varchar(100) default NULL,
   inetuserstatus varchar(60) NOT NULL,
   iplanet_am_user_account_life varchar(60) default NULL,
   iplanet_am_user_auth_config varchar(60) default NULL,
   iplanet_am_user_alias_list varchar(60) default NULL,
   preferredlocale varchar(60) default NULL,
   iplanet_am_user_success_url varchar(60) default NULL,
   iplanet_am_user_failure_url varchar(60) default NULL,
   iplanet_am_user_password_resetoptions varchar(100) default NULL,
   iplanet_am_user_password_reset_question_answer varchar(60) default NULL,
   iplanet_am_user_password_reset_force_reset varchar(60) default NULL,
   sunIdentityMSISDNNumber varchar(30) default NULL,
   PRIMARY KEY  (user_pk),
   UNIQUE KEY uid (uid)
   );

  Note – In OpenSSO Express 8, your users' attribute names must exist in a single table in your MySQL database (unless you are implementing this option by writing your own class for the database data access object (DAO) plug-in).

• InsertUsers.sql inserts users into the opensso_users table. Edit this file for the OpenSSO users you want to add.

# This file adds OpenSSO users to the opensso_users table to test MySQL as the
# OpenSSO Express user data store. Edit the file to add users as needed.

INSERT INTO opensso_users (uid, givenname, sn, cn, userpassword, inetuserstatus)
VALUES ('uid1', 'givenname1', 'surname1', 'completename1', 'uid1pw','Active')
VALUES ('uid2', 'givenname2', 'surname2', 'completename2', 'uid2pw','Active')
;

To Add OpenSSO Users to a MySQL Database

Note – This task is optional. Perform this task only if you want to use the Sun sample schema instead of your own users' schema.

1. Make sure that the MySQL database is running.
2. Download the CreateUsersTable.sql file.
3. Create the opensso_users table in the MySQL database using the CreateUsersTable.sql file.
   If you wish, first edit this file to add or remove columns; however, you must be able to map the table columns to the attribute names in the MySQL user data store in the OpenSSO Console.
4. Download the InsertUsers.sql file.
5. Edit the InsertUsers.sql file and add some OpenSSO users.
6. Add the new users to the opensso_users table in the MySQL database using the InsertUsers.sql file.

Installing and Configuring OpenSSO Express 8

To Install and Configure OpenSSO Express 8

1. Install OpenSSO Express 8 by deploying the opensso.war file.
2. Configure OpenSSO Express 8 using either the GUI or command-line Configurator.
   Note – The OpenSSO Express 8 GUI or command-line Configurator does not have the option to specify MySQL as the User Data Store. Instead, after you finish running the Configurator, you must create a user data store for MySQL in the OpenSSO Console, as described in the next section.

Creating an OpenSSO Data Store for MySQL

Perform this task in the OpenSSO Administration Console.

To Create an OpenSSO Data Store for MySQL

1. Log in to OpenSSO Console as amadmin.
2. Click Access Control, realm-name, Data Stores, and then New.
3. On the Select Type of Data Store page:
   • Type the Name of the data store. For example, MySQLDataStore.
   • Check Database Repository (Early Access).
   • Click Next.
4. On the New Data Store - Database Repository (Early Access) page:
   • Attribute Name Mapping: Map each column name in the OpenSSO users table in your MySQL database to the corresponding OpenSSO attribute name.
   • Database Repository Plugin Class Name: Accept the default value unless you are writing your own class.
   • Database Plug-in Supported Types and Operations: Accept the default values.
   • Database Data Access Object Plugin Class Name: Use Accept default value unless you are writing your own class.
   • Connection Type: Accept the default (Connection is retrieved via programmatic connection).
   • Database DataSource Name: Accept the default name.
   • JDBC Driver Class Name: Specify the name of the driver class you installed for MySQL.
   • Password for Connecting to Database: Enter and confirm the password to connect to the MySQL database.
   • JDBC Driver URL: Specify the URL for the JDBC driver you installed for MySQL.
   • Connect This User to Database: Specify the user who will connect to the MySQL database.
   • Database User Table Name: Specify the name of the OpenSSO users table, if you are using a name other than opensso_users.
   • List of User Attributes Names in Database: Specify all user attribute column names in the OpenSSO users table in the MySQL database.
   • User Password Attribute Name: Specify the column name in the MySQL database.
   • Attribute Name of User Status: Specify the column name in the MySQL database.
   • User ID Attribute Name: Specify the column name in the MySQL database.
   • User Status Active Value: Specify the value used in the MySQL database.
   • User Status In-Active Value: Specify the value used in the MySQL database.
   • Maximum Results Returned from Search: Accept the default.
   • Users Search Attribute in Database: Accept the default.
5. Accept the default values for the following fields, because these attributes are not supported in OpenSSO Express 8. (To use these attributes, you must write your own class that extends the Sun default implementation.)
   • Database Membership table name
   • Membership ID Attribute Name
   • Membership Search Attribute in Database
6. Click Finish.

Later, if you need to make additional configuration changes to the MySQL data store, click Access Control, realm-name, Data Stores, mysql-data-store-name, and then make your changes.

Optional – If you are not using the OpenSSSO default embedded data store, you can remove it:

1. In the OpenSSO Administration Console, click Access Control, realm-name, and then Data Stores.
2. Check embedded and then click Delete.

Testing Your MySQL User Data Store

To Test Your MySQL User Data Store

1. Log in to OpenSSO Console as amadmin.
2. Click Access Control, realm-name, and then Subjects.
   You should see the OpenSSO users from the MySQL database.
3. Click on specific users to see the details.
4. Create, update, and delete several of the users.
5. In the MySQL database and check the users in the OpenSSO users table to see if the changes were made from the previous step.

Top of Page