Multiple Derby Network Servers on the same Host

Abstract

Suppose you want to start multiple Derby network servers on the same host. They need to be listening on different ports and ideally store their data in different locations. The listings below show Linux bash and Windows batch scripts to configure starting a Derby network server. In this example, the Derby network server will listen on port 1110. Each Derby network server will also have its own file system location to store its databases.

Disclaimer

This post is solely informative. Critically think before using any information presented. Learn from it but ultimately make your own decisions at your own risk.

Requirements

I did all of the work for this post using the following major technologies. You may be able to do the same thing with different technologies or versions, but no guarantees.

• Apache Derby 10.14.1.0
• Java zulu11.1+23-ea-jdk11

I am not going to go through the process of downloading and installing these technologies. I’ll leave that as an exercise for you.

Linux bash scripts

Here are the Linux bash scripts to configure starting a Derby network server. We’ll look at the details of each file. First we’ll set the environment.

Listing 1 - setenv.sh

#!/bin/bash
 
export DERBY_HOME=/home/derby/opt/derby
export PATH="$DERBY_HOME/bin:$PATH"
echo "DERBY_HOME=$DERBY_HOME"

export JAVA_HOME=/home/derby/opt/java
echo "JAVA_HOME=$JAVA_HOME"

export NS_HOME=/var/local/derby/1110
mkdir -p $NS_HOME
echo "NS_HOME=$NS_HOME"

export NS_PORT=1110
echo "NS_PORT=$NS_PORT"

export NS_HOST=0.0.0.0
echo "NS_HOST=$NS_HOST"
 
export DERBY_OPTS=""
export DERBY_OPTS="$DERBY_OPTS -Dderby.drda.host=$NS_HOST"
export DERBY_OPTS="$DERBY_OPTS -Dderby.drda.portNumber=$NS_PORT"
export DERBY_OPTS="$DERBY_OPTS -Dderby.system.home=$NS_HOME"

DERBY_HOME is self explanatory: it’s where Derby is unzipped (installed). Add Derby’s bin directory to the PATH.

JAVA_HOME is self explanatory: it’s where Java is unzipped (installed). Add Java’s bin directory to the PATH.

NS_HOME is “Network Server Home”. This is the directory the Derby network server will use to store its configuration and databases. Whenever a new database is created on this Derby network server, a new sub-directory will be created under NS_HOME for the new database. This allows multiple Derby network servers running on the same host to keep their data separate.

NS_PORT is self explanatory: it’s the port the Derby network server uses to listen for connections. This allows multiple Derby network servers to run on the same host.

NS_HOST sets the network interface used by the Derby network server when listening for connections. By default, the Derby network server only listens for connections on the loopback address of 127.0.0.1. This default means clients must run on the same host as the network server - not very useful. By setting the host to 0.0.0.0, the Derby network server will listen for connections on any network interface on the host. If your Derby network server host has multiple network interfaces, NS_HOST should be set to the IP of one of those interfaces. Setting this value allows clients to be remote and run on any host.

DERBY_OPTS is the system property used to get all of the configuration options to Derby. It’s value is created by concatenating together the appropriate Derby system properties with their associated values.

Now that the environment is set, we can start the server.

Listing 2 - start.sh

#!/bin/bash

# Directory of the script
SD=$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )

# Source in common variables
source $SD/setenv.sh

startNetworkServer -noSecurityManager

SD is Script Directory. The evaluation determines the fully-qualified file system location of the start.sh script and assigns it to SD. This is useful when referencing other scripts.

source self explanatory: it sources in the environment configuration to run the Derby network server.

startNetworkServer -noSecurityManager starts the Derby network server. The DERBY_OPTS variable - set in setenv.sh - is used to configure the network server. The -noSecurityManager command line option needs some explanation. By default, Derby’s Java process runs with a limited security policy. I’ve found that this limited security policy gets in the way of database operations which I normally would just expect to work. By specifying -noSecurityManager, you run the Derby network server without any security policy. This may not be the ideal way for you to run a Derby network server. However, this blog is limited in scope to running the server. Search for my other blog on how to secure a Derby network server.

Now that the server is running, we need to stop it.

Listing 3 - stop.sh

#!/bin/bash

# Directory of the script
SD=$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )

# Source in common variables
source $SD/setenv.sh

stopNetworkServer

All of this is self explanatory. No further comments are needed for this script.

Windows batch scripts

Here are the Windows batch scripts to configure starting a Derby network server. We will not look at these files in additional details; details are included with the Linux bash scripts.

Listing 1 - setenv.cmd

@echo off
 
set DERBY_HOME=C:\Applications\Derby
set PATH=%DERBY_HOME%\bin;%PATH%
echo DERBY_HOME=%DERBY_HOME%

set JAVA_HOME=C:\Applications\Java
echo JAVA_HOME=%JAVA_HOME%

set NS_HOME=C:\Users\Derby\1110
md %NS_HOME% 2>NUL
echo NS_HOME=%NS_HOME%

set NS_PORT=1110
echo NS_PORT=%NS_PORT%

set NS_HOST=0.0.0.0
echo NS_HOST=%NS_HOST%
 
set DERBY_OPTS=-Dderby.drda.host=%NS_HOST%
set DERBY_OPTS=%DERBY_OPTS% -Dderby.drda.portNumber=%NS_PORT%
set DERBY_OPTS=%DERBY_OPTS% -Dderby.system.home=%NS_HOME%

Listing 2 - start.cmd

@echo off
call setenv.cmd
startNetworkServer -noSecurityManager

Listing 3 - stop.cmd

@echo off
call setenv.cmd
stopNetworkServer

Summary

That’s it. I hope you enjoyed learning how to run multiple Derby network servers on the same host.

Apache Derby Database Users and Permissions

Abstract

Apache Derby is awesome! Especially in a Microservices environment where the data for services (may) shrink and not require a heartier RDBMS. Derby is awesome because it’s so easy to use, especially when it come to users and permissions - you don’t need any! But, it may be the case you want to create an application-level user with limited permissions to use in Derby. The purpose of this blog is to document how to create application-level, limited permission users in Derby.

Disclaimer

This post is solely informative. Critically think before using any information presented. Learn from it but ultimately make your own decisions at your own risk.

Requirements

I did all of the work for this post using the following major technologies. You may be able to do the same thing with different technologies or versions, but no guarantees.

• Apache Derby 10.14.1.0
• Java 1.8.0_152_x64

I am not going to go through the process of downloading and installing these technologies. I’ll leave that as an exercise for you.

Run Derby Network Server

The first thing you must do is run a Derby network server. In my previous blog post titled Multiple Derby Network Servers on the same Host, I give detailed instructions on how to do this. Please refer to that blog post to run your own Derby network server.

Configure Derby Network Server

To configure the Derby network server, you need to create a derby.properties file. But where does the file go? It can go in a couple different places. Let’s take a look.

I’ll first assume that you ignored the Run Derby Network Server section above and instead are running Derby with all its defaults. If that’s the case, you probably started the network server by finding the %DERBY_HOME%\bin\startNetworkServer.bat file and double-clicking it. If you did this - highly not recommended - then Derby thinks the %DERBY_HOME%\bin directory is its system directory. You can confirm this by looking for the %DERBY_HOME%\bin\derby.log file. If confirmed, then you need to create a %DERBY_HOME%\bin\derby.properties file. Wherever the derby.log file is, that’s where you create the derby.properties file.

On the other hand if you didn’t ignore the Run Derby Network Server section above, congratulations! The derby.properties file must go into the directory set by the -Dderby.system.home Java system property.

Now that you know where to put the derby.properties file, here is (an example) of what to put in it:

# Passwords don't expire for 10 years
derby.authentication.native.passwordLifetimeMillis=315360000000

# Use the best hash algorithm you can
derby.authentication.builtin.algorithm=SHA-512

# Use a larger salt length for better security
derby.authentication.builtin.saltLength=128

# Re-hash this number of times for better security
derby.authentication.builtin.iterations=1564

The first property derby.authentication.native.passwordLifetimeMillis is the most important one. It configures how long Derby user passwords can be used before they go stale. By default, a password goes stale in 31 days unless this property is configured.

NOTE Derby uses the word “stale”, whereas we would typically use the word “expired”.

The value of this property is in milliseconds. The example above - 315360000000 - means a user’s password goes stale 10 years after the user is created. This configuration is for all users in every database in that Derby network server. There does not seem to be any per-user password expire setting.

Now you have the derby.properties file in place. Start the network server and let’s use it.

Run ij

ij is to Derby what sqlplus is to Oracle; just a simple command-line interface to execute SQL. Find and run %DERBY_HOME%\bin\ij.bat. For the rest of the blog, the "ij>" prompt will indicate SQL commands that must be executed within ij.

With ij running, we can create a database. Let’s do that next.

Create a Database

First, make sure your Derby network server is configured (as described above), and running (as described above) and is waiting for connections on whatever port you configured.

Second, make sure ij is running. You should be at this prompt:

ij>

Third, issue a connect statement which will create a new database.

ij> connect 'jdbc:derby://localhost:11528/resiste;create=true;' user 'sa_resiste';

Let’s look at this in more detail. localhost assumes the Derby network server is running on the same machine as ij, though it can be any host on your network. 11528 is the port the Derby network server is using to listen for connections. resiste is the name of the database to connect to; remember a Derby network server can have many different databases. create=true tells Derby to create the database if it doesn’t exist already. When created, the resiste database will the file system folder %derby.system.home%\resiste. sa_resiste is name of the admin user for the resiste database. Usually it’s just sa. Next we’ll look at setting the password for this admin user.

Create the Admin User

Derby provides an internal system call to create users and passwords. Make sure you are connected to the resiste database then execute:

ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa_resiste', 'derby123'); 
ij> disconnect;
ij> exit;

Line 1 creates the sa_resiste user with the password derby123. Lines 3 and 4 then disconnect from the database and exits ij.

RESTART THE NETWORK SERVER NOW

After restarting, let’s see if it worked. Connect with sa_resiste and no password. Connection will get authentication failure.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'sa_resiste';
ERROR 08004: Connection authentication failure occurred.  Reason: Userid or password invalid.

Now connect with sa_resiste and password. Connection will succeed.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'sa_resiste' password 'derby123';
ij> 

Good! The admin user is now created. Next we’ll use the admin user to create a table. This table will be used to validate the permissions of the application-level user we’ll create later.

Create Test Table

Now we are going to use the admin user to create a test table. We will do this for a couple reasons.

1. Verify the admin user has all permissions and is able to execute these SQL commands.
2. Verify the permissions of the application-level user we’ll create later.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'sa_resiste' password 'derby123';
ij> create schema testing;
ij> set schema testing;
ij> create table names (full_name varchar(100));
ij> insert into names values ('rita red');
ij> select * from names;
FULL_NAME
----------------------------------------------------------------------------------------------------
rita red
ij> disconnect; 

Next let’s create the application-level user.

Create the Application User

Now for the fun stuff. Let’s create an application-level user. This will be a user with permission limited to only the operations an application is able to perform. For example, if your Microservice is only going to GET data, then the application-level user should only have SELECT permissions on the database table. We will test the application-level user’s permission, but first let’s create the user.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'sa_resiste' password 'derby123';
ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('oscar', 'orange');
ij> disconnect;
ij> exit;

NOTE Remember, oscar’s password will go stale in 31 days by default. However, if you have a derby.properties file with the derby.authentication.native.passwordLifetimeMillis property set, then it will go stale after whatever time is set by that property. There is no other way to change the time it takes for the password to go stale.

RESTART THE NETWORK SERVER NOW

After restarting, let’s see if it worked. Connect with oscar. Connection will succeed, but, oscar won’t have the permission to read the test table.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'oscar' password 'orange';
ij> select * from testing.names;
ERROR 42502: User 'OSCAR' does not have SELECT permission on column 'FULL_NAME' of table 'TESTING'.'NAMES'.
ij> disconnect;

Even though the SELECT statement failed, failure means a successful test. oscar has no permissions so should not be able to select from the test table. Let’s configure oscar next.

Configure the Application User

Let’s set some permissions for oscar. Of course the sa_resiste admin user is required to do this.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'sa_resiste' password 'derby123';
ij> set schema testing;
ij> grant select on names to oscar;
ij> disconnect;

This will give oscar only 1 permission: to select from TESTING.NAMES table. Let’s see if it worked.

ij> connect 'jdbc:derby://localhost:11528/resiste' user 'oscar' password 'orange';
ij> select * from testing.names;
FULL_NAME
----------------------------------------------------------------------------------------------------
rita red
ij> disconnect;

Congratulations! You now have an application-level user with limited permissions in your Derby database.

Summary

I hope you enjoyed learning how to do simple user administration with Derby.