db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Remijan <mjremi...@live.com>
Subject RE: Embedded database, authentication, and derby.system.home
Date Mon, 22 Mar 2021 11:26:22 GMT
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.hillegas@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.hillegas@gmail.com><mailto:rick.hillegas@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






Mime
View raw message