How To Create an OpenOffice.org Database Log On

Adding User Accounts and Passwords to a Base Database

Jun 23, 2009 Mark Alexander Bain

When looking an OpenOffice.org database there is no way of adding user accounts. Not directly, anyway. The secret is to use a little bit of SQL.

At some point in their lives every OpenOffice.org Base developer will start to consider the security of their database. For example they may wish to:

create individual user accounts
only allow certain users to update the data in some tables, whilst others may only read the data

And, of course, they will find that there is no obvious way of doing this. That's because OpenOffice.org Base does not have its own, built in security. Instead it relies on the fact that:

the operating system (for example Linux or Windows) has (or should have) its own user accounts
back-end databases (such as MySQL) have their own security

However, it's this last point that enables the OpenOffice.org Base developer to introduce some security. They can make use of the fact that OpenOffice.org Base is not a database in its own right. It actually uses an embedded database. The secret is, therefore, to start looking at the security of this embedded database. And that embedded database is HSQLDB. Once the database developer has realized that the OpenOffice.org Base database is actually an embedded HSQLDB database then they will also realized that they will be able to use SQL to create user accounts.

The Default HSQLDB User account

It may surprise the database developer to learn that OpenOffice.org Base already has a user account. This user account (named SA) is used by OpenOffice.org to access the embedded HSQLDB but is invisible to the user because:

it has no password
it has administrator level permissions, meaning that the user can create tables, queries, etc.

It is vital that the developer does nothing to this account. For example:

they must not add a password to the account
they must not delete (or drop) the account

If they do either of these then the database will become unusable. The answer is to leave that account alone and to start adding new ones, and it's important that the first account is set as an administrator.

Adding an Administrator Account to OpenOffice.org Base

There is no custom GUI (Graphical User Interface) for adding new user accounts. The developer must, therefore, use Base's SQL interface. They access this by clicking on:

Tools
SQL...

It's then just a matter of entering the SQL for creating the new user (as shown in figure 1 at the bottom of this article):

create user admin password admin_user admin

The “admin” key word ensures that the user account is created with administrator privileges. This is necessary because any accounts created without this will have a default read only status.

Enforcing User Passwords in OpenOffice.org Base

Unfortunately Base will not yet recognize the new account. If the database is closed and then opened again, it will still use the default SA account. The database designer must edit a background database file in order for the password protection to be invoked. The process is quite simple:

close the database
obtain a file archive tool such as 7-Zip
open the database using the file archive tool

The developer will now see a number of files and sub-directories, however they' re only interested in one of them

content.xml

The next stage is to edit this file and to look for :

<db:login db:is-password-required="false"/>

This (as shown in figure 2) should be changed to:

<db:login db:is-password-required="true"/>

The database is now almost ready for use and so the developer must:

save the altered content.xml back into the database archive
close the file archive tool
reopen the database with OpenOffice.org Base

The user will now be presented with a request for a user name and password (as shown in figure 3). It's also worth noting that anyone accessing the database via Writer or Calc will see the same request (as shown in figure 4).

Limitations of the OpenOffice.org Base Passwords

It's important to remember:

that the password protection can be turned off again by editing the content.xml file and changing the “is-password-required” property back to false
if Base is opened and the password entered then the data will be available to Writer or Calc without the password (since it's all counted as a single session). This means that if Base is closed then the session is still available to Writer and Calc. The session only ends once OpenOffice.org is completely closed

However, even with these limitations this provides a simple, but effective, way of protecting the data in an OpenOffice.org Base database from any indiscriminate misuse by a typical user.

The copyright of the article How To Create an OpenOffice.org Database Log On in Computer Programming is owned by Mark Alexander Bain. Permission to republish How To Create an OpenOffice.org Database Log On in print or online must be granted by the author in writing.

How To Create an OpenOffice.org Database Log On	Figure 1: The OpenOffice Base SQL GUI	Figure 2 Setting the Database Password
Figure 3: A Base Database with a User Login	Figure 4: Accessing Base from Writer