Hi Rick,

Here is a working Java example for you. It’s fully self-contained with its own 
JRE so all you need do is download it and run it. This bin distribution is for 
Windows, so sorry if you’re on another platform.  It’s too big for email, so I 
have it on my OneDrive 
https://1drv.ms/u/s!Ao0GHBDYRFEMgbMss-5a_R3q1GOvBQ?e=npfMBd

First, download, unzip, then run /bin/start-resiste.bat. You’ll see the 
following:
[cid:image001.png@01D71EF8.C4AFB090]


Next, m move the /data directory to a different location and then update 
/conf/db.properties.
[cid:image002.png@01D71EF9.233513D0]


Next, run /bin/start-resiste.bat again. You’ll see the following error:
[cid:image003.png@01D71EF9.233513D0]


Next, update /bin/start-resiste.bat to make sure derby.system.home is in sync 
with the new location of the database:
[cid:image004.png@01D71EF9.3A7B5450]

Next, run /bin/start-resiste.bat again. You’ll see it’s now successful with 
/conf/db.properties and /bin/start-resiste.bat in sync
[cid:image001.png@01D71EF8.C4AFB090]


Now here is a big Caveat. I discovered this while getting this demo together. I 
have Derby 10.14.2.0 installed on my computer. For my first attempt at this 
demo, I created the database from scratch using 10.14.2.0. When I did this, I 
DID NOT experience the connection error as described above. However, when I use 
my original database, I DO experience the error as demonstrated above. 
Unfortunately, I do not remember which version of Derby I used to create the 
original database, but obviously it was an older version. So perhaps this has 
something to do with an older database version?

Mike





From: Rick Hillegas <rick.hille...@gmail.com>
Sent: Friday, March 19, 2021 4:04 PM
To: derby-user@db.apache.org
Subject: Re: Embedded database, authentication, and derby.system.home

Hi Michael,

I must be missing something in your description of the problem, because I 
cannot reproduce it. I have created the following scripts in order to reproduce 
the behavior of both changing the value of derby.system.home and moving the 
database to another directory:

First SQL script nativeTest.sql:

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';



CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );



-- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';



CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';



VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');



CREATE TABLE t(a INT);

INSERT INTO t VALUES (1), (2);


Second SQL script nativeTest2.sql:

CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';



SELECT * FROM t;


Then a shell script (z1) to run nativeTest.sql, move the database to another 
directory, then run nativeTest2.sql:

#! /bin/bash

#

# Run a derby test



rm -rf z/db1

rm -rf zz/db1

rm zzz/*

rm zzzz/*



. setupClasspath



java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzz 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest.sql



mv z/db1 zz/



java -cp $CLASSPATH -Dderby.system.home=/Users/rhillegas/derby/mainline/zzzz 
-Dderby.language.logStatementText=true -Dderby.stream.error.logSeverityLevel=0 
org.apache.derby.tools.ij  $MAC_HOME/sql/nativeTest2.sql


When I run z1, I get the following output:

ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;create=true;user=dbo';

ij> CALL syscs_util.syscs_create_user( 'DBO', 'dbo_password' );

0 rows inserted/updated/deleted

ij> -- shutdown in order to enable NATIVE authentication

CONNECT 'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;shutdown=true';

ERROR 08006: Database '/Users/rhillegas/derby/mainline/z/db1' shutdown.

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/z/db1;user=dbo;password=dbo_password';

ij(CONNECTION1)> VALUES 
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.provider');

1

--------------------------------------------------------------------------------------------------------------------------------

NATIVE::LOCAL



1 row selected

ij(CONNECTION1)> CREATE TABLE t(a INT);

0 rows inserted/updated/deleted

ij(CONNECTION1)> INSERT INTO t VALUES (1), (2);

2 rows inserted/updated/deleted

ij(CONNECTION1)> ij version 10.16

ij> CONNECT 
'jdbc:derby:/Users/rhillegas/derby/mainline/zz/db1;user=dbo;password=dbo_password';

ij> SELECT * FROM t;

A

-----------

1

2



2 rows selected


Can you post a similar set of scripts which demonstrate the problem you are 
seeing?

Thanks,
-Rick

On 3/19/21 11:14 AM, Michael Remijan wrote:

NATIVE.



After creating the database I, I set the 'sa' user like this:



ij> CALL SYSCS_UTIL.SYSCS_CREATE_USER('sa', 'xxxxxxxxxxxxxxx');



Then I create the application user like this:



CALL SYSCS_UTIL.SYSCS_CREATE_USER('resiste_standalone', 'xxxxxxxxxxxxxxx');



And I have the following in derby.properties



# Passwords don't expire for 20 years

derby.authentication.native.passwordLifetimeMillis=631138520000

# 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



BTW, I've updated code to dynamically 
System.setProperty("derby.system.home","/path/to/dir"); at runtime based off of 
user configuration. That been successful. So if I move the database to 
somewhere other than my application's default location, I set derby.system.home 
at runtime based on that configuration.



I also found that this is only a factor with embedded databases where the path 
to the database is a fully qualified file system path. If the configuration 
uses a remote database, the value for derby.system.home doesn't seem to matter.



-----Original Message-----

From: Rick Hillegas <rick.hille...@gmail.com><mailto:rick.hille...@gmail.com>

Sent: Friday, March 19, 2021 11:22 AM

To: derby-user@db.apache.org<mailto:derby-user@db.apache.org>

Subject: Re: Embedded database, authentication, and derby.system.home



Hi Mike,



What kind of authentication are you using: LDAP, NATIVE, or custom?



On 3/19/21 7:05 AM, Michael Remijan wrote:

Greetings,



I have an interesting issue I just ran into and it took a little while to debug 
and figure out exactly what is happening.



I have a project that uses an embedded Derby database.  My project folder 
organization is a typical for an open source project:



/projectname

    /bin

        start.sh   // starts the application

   /data

      /projectdb  // this is the derby database.



My projectdb database also has a specific username, password, and 
permissions/roles on that user.



When I start the application, the "start.sh" script sets the 
-Dderby.system.home to the "/data" directory and my JDBC connection URL figures 
out the fully qualified location of the "/projectdb" directory so Derby can 
easily access it. This all works perfectly fine.



Then I wanted to run a test where I moved the /projectdb directory to a 
different location. I though  I would be able to do this with no problems. But 
I was wrong :( When I do this I get the following authentication exception



Caused by: java.sql.SQLNonTransientConnectionException: Connection 
authentication failure occurred.  Reason: Invalid authentication..

         at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

         at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)

         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown 
Source)

         at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown 
Source)

         at 
org.apache.derby.impl.jdbc.EmbedConnection.checkUserCredentials(Unknown Source)

         at org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)

         at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)

         at org.apache.derby.jdbc.InternalDriver$1.run(Unknown Source)

         at java.security.AccessController.doPrivileged(Native Method)

         at org.apache.derby.jdbc.InternalDriver.getNewEmbedConnection(Unknown 
Source)

         at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)

         at org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)

         at org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)

         at java.sql.DriverManager.getConnection(DriverManager.java:664)

         at java.sql.DriverManager.getConnection(DriverManager.java:247)

         at 
org.ferris.resiste.console.sql.SqlConnectionProducer.postConstruct(SqlConnectionProducer.java:31)

         ... 67 more

Caused by: ERROR 08004: Connection authentication failure occurred.  Reason: 
Invalid authentication..

         at org.apache.derby.iapi.error.StandardException.newException(Unknown 
Source)

         at 
org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown
 Source)

         ... 84 more



Which I thought was kind of odd. What I eventually discovered is the 
-Dderby.system.home value and the location of the database must be in sync. If 
they are not, I get the authentication exception. If do not set the 
-Dderby.system.home value, I get this authentication exception.



Originally, I remember setting the -Dderby.system.home value because I wanted 
the derby.log files created in that directory. I didn't expect this value would 
also be critical for connecting to the database with a username/password.  I 
haven't tried it unauthenticated, but it seems to me I should be able to move 
the location of the database around and as long as I'm connecting to the 
database, authentication should work OK regardless of -Dderby.system.home and 
the location of the database being in sync.  This make sense?



Thanks!

Mike






Reply via email to