The issue I have is connecting with a Java client. I will try and put together
a simple example this week.
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
