db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Embedded database, authentication, and derby.system.home
Date Fri, 19 Mar 2021 21:03:38 GMT
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>
> Sent: Friday, March 19, 2021 11:22 AM
> To: 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