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. Here is the tldr; (for Windows):
config-resiste.cmd
@echo off REM --- START EDITING --- set DERBY_HOME=C:\Users\Michael\Applications\Derby\db-derby-10.14.1.0-bin set JAVA_HOME=C:\Program Files\Java\jdk1.8.0_152 set NS_HOME=C:\Users\Michael\Applications\Derby\servers\resiste\data set NS_PORT=11528 REM --- STOP EDITING --- set PATH=%DERBY_HOME%\bin;%PATH% set DERBY_OPTS=-Dderby.drda.portNumber=%NS_PORT% -Dderby.system.home=%NS_HOME%
start-resiste.cmd
@echo off call config-resiste.cmd StartNetworkServer
stop-resiste.cmd
@echo off call config-resiste.cmd StopNetworkServer
Now that you can run a Derby network server, let’s configure it.
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. See the config-resiste.cmd file example above.
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
Now you have the network server configured. Start it and let’s use it. The first thing we’ll use it for is configuring the Derby admin user. We’ll look at this next.
Run ij
Before we configure the Derby admin user, we first neeed to run the ij application. ij is to Derby what sqlplus is to Oracle; just a simple command-line interface. Find and run %DERBY_HOME%\bin\ij.bat.
NOTE For the rest of the blog, the "ij>" prompt will indicate SQL commands that must be executed within ij. I assume you’ll figure out you need to run ij to execute these commands.
Now that ij is running, we get get some work done. Let’s look at that Derby admin user.
Create the Admin User
Now that the Derby network server is configured and running, we’ll need configure the admin user. The admin user will have full permissions to perform any database operation. Let’s look at the commands:
ij> connect 'jdbc:derby://localhost:11528/resiste;create=true;' user 'sa_resiste';
ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa_resiste', 'derby123');
ij> disconnect;
ij> exit;Line 1 is a standard JDBC connection string to connect to the database. The database name is resiste. Since this is the first time connecting to the database, the connection string contains create=true; to create the database. I connect to the database with the sa_resiste user, and since the database is being created during this first connection, the sa_resiste user will be setup as the admin user. Line 2 creates this user with the password derby123. Lines 3 and 4 then disconnect from the database and exit 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.
- Verify the admin user has all permissions and is able to execute these SQL commands.
- 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;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.
| Published on Java Code Geeks with permission by Michael Remijan, partner at our JCG program. See the original article here: Apache Derby Database Users and Permissions Opinions expressed by Java Code Geeks contributors are their own. |
