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\u2019s so easy to use, especially when it come to users and permissions – you don\u2019t 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
I am not going to go through the process of downloading and installing these technologies. I\u2019ll leave that as an exercise for you.<\/p>\n
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<\/a>, I give detailed instructions on how to do this. Here is the tldr; (for Windows):<\/p>\n Now that you can run a Derby network server, let\u2019s configure it.<\/p>\n To configure the Derby network server, you need to create a I\u2019ll first assume that you ignored the Run Derby Network Server<\/strong> section above and instead are running Derby with all its defaults. If that\u2019s the case, you probably started the network server by finding the On the other hand if you didn\u2019t ignore the Run Derby Network Server<\/strong> section above, congratulations! The Now that you know where to put the Now you have the network server configured. Start it and let\u2019s use it. The first thing we\u2019ll use it for is configuring the Derby admin user. We\u2019ll look at this next.<\/p>\n Before we configure the Derby admin user, we first neeed to run the NOTE<\/strong> For the rest of the blog, the Now that Now that the Derby network server is configured and running, we\u2019ll need configure the admin user. The admin user will have full permissions to perform any database operation. Let\u2019s look at the commands:<\/p>\n Line 1 is a standard JDBC connection string to connect to the database. The database name is RESTART THE NETWORK SERVER NOW<\/strong><\/p>\n After restarting, let\u2019s see if it worked. Connect with Now connect with Good! The admin user is now created. Next we\u2019ll use the admin user to create a table. This table will be used to validate the permissions of the application-level user we\u2019ll create later.<\/p>\n Now we are going to use the admin user to create a test table. We will do this for a couple reasons.<\/p>\n Next let\u2019s create the application-level user.<\/p>\n Now for the fun stuff. Let\u2019s 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\u2019s permission, but first let\u2019s create the user.<\/p>\n RESTART THE NETWORK SERVER NOW<\/strong><\/p>\n After restarting, let\u2019s see if it worked. Connect with Even though the SELECT statement failed, failure means a successful test. Let\u2019s set some permissions for This will give Congratulations! You now have an application-level user with limited permissions in your Derby database.<\/p>\n I hope you enjoyed learning how to do simple user administration with Derby.<\/p>\nconfig-resiste.cmd<\/h2>\n
@echo off\r\nREM --- START EDITING ---\r\nset DERBY_HOME=C:\\Users\\Michael\\Applications\\Derby\\db-derby-10.14.1.0-bin\r\nset JAVA_HOME=C:\\Program Files\\Java\\jdk1.8.0_152\r\nset NS_HOME=C:\\Users\\Michael\\Applications\\Derby\\servers\\resiste\\data\r\nset NS_PORT=11528\r\nREM --- STOP EDITING ---\r\nset PATH=%DERBY_HOME%\\bin;%PATH%\r\nset DERBY_OPTS=-Dderby.drda.portNumber=%NS_PORT% -Dderby.system.home=%NS_HOME%<\/pre>\n
start-resiste.cmd<\/h2>\n
@echo off\r\ncall config-resiste.cmd\r\nStartNetworkServer<\/pre>\n
stop-resiste.cmd<\/h2>\n
@echo off\r\ncall config-resiste.cmd\r\nStopNetworkServer<\/pre>\n
Configure Derby Network Server<\/h2>\n
derby.properties<\/code> file. But where does the file go? It can go in a couple different places. Let\u2019s take a look.<\/p>\n%DERBY_HOME%\\bin\\startNetworkServer.bat<\/code> file and double-clicking it. If you did this – highly not recommended – then Derby thinks the %DERBY_HOME%\\bin<\/code> directory is its system directory. You can confirm this by looking for the %DERBY_HOME%\\bin\\derby.log<\/code> file. If confirmed, then you need to create a %DERBY_HOME%\\bin\\derby.properties<\/code> file. Wherever the derby.log<\/code> file is, that\u2019s where you create the derby.properties<\/code> file.derby.properties<\/code> file must go into the directory set by the -Dderby.system.home<\/code> Java system property. See the config-resiste.cmd<\/code> file example above.<\/p>\nderby.properties<\/code> file, here is (an example) of what to put in it:<\/p>\n# Passwords don't expire for 10 years\r\nderby.authentication.native.passwordLifetimeMillis=315360000000\r\n\r\n# Use the best hash algorithm you can\r\nderby.authentication.builtin.algorithm=SHA-512\r\n\r\n# Use a larger salt length for better security\r\nderby.authentication.builtin.saltLength=128\r\n\r\n# Re-hash this number of times for better security\r\nderby.authentication.builtin.iterations=1564<\/pre>\n
Run ij<\/h2>\n
ij<\/code> application. ij<\/code> is to Derby what sqlplus<\/code> is to Oracle; just a simple command-line interface. Find and run %DERBY_HOME%\\bin\\ij.bat<\/code>.<\/p>\n\"ij>\"<\/code> prompt will indicate SQL commands that must be executed within ij<\/code>. I assume you\u2019ll figure out you need to run ij<\/code> to execute these commands.<\/p>\nij<\/code> is running, we get get some work done. Let\u2019s look at that Derby admin user.<\/p>\nCreate the Admin User<\/h2>\n
ij> connect 'jdbc:derby:\/\/localhost:11528\/resiste;create=true;' user 'sa_resiste';\r\nij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa_resiste', 'derby123'); \r\nij> disconnect;\r\nij> exit;<\/pre>\nresiste<\/code>. Since this is the first time connecting to the database, the connection string contains create=true;<\/code> to create the database. I connect to the database with the sa_resiste<\/code> user, and since the database is being created during this first connection, the sa_resiste<\/code> user will be setup as the admin user. Line 2 creates this user with the password derby123<\/code>. Lines 3 and 4 then disconnect from the database and exit ij<\/code>.<\/p>\nsa_resiste<\/code> and no password. Connection will get authentication failure.<\/p>\nij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'sa_resiste';\r\nERROR 08004: Connection authentication failure occurred. Reason: Userid or password invalid.<\/pre>\n
sa_resiste<\/code> and password. Connection will succeed.<\/p>\nij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'sa_resiste' password 'derby123';\r\nij><\/pre>\n
Create Test Table<\/h2>\n
\n
ij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'sa_resiste' password 'derby123';\r\nij> create schema testing;\r\nij> set schema testing;\r\nij> create table names (full_name varchar(100));\r\nij> insert into names values ('rita red');\r\nij> select * from names;\r\nFULL_NAME\r\n----------------------------------------------------------------------------------------------------\r\nrita red\r\nij> disconnect;<\/pre>\nCreate the Application User<\/h2>\n
ij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'sa_resiste' password 'derby123';\r\nij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('oscar', 'orange');\r\nij> disconnect;\r\nij> exit;<\/pre>\noscar<\/code>. Connection will succeed, but, oscar<\/code> won\u2019t have the permission to read the test table.<\/p>\nij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'oscar' password 'orange';\r\nij> select * from testing.names;\r\nERROR 42502: User 'OSCAR' does not have SELECT permission on column 'FULL_NAME' of table 'TESTING'.'NAMES'.\r\nij> disconnect;<\/pre>\n
oscar<\/code> has no permissions so should not be able to select from the test table. Let\u2019s configure oscar<\/code> next.<\/p>\nConfigure the Application User<\/h2>\n
oscar<\/code>. Of course the sa_resiste<\/code> admin user is required to do this.<\/p>\nij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'sa_resiste' password 'derby123';\r\nij> set schema testing;\r\nij> grant select on names to oscar;\r\nij> disconnect;<\/pre>\n
oscar<\/code> only 1 permission: to select from TESTING.NAMES table. Let\u2019s see if it worked.<\/p>\nij> connect 'jdbc:derby:\/\/localhost:11528\/resiste' user 'oscar' password 'orange';\r\nij> select * from testing.names;\r\nFULL_NAME\r\n----------------------------------------------------------------------------------------------------\r\nrita red\r\nij> disconnect;<\/pre>\n
Summary<\/h2>\n