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:
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
The next stage is to edit this file and to look for :
This (as shown in figure 2) should be changed to:
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