JavaDB end to end security

Masoud Kalali
02/19/2007

JavaDB, as an open source and pure Java relational database, provides several features that make it suitable for embedded and network server mode. One of these features is JavaDB mechanisms to make it secure on several levels. If you are going to build an application using JavaDB and security is important, then this article is for you.

These are the steps that we should follow to make JavaDB work with an acceptable level of security.

1. Apply encryption to secure database files physically.
2. Apply authentication for connections to restrict access to our server.
3. Apply SQL authorization for internal permission management over database objects.
4. Securing environment using Java security manager.

Before we dig into each of these subjects, you need to have JavaDB installed in your environment. We will use OpenDS Which is a pure Java open source LDAP server for authentication purpose.

I will talk about each of the above items and in the same time give you detail instructions to address it using JavaDB provided features.

Database Encryption

First let's see what we have when we create a database in JavaDB. A JavaDB database is usually a directory containing some data files, transaction logs, and a database configuration file, service.properties. The JavaDB database directory is portable and anyone can copy a JavaDB database and boot it up using another JavaDB instance and take a look into its data. But there are some mechanisms that protect our database from any kind of external unauthorized access. A JavaDB database can be encrypted on hard disk and thus not be useable until decrypted. Decryption is only possible using a single key or password, which is provided during database creation.

OK, let's see how we can encrypt our database and then how we can connect to this encrypted database. As I mentioned earlier encryption happens during database creation time.

You may already know that we can create a database by supplying extra attributes in JDBC connection string. To demonstrate our steps, I will use IJ, which is the JavaDB command line tool for database administration and development. Start the JavaDB network server and then run the ij CLI and execute the following commands one after another. I should say that during this article we will use NetworkServerControl script to start and stop network server. You can start network server by passing a start as a command line argument to start JavaDB.

F:\dev\dbServers\JavaDB-10.2.2.0\bin>ij

ij version 10.2

ij> DRIVER 'org.apache.derby.jdbc.ClientDriver';

ij> CONNECT 'jdbc:derby://127.0.0.1/secArticle;create=true;dataEncryption=true;bootPassword=a@Simple_More_Than_16_Char_Password;encryptionAlgorithm=Blowfish/CBC/NoPadding;user=tquist';

ij>

The first command, DRIVER, tells ij which driver it should use and second command creates an encrypted database. Our Database owner is tquist; it is not mandatory to specify a user but we specify it because in the next few steps we will talk about database ownership.

You can see three attribute inside the connection string which you may not be familiar with.

• dataEncryption: This attribute simply tell networks server to create an encrypted database.

• bootPassword: password which will be used to encrypt the key that JavaDB engine uses to encrypt the data files. Password length must comply with algorithm key length and in our case it must have 16 characters or more because Blowfish is a 128 bit encryption algorithm.

• encryptionAlgorithm: determine which algorithm with what kind of configuration should be used to encrypt the database.

Now we have an encrypted database and we need to connect to this database from our java application, how does the code that connects to this encrypted database will look like? It is quite simple: you just need to include the bootPassword attribute in your connection string. Something like:

...

   class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();

   Connection connection =

        DriverManager.getConnection ("jdbc:derby://127.0.0.1/secArticle;bootPassword=a@Simple_More_Than_16_Char_Password");

... 

That's it; you have a connection to your encrypted database. Beware that once you boot a database (i.e., you've made a first connection with a boot password) it will be open for connections even without bootPassword. So shut down the database as soon as you have finished your work with it. The following code snippet shows how you can shut down a database from within your application.

   boolean cantShutdown = false;

   try {

      DriverManager.getConnection("jdbc:derby:;shutdown=true");

   } catch (SQLException se)  {

      if ( se.getSQLState().equals("XJ015") ) {

         cantShutdown = true;

      }

   }

   if (!cantShutdown ) {

      //Your database did not shutdown properly

   }  else  {

      //Your database is shutdown 

   }

If you try to connect to database without bootPassword attribute, you will get an error message. Using encryption, you protect your database against any external unauthorized access by anyone who do not know the bootpassword.

Connection authentication

As a second concern I am going to talk about connection authentication. A JavaDB database or server is open for connections with full access to all database server resources. A database developer can configure it to authenticate users that are trying to connect to a database on the server. Until you try to configure it to your specific needs, JavaDB is a zero-administration RDBMS. But configuring JavaDB to perform authentication is very simple and nearly a zero-cost configuration.

The JavaDB network server is configurable using a standard properties file, named derby.properties, which can simply be placed inside the JavaDB bin directory. There are dozens of configuration parameters we can put in this properties file, but for now we are just going to use a few of them.

JavaDB can authenticate users in three ways in addition to its default no-authorization behavior:

• Built-in
• External LDAP
• Application defined

Each of the above mechanisms has its own strengths and weaknesses; in the next few paragraphs you will see how easily you can configure JavaDB to perform authentication against an external LDAP like OpenDS.

So before we continue, you need to setup and start your OpenDS directory server. OpenDS is a pure Java open-source directory server with many features that could be used to build an enterprise application. The OpenDS installation is straightforward: just launch the QuickSetup JNLP (Java Web Start) installer, and follow the installation steps to set up your directory server.

Having installed OpenDS, I will describe the test data which you will import into your directory server storage to test JavaDB authentication. Our test data is a standard LDIF file, which you can find in the resources section. You can import it into OpenDS using its shell script named import-ldif .

>import-ldif --backendID userRoot --ldifFile path/to/secArticle.LDIF

By executing the above command in the OpenDS bin directory, you can import the sample data into your directory server storage. Make sure that OpenDS is not running when you want to import data into its storage; otherwise you will receive an error message indicating that the import utility cannot acquire a lock over storage.

If you browse OpenDS storage using an LDAP browser like LDAP Studio, you will find four entries under dc=example,dc=com. These entries are the users that we will use during next parts of this article. The user names are jhallett, mchrysta, thardy, and tquist; passwords are same as respective userids.

To activate the authentication we need to add a property in the derby.properties file. So, create a text file and save it in JavaDB_home/bin as derby.properties, open the file and add the following line to it.

derby.connection.requireAuthentication=true

This property alone tells JavaDB to authenticate users whom try to connect to JavaDB but it does not define any source for authenticating users' credentials. Therefore we should define a source that JavaDB should use to authenticate users against. Here is what we should add in order to define a source of authentication:

derby.authentication.server=ldap://127.0.0.1:389

derby.authentication.provider=LDAP

derby.authentication.ldap.searchAuthPW=YOUR_SELECTED_PASSWORD

derby.authentication.ldap.searchAuthDN=cn=Directory Manager

derby.authentication.ldap.searchBase=dc=example,dc=com

derby.authentication.ldap.searchFilter=objectClass=person

It is not mandatory to add all of the above properties, but it is a good practice to include the last two, since they will limit the search scope for usernames during authentication. Ab explanation about each of those attribute can be found in the JavaDB documentation.

Now, restart the JavaDB network server and let's try to connect to our secArticle database as in the previous step, to what we will get when we try it. Open an ij session and execute the following commands.

ij> DRIVER 'org.apache.derby.jdbc.ClientDriver';

ij> CONNECT 'jdbc:derby://127.0.0.1/secArticle;bootPassword=a@Simple_More_Than_16_Char_Password;user= tquist';

ERROR 08004: Connection authentication failure occurred.  Reason: userid or password invalid.

Now let's provide a correct username and password; we can use any of the 4 users that we added to our OpenDS storage in the previous section.

ij> CONNECT 'jdbc:derby://127.0.0.1/secArticle;bootPassword=a@Simple_More_Than_16_Char_Password;user=tquist;password=tquist'; 

ij>

And this time you should be able to connect to the database without any problem.

SQL authorization

Now we have authentication present in the deployment, what about authorization? Activating JavaDB SQL authorization is very simple; just open derby.properties and add some entries which ask JavaDB to apply SQL authorization. The lines you need to add are as follows:

derby.database.sqlAuthorization=true

derby.database.defaultConnectionMode=sqlStandard

These two properties enforce SQL authorization, for any database created from now on. But what about our already-created database? What we can do to enable SQL authorization in them? Fortunately, we just need to execute a system function to set SQL authorization for an already-created database. So, open an IJ session, connect to our sample database, and execute the following command.

ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.sqlAuthorization','true');

Now, our database has SQL authorization capability, but the network server needs to be restarted before we can use this feature. Restart the network server and continue. Then open an ij session, connect to the database with the tquis user, and create a table.

ij> CONNECT 'jdbc:derby://127.0.0.1/secArticle;bootPassword=a@Simple_More_Than_16_Char_Password; user=tquist;password= tquist';

ij> CREATE TABLE sec_art_table ( name VARCHAR(25) , lastName VARCHAR(25));

0 rows inserted/updated/deleted

ij> disconnect;

Executing the above commands will create a table in TQUIST schema, with table owner tquist who has full privileges over this table. The last command will end the current session.

Now to check whether our table is safe or not connect to database with another user and try to insert some data into that table.

ij> CONNECT 'jdbc:derby://127.0.0.1/secArticle;bootPassword=a@Simple_More_Than_16_Char_Password;user= jhallett;password=jhallett';

ij> INSERT INTO TQUIST.sec_art_table  values ( ‘John' ,'Connor');

ERROR 28506: User 'JHALLETT' does not have insert permission on table 'TQUIST'.'

SEC_ART_TABLE'.

ij>disconnect;

The above error indicate that our user has no permission to perform an insert on TQUIST.sec_art_table. In the next code snippetm we connect to database as user tquist and grant some permission to jhallett.

ij> CONNECT 'jdbc:derby://127.0.0.1/secArticle;bootPassword=a@Simple_More_Than_1

6_Char_Password; user=tquist;password= tquist'; 

ij> grant insert on TQUIST.sec_art_table to jahllett;

0 rows inserted/updated/deleted

Now jhallett has insert privileges on TQUIST.sec_art_table. You can try inserting statement again to make sure that it works fine. Other implemented and supported privileges can be found in the JavaDB documentation.

Securing environment

The Java Security manager and policy definition is a mechanism that allows one to define and apply specific policies over execution of specific code and JVM access to some potentially risky resources like sockets, files, etc. By default when you install the JRE or JDK, there is no restriction and all permissions are granted to standard extensions.

At the JVM level, we can have one default policy implementation which will apply on all users of that JRE. We can also define per-user policy files to apply specific policies for certain users. There are multiple ways that we could apply policies to the JVMm but the most popular one is using policy files which we pass to the java command. A policy file is a plain text file in UTF-8 encoding, that follows the standard policy declaration syntax. Discussing policy files and security manager is outside of this article's scope, so let's jump back to security configuration for JavaDB the environment.

When you are starting the JavaDB network server, you can activate the Java security manager and pass a policy file to the JVM to use when it is running. To achieve this you can either edit the NetworkServerControl shell file (a .bat or .sh file) or directly execute the NetworkServerControl class and pass the required JRE parameter. I, for one, prefer to edit the NetworkServerControl batch file. Look at NetworkServerControl.bat file and you will find a code block like:

:runWithClasspath

"%_JAVACMD%" %DERBY_OPTS% -classpath "%CLASSPATH%;%LOCALCLASSPATH%" org.apache.derby.drda.NetworkServerControl %DERBY_ARGS% %DERBY_CMD_LINE_ARGS%

goto end

You just need to change the middle line to:

:runWithClasspath

"%_JAVACMD%"  -Djava.security.manager -Djava.security.policy=JavaDB.policy  -classpath "%CLASSPATH%;%LOCALCLASSPATH%" org.apache.derby.drda.NetworkServerControl %DERBY_ARGS% %DERBY_CMD_LINE_ARGS%

goto end

As I said a policy file is a plain text file, so create a text file and save it as JavaDB.policy in the JavaDB bin directory. Now add the following snippet to it.

grant codeBase "file:F:/dev/dbServers/JavaDB-10.2.2.0/lib/*" {

permission java.io.FilePermission "${derby.system.home}", "read";

permission java.io.FilePermission "${derby.system.home}${/}-", "read, write, delete";

permission java.io.FilePermission "${user.dir}${/}-", "read, write, delete";

permission java.util.PropertyPermission "derby.*", "read";

permission java.util.PropertyPermission "user.dir", "read";

permission java.lang.RuntimePermission "createClassLoader";

permission java.net.SocketPermission "localhost", "accept";

permission java.net.SocketPermission "127.0.0.1", "accept, connect,resolve";

};

In the above policy description we grant some permissions to all jar files and classes placed in lib folder; permissions are as follow:

• We grant full access over the JavaDB home directory and its sub directories, for example JavaDB classes has full access to bin directory which our databse resides inside it.
• Read permission for reading user dir property and all JavaDB properties.
• We permit Classloader creation, which allows JavaDB to execute queries and load required classes.
• We allow codebase to create sockets and accept connections from localhost and loopback IP address.

By default JavaDB network server will listen on localhost, but we can pass an -h AN_IP_ADDRESS argynebt to NetworkServerControl in order to configure it to listen on other IP addresses. Now imagine that your JavaDB is open for incoming connections from the Internet; you can simply limit incoming connections to your JavaDB network server by providing a list of permitted address in a policy file, as we have done in our sample policy file.

Conclusion

JavaDB as an open source, pure Java database, can play an important role in future Java applications, especially for small applications that do not need a huge list of expensive features that are available in commercial RDBMS's. Security is a major concern to the software development industry and JavaDB provides an acceptable set of features to make your small database secure.

Some questions that you may face during this article:

What will happen if I grant privilege(s) to a user that does not exist?

Nothing, you will not get an exception or error message. Privilege(s) will be granted to the user because JavaDB does not check your authentication source for a user existence when you grant some privileges to a user.

Who is database administrator?

The user who creates the database is the database administrator, meaning the user has the ability to grant or revoke privileges of other users on any schema. Each user has full access to its own schema.

Can we use our own algorithm for JavaDB Encryption?

Yes, you can use your own algorithm but it must meet some criteria. Take a look at the Java Cryptography Extension (JCE) and documentation on Specifying an alternate encryption algorithm .

What is the derby.system.home variable?

This is an environment variable used to point to the directory that JavaDB will start in. By default, JavaDB will start in its bin directory, so derby.system.home is the JavaDB bin directory by default.

Resources

• JavaDB homepage
• OpenDS homepage
• PolicyFiles syntax refrence
• Permissions in the JavaTM 2 Standard Edition Development Kit
• JavaDB properties list
• Java Cryptography Extension (JCE)
• Specifying an alternate encryption algorithm
• Sample LDAP data for our authentication tests