[DERBY-3271] Using BUILTIN authentication, I can't log in as database creator after storing credentials in the database.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.3.1.4
• Component/s: JDBC, Services
• Bug behavior facts: Security
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 11/Dec/07 16:05
• Updated: 13/Jul/10 19:20
• Resolved: 06/Jul/09 14:56

Description

Using builtin authentication I am able to create a database and store credentials for 2 users: the original database creator and a second user. After that, I am able to reconnect as the second user but not as the original database creator. My test case follows.

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

Here is my command for running ij with authentication turned on:

java \
-cp $CLASSPATH \
-Dderby.stream.error.logSeverityLevel=0 \
\
-Dderby.connection.requireAuthentication=true \
-Dderby.authentication.provider=BUILTIN \
-Dderby.user.builtindba=dummypassword \
\
org.apache.derby.tools.ij myscript.sql

Here is the first run of my script. This creates the database and stores credentials for 2 users, including the connected user:

ij version 10.4
ij> –
-- First try to connect as builtindba.
–
connect 'jdbc:derby:derby_builtin;create=true;user=builtindba;password=dummypassword';
ij> –
-- If I can't connect as builtindba, try connecting as fred.
–
connect 'jdbc:derby:derby_builtin;create=true;user=fred;password=wilma';
ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
ij> –
-- Store passwords in the database where they will be encrypted.
–
call syscs_util.syscs_set_database_property( 'derby.user.builtindba', 'dummypassword' );
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_database_property( 'derby.user.fred', 'wilma' );
0 rows inserted/updated/deleted
ij> values current_user;
1
--------------------------------------------------------------------------------------------------------------------------------
BUILTINDBA

1 row selected

Here is the second run of my script. This fails to connect as the original user but succeeds as the other user:

ij version 10.4
ij> –
-- First try to connect as builtindba.
–
connect 'jdbc:derby:derby_builtin;create=true;user=builtindba;password=dummypassword';
ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication..
ij> –
-- If I can't connect as builtindba, try connecting as fred.
–
connect 'jdbc:derby:derby_builtin;create=true;user=fred;password=wilma';
WARNING 01J01: Database 'derby_builtin' not created, connection made to existing database instead.
ij> –
-- Store passwords in the database where they will be encrypted.
–
call syscs_util.syscs_set_database_property( 'derby.user.builtindba', 'dummypassword' );
0 rows inserted/updated/deleted
ij> call syscs_util.syscs_set_database_property( 'derby.user.fred', 'wilma' );
0 rows inserted/updated/deleted
ij> values current_user;
1
--------------------------------------------------------------------------------------------------------------------------------
FRED

1 row selected

Issue Links

• duplicates: DERBY-3272 BUILTIN authentication: Passwords stored in a database are not hashed if also defined as system property
• is related to: DERBY-3272 BUILTIN authentication: Passwords stored in a database are not hashed if also defined as system property
• is related to: DERBY-224 System versus Database authentication conflict