OA4MP: MySQL Configuration

Scope: client and server, all versions.

This configures a Apache Derby store. Derby is a lightweight relational database written in Java. It is quite easy to set up and run and is full-featured, with support for standard SQL. It is much less work than one of the big databases (e.g. MySQL) to maintain.

As of version 5.5, OA4MP support Derby in various modes:

  • standard If you have Derby installed and running on your system as you SQL database server, simply add an OA4MP database with tables and use that.
  • file If you want local storage, use this. You may supply a user name and password to lock it down, and it will be created and managed by the system. It resides locally. The downside (this is a limitation of Derby itself) is that only a single process can access the database at once.
  • memory This creates the database and it resides wholly in memory. It is again managed by OA4MP. This does not persist between server restarts.

File mode

In this case, you do not need to have Derby installed at all. OA4MP will handle the details. All you need to do is set up access to it. If you do not supply credentials, default credentials will be used. Since there is no external access from outside the system, as long as you have the correct permissions set for the directory where the file store resides it should be secure.

Attribute Required? Default Description
createScript N -- If you do not create this and want the system to do so, then specify the script here that contains all of the tables. If you do not specify it, the system will use whatever the current release version of this script is automatically (which is included in the OA4MP distro.) This must be the full path on your system.
path Y -- The physical path to the database file store. Note that this is the directory where the database resides. If you do not specify it explicitly as the database property.
storeType Y file This is required and is what tells OA4MP to use Derby as file store mode. If it is missing, the assumption is that it is a standard database. It may also have the value of memory for a memory store.

Notes

  • The database property if present is the full path to the database. If it is missing, then it is constructed from the path property as path+"/derby". Note that if this directory exists, it is assumed that the database has been created and resides there. If you want the system to create the database, be sure that directory does not exist.
  • The path property is not optional. Generally it is suggested to be the parent directory of the database (and will be if you do not specify the database property).
  • The create script is simply what you would type at the command line. A very simple-minded parser is invoked that breaks it up into commands and issues those sequentially. If you have a sophisticated script for creation, you should probably just create the database (same steps for a standard database, see below) and just use that.

Example setting the username, password and boot password. Note that since this is a database, the standard parameters such as schema are still supported.

<derby username="oa4mp"
       password="6eXSbO_W"
       bootPassword="o7MtXykd"
       storeType="file"
       path="/opt/oa4mp/test"
       schema="oauth2">
    <!-- all the stores -->
<derby/>

Most minimal configuration

    <derby storeType="file"
           path="/opt/oa4mp"
           >
    <!-- all the stores -->
    </derby>

This creates a file store which resides at "/opt/oa4mp/derby".

Memory Mode

In this mode, the database is created and resides wholly in memory. It is not persistent between server restarts.

Attribute Required? Default Description
createScript N -- If you do not create this and want the system to do so, then specify the script here that contains all of the tables. If you do not specify it, the system will use whatever the current release version of this script is automatically (which is included in the OA4MP distro.) This must be the full path on your system.
storeType Y memory This is required and is what tells OA4MP to use Derby in memory store mode. If it is missing, the assumption is that it is a standard database.

Notes

  • The database property is the name of the database. This is not a path. If it is not supplied, the name "derby" is used.

Minimal example

    <derby storeType="memory">
    <!-- all the stores -->
    </derby>

A more elaborate example

    <derby storeType="memory"
           database="oa4mp"
           createScript="/opt/oa4mp/etc/oa4mp-derby.sql"
           schema="oauth2">
        <!-- all the stores -->
    </derby>

In this case, a database named oa4mp is created from a custom script. The schema is specified (which is used in the script.

Standard mode

Generally the schema, tablename and such use the default. Any option prefixed with a * in the table will have its default used. Note that there may be many stores for a configuration if each of them, e.g. requires different connection information.

Attribute Required? Default Description
username Y N/A The name of the user to use
password Y N/A The password for the user
bootPassword Y N/A The password to unlock the database.
driver N current The JDBC driver
database Y N/A The path to the database.
schema N The schema for the database
parameters N (none) An optional extra set of parameters to pass to the JDBC driver. This is of the form key0=value0&key1=value1&key2=value2... Note that each key/value pair is separated with an ampersand. Note that OA4MP does all connections using UTF8 so that parameter is always added.


The content of the store is specified in one of the content tags.

If you are installing Derby, you need to get the tools from their website (unless you have some form of package manager) and install that. There are install instructions for settig up your system.

Setting up a Derby database

This comment section tells how to set up an Apache Derby database to work with OA4MP.

Create the directory to where you want derby to create the database. This directory should be empty, since Derby will create everything on your behalf -- and might refuse to do anything if the directory has content. Make the following substitutions below:

Attribute Description
DB_NAME the entire path to this database
DB_PASSWORD password to the database
USER_NAME name of the user (created below)
USER_PASSWORD password for user
SCHEMA schema for the database

Note 1

If you want your database to live in

         /opt/oauth2/var/derby/oa4mp

you would create

    /opt/oauth2/var/derby

with nothing in it and the DB_NAME is then

    /opt/oauth2/var/derby/oa4mp

I.e., the last directory in this path is what Derby creates.

Note 2

In Derby, the database lives in a directory. This means that unless certain precautions are taken, it is completely insecure. The setup below mitigates this.

  1. Puts a password on the entire database so it cannot be read from the disk
  2. Sets a user and password to access the database. These are stored in the database, hence step 1 to lock the whole thing down.
  3. All database access from OA4MP is via the so-called embedded driver, so no network traffic is needed.

One-time install instructions

Install derby, probably with a package manager like synaptic or yum.

Start derby with 

  ij

Then issue the following commands. This sets up the database and will create the user above (Note that the user name and password are set as Java properties, so do substitute). When he datqbase is shut down, these values are stored for subsequent usage and you then put them in the JDBC connection string. Even though the user does not exist yet, you must connect with the user name so that they are the owner of the database. 

  connect 'jdbc:derby:DB_NAME;create=true;dataEncryption=true;bootPassword=DB_PASSWORD;user=USER_NAME';
  call syscs_util.syscs_set_database_property('derby.connection.requireAuthentication', 'true');
  call syscs_util.syscs_set_database_property('derby.authentication.provider', 'BUILTIN');
  call syscs_util.syscs_set_database_property('derby.user.USER_NAME', 'USER_PASSWORD');
  call syscs_util.syscs_set_database_property('derby.database.propertiesOnly', 'true');
  call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true');

Optional test

If you want be sure it works, create the schema as follows:

  create schema oauth2;
  show schemas;

And a bunch of schemas will be displayed, including oauth2. This means everything worked. You don't need to issue the create schema command below.

At this point, exit Derby normally. Initial setup is done. You must connect again as the user that runs this because creating the tables below will automatically assign the current user as the table owner, so no other permissions (which can get complicated) are needed. 

  exit;

Now connect to it with the following from the command line after restarting ij:

  connect 'jdbc:derby:DB_NAME;user=USER_NAME;password=USER_PASSWORD;bootPassword=DB_PASSWORD';

and either paste in the rest of this file OR just run the whole thing from inside ij

    run '/full/path/to/oa4mp-derby.qdl';

At this point, your database is ready for use.

Example

Let us say that you set up your Derby database with the following information

Attribute Description
DB_NAME /opt/oa2/var/storage/my_db
DB_PASSWORD mairzy_doats
USER_NAME oa4mp
USER_PASSWORD dozey_doats
SCHEMA oauth2

Then the configuration of the derby element for all stores would be 

    <derby username="oa4mp"
           password="dozey_doats"
           bootPassword="mairzy_doats"
           database="opt/oa2/var/storage/my_db"
           schema="oauth2">
        <transactions/>
        <clients/>
        <clientApprovals/>
        <permissions/>
        <adminClients/>
        <txStore/>
        <voStore/>
    </derby>