Introduction

About 4 months ago, I had a quick look at the Apache Derby Java database offering. I got things working pretty well by following the tutorial and reference material accessing the database via the interactive shell ij and via java code. However it did take me a while to figure out basic database authentication.

Here are my distilled findings and hints after looking at it for the second time just recently.

Checking it works

java -jar $DERBY_HOME\lib\derbyrun.jar sysinfo

Connecting to a derby prompt

java -jar $DERBY_HOME\lib\derbyrun.jar ij

Starting a derby server

java -jar $DERBY_HOME/lib/derbyrun.jar server start [-p 1088]

Or with the -Dderby.drda.portNumber=9988 property

Stopping a derby server

java -jar $DERBY_HOME/lib/derbyrun.jar server shutdown

Connecting to a derby prompt MK II

java -Dderby.system.home=/derby_db_root_dir -jar $DERBY_HOME/lib/derbyrun.jar ij

Creating your first database

ij>connect 'jdbc:derby:firstdb;create=true';

Creating your first server database

ij>connect 'jdbc:derby://localhost:1527/firstdb;create=true';

Adding a user to a database

ij>connect 'jdbc:derby://localhost:1527/firstdb;create=true';

ij>CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.sa','derbypass');

Configuring User authentication

Start the server with the authentication property set to true :

-Dderby.connection.requireAuthentication=true

(If you find that you have locked yourself out of your database during this stage then you can turn off authetication by using -Dderby.connection.requireAuthentication=false)

ij>connect 'jdbc:derby:firstdb;user=sa;password=derbypass';

To specify full access users :

ij>CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers','sa,user1,user2');

To specific read-only access users :

ij>CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers', 'anon1anon2');

Turn on connections must have a user and password :

ij>CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','noAccess');

Finally... Commit the authentication property to your database

This is the point of no return, make sure that your authentication model works with the -Dderby.connection.requireAuthentication=true switch first before setting this property in the database!!

ij>CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication','true'); to switch on authentication at the database level