Users suddenly denied database access - Connection authentication error.

[Kerry <ka...@avionicengineers.com>]

Hi,

I appear to be having a very odd issue with Derby. I have found on more than one occasion (it seems) that all users previously granted access to a database are suddenly denied connection and access and there has been no deliberate changes applied by myself in respect of user access or any other kind of change - I am the only one with access/uses the DB on my home network. And I am sure this has now occurred more than once, previously I had assumed I had messed something up.

The database can be considered 'production' so there are no deliberate changes made to it on my part. Once every week, I have an autonomous service that gathers some data and pushes it into the Derby instance. That is all it does and it has been happily doing this for the past several weeks. However when I checked the logs of the autonomous service today I found it was giving:

java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred.  Reason: Userid or password invalid.

I have manually tried connecting to the database with several other user IDs including the 'owner' but I get the same error. The only user that can connect is the one I used to set up the credentials database - I am using native authentication with a dedicated database, or at least that is how I believe I have set this up. However this particular user cannot select anything from the tables etc because it was never granted permission and because it isn't the owner of the database I cannot change the permissions.

I think last time this happened I tried copying the database folder to another derby instance which had no authentication enabled in an attempt to recover the data but I seem to remember the user permissions were copied with the directory and I wasn't able to recover the data.I cannot rule out some third-party interference but it would seem unlikely as the database is on my home network which isn't open to the world.

Because I cannot connect to the database and select data, does this mean I have lost access completely or is there something I can modify in the database directory to allow me to recover the data?

Thanks for any help or suggestions

Kerry

Re: Users suddenly denied database access - Connection authentication error.

[Kerry <ka...@avionicengineers.com>]

Hi Rick,

That was exactly the problem. I missed that in the docs about password expiry. I set it with a system-wide property and all is good now.

Thanks for your prompt assistance!

Kerry

On 22/08/2020 16:16, Rick Hillegas wrote:
> Hi Kerry,
>
> Thanks for that detailed explanation of your issue. The most likely problem is that your user passwords have expired. Check the value of the system property derby.authentication.native.passwordLifetimeMillis. The following command should get you that value:
>
>
>   VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.native.passwordLifetimeMillis')
>
> If you set this property to 0, then your passwords will not expire, according to http://db.apache.org/derby/docs/10.15/ref/rrefproperpasswordmillis.html
>
> The default password lifetime is one month, according to http://db.apache.org/derby/docs/10.15/security/csecnativeotherprops.html. The DBA's password does not expire. That would explain why the DBA can connect but no-one else can. As you get down to the wire on your password's lifetime, you will receive SQLWarnings when you connect. It is likely that your application does not check for these warnings, so you never know that passwords are about to age out.
>
> Let us know if that is not the problem. Hope this helps...
>
> -Rick
>
> On 8/22/20 7:50 AM, Kerry wrote:
>> Hi,
>>
>> I appear to be having a very odd issue with Derby. I have found on more than one occasion (it seems) that all users previously granted access to a database are suddenly denied connection and access and there has been no deliberate changes applied by myself in respect of user access or any other kind of change - I am the only one with access/uses the DB on my home network. And I am sure this has now occurred more than once, previously I had assumed I had messed something up.
>>
>> The database can be considered 'production' so there are no deliberate changes made to it on my part. Once every week, I have an autonomous service that gathers some data and pushes it into the Derby instance. That is all it does and it has been happily doing this for the past several weeks. However when I checked the logs of the autonomous service today I found it was giving:
>>
>> java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred.  Reason: Userid or password invalid.
>>
>> I have manually tried connecting to the database with several other user IDs including the 'owner' but I get the same error. The only user that can connect is the one I used to set up the credentials database - I am using native authentication with a dedicated database, or at least that is how I believe I have set this up. However this particular user cannot select anything from the tables etc because it was never granted permission and because it isn't the owner of the database I cannot change the permissions.
>>
>> I think last time this happened I tried copying the database folder to another derby instance which had no authentication enabled in an attempt to recover the data but I seem to remember the user permissions were copied with the directory and I wasn't able to recover the data.I cannot rule out some third-party interference but it would seem unlikely as the database is on my home network which isn't open to the world.
>>
>> Because I cannot connect to the database and select data, does this mean I have lost access completely or is there something I can modify in the database directory to allow me to recover the data?
>>
>> Thanks for any help or suggestions
>>
>> Kerry
>>
>>
>

Re: Users suddenly denied database access - Connection authentication error.

[Rick Hillegas <ri...@gmail.com>]

Hi Kerry,

Thanks for that detailed explanation of your issue. The most likely 
problem is that your user passwords have expired. Check the value of the 
system property derby.authentication.native.passwordLifetimeMillis. The 
following command should get you that value:


   VALUES 
SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.authentication.native.passwordLifetimeMillis')

If you set this property to 0, then your passwords will not expire, 
according to 
http://db.apache.org/derby/docs/10.15/ref/rrefproperpasswordmillis.html

The default password lifetime is one month, according to 
http://db.apache.org/derby/docs/10.15/security/csecnativeotherprops.html. 
The DBA's password does not expire. That would explain why the DBA can 
connect but no-one else can. As you get down to the wire on your 
password's lifetime, you will receive SQLWarnings when you connect. It 
is likely that your application does not check for these warnings, so 
you never know that passwords are about to age out.

Let us know if that is not the problem. Hope this helps...

-Rick

On 8/22/20 7:50 AM, Kerry wrote:
> Hi,
>
> I appear to be having a very odd issue with Derby. I have found on more than one occasion (it seems) that all users previously granted access to a database are suddenly denied connection and access and there has been no deliberate changes applied by myself in respect of user access or any other kind of change - I am the only one with access/uses the DB on my home network. And I am sure this has now occurred more than once, previously I had assumed I had messed something up.
>
> The database can be considered 'production' so there are no deliberate changes made to it on my part. Once every week, I have an autonomous service that gathers some data and pushes it into the Derby instance. That is all it does and it has been happily doing this for the past several weeks. However when I checked the logs of the autonomous service today I found it was giving:
>
> java.sql.SQLNonTransientConnectionException: Connection authentication failure occurred.  Reason: Userid or password invalid.
>
> I have manually tried connecting to the database with several other user IDs including the 'owner' but I get the same error. The only user that can connect is the one I used to set up the credentials database - I am using native authentication with a dedicated database, or at least that is how I believe I have set this up. However this particular user cannot select anything from the tables etc because it was never granted permission and because it isn't the owner of the database I cannot change the permissions.
>
> I think last time this happened I tried copying the database folder to another derby instance which had no authentication enabled in an attempt to recover the data but I seem to remember the user permissions were copied with the directory and I wasn't able to recover the data.I cannot rule out some third-party interference but it would seem unlikely as the database is on my home network which isn't open to the world.
>
> Because I cannot connect to the database and select data, does this mean I have lost access completely or is there something I can modify in the database directory to allow me to recover the data?
>
> Thanks for any help or suggestions
>
> Kerry
>
>