Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Message-ID: <4F1544B7.4090407@oracle.com>
Date: Tue, 17 Jan 2012 10:51:51 +0100
From: Kristian Waagan <kristian.waagan@oracle.com>
User-Agent: Mozilla/5.0 (X11; SunOS i86pc;
 rv:8.0) Gecko/20111202 Thunderbird/8.0
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: Authentication and passwords in derby.properties
References: 
 <99FC70E9B6AD72468CD241D1D64D5F0C04497C3A8A@EXCH1-COLO.accelrys.net>
 <x6r4z2jnwt.fsf@oracle.com>
 <99FC70E9B6AD72468CD241D1D64D5F0C04497C3B2C@EXCH1-COLO.accelrys.net>
In-Reply-To: 
 <99FC70E9B6AD72468CD241D1D64D5F0C04497C3B2C@EXCH1-COLO.accelrys.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 16.01.12 16:05, Robert DiPietro wrote:
> Hi Knut,
>
> Thanks for your response. I tried setting the properties with derby completely lowercase and still don't seem to be having much luck with what I am trying to do.
>
> I have tried this a few different ways. Let me try to step through what I'm doing (and trying to do) and maybe you'll catch where I am going wrong:
>
> Here's what my derby.properties looks like (with auth disabled and the system level user disabled).
>
> #derby.connection.requireAuthentication=true
> #derby.authentication.provider=BUILTIN
> #derby.user.adminuser=adminpass
> derby.drda.portNumber=9947
> derby.drda.host=0.0.0.0
> derby.infolog.append=true
> derby.stream.error.file=C:/logs/javaserver_derby.log
>
> We have Derby running inside of a Tomcat process/service. I use RazorSQL as my interface to Derby.
>
> So today I tried:
>
> 1. Create database/user via the connection string:
> jdbc:derby://localhost:9947/mdb;create=true;user=mdb;password=mdb
> without specifying a username/password in the RazorSQL connection profile. This works.
>
> 2. Modify the profile to use the mdb user/pass and remove the connection string parameters and connect. This works.
>
> 3. Issue the following to Derby while logged in as MasterDB:
>
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','true');
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 'true');
> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider', 'BUILTIN');
>
> This works, and I can still log in.

Hi,

When I perform the above steps, I'm not able to log into the database at 
all.
If I additionally do
   CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.mdb', 'mdb');
, I can only connect with that user/pass.

Here's from the ij session:
ij> connect 'jdbc:derby:mdb;create=true;user=mdb;password=mdb'; 

ij> CALL 
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly','true'); 

0 rows inserted/updated/deleted
ij> CALL 
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 
'true');
0 rows inserted/updated/deleted
ij> CALL 
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider', 
'BUILTIN');
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.mdb', 
'mdb');
0 rows inserted/updated/deleted
ij> disconnect; 

ij> connect 'jdbc:derby:mdb;shutdown=true;user=mdb;password=mdb'; 

ERROR 08006: Database 'mdb' shutdown.
ij> connect 'jdbc:derby:mdb';
ERROR 08004: Connection authentication failure occurred.  Reason: 
Invalid authentication..
ij> connect 'jdbc:derby:mdb;user=mdb;password=incorrect';
ERROR 08004: Connection authentication failure occurred.  Reason: 
Invalid authentication..
ij> connect 'jdbc:derby:mdb;user=mdb;password=mdb';
ij> disconnect;
ij> connect 'jdbc:derby:mdb;shutdown=true';
ERROR 08004: Connection authentication failure occurred.  Reason: 
Invalid authentication..
ij> connect 'jdbc:derby:mdb;shutdown=true;user=mdb;password=mdb';
ERROR 08006: Database 'mdb' shutdown.
ij>


When using the BUILTIN scheme, users defined in derby.properties are 
system-wide, whereas users defined as database properties are specific 
to that database.


Regards,
-- 
Kristian

>
> *However* what I want at this point is for no one else to be able to log in access this database or issue commands to Derby besides the "mdb" user that I created. My thought was to I enable authentication in derby.properties. However this then disallows access to the "mdb" user (I assume because the settings in derby.properies supercede the database level properties).
>
> So either I am doing something wrong, or what I want to do is not possible in Derby.
>
> Can I have a single database user that can log in and act against a single database schema without having a user declared in derby.properties in plain text. And can I deny access to Derby/schema to everyone else? If this is possible, can someone walk me through the steps to accomplish it.
>
> Thanks,
>
> Rob DiPietro
>
>
>
> -----Original Message-----
> From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
> Sent: Saturday, January 14, 2012 7:37 AM
> To: Derby Discussion
> Subject: Re: Authentication and passwords in derby.properties
>
> Robert DiPietro<Robert.DiPietro@accelrys.com>  writes:
>
>> Hello!
>>
>> I have been deving against a Derby DB on which requireAuthentication
>> is true, the provider is built-in, and all users have been defined in
>> the derby.properties file (so they are all system level users). This
>> has been extremely convenient in development, however there are
>> obvious concerns about having the password stored in plaintext in a
>> file.
>>
>> Is it possible to require authentication in built-in mode, but have no
>> system level users defined in derby.properties, rather only defined at
>> database level (stored internally on the database)?
>>
>> For example, I have tried disabling requireAuthentication, logging in
>> to the system, creating a db, and then creating a user against it
>> using these commands:
>>
>> CALL
>> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.database.propertiesOnly'
>> ,'true'); CALL
>> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.connection.requireAuthen
>> tication', 'true'); CALL
>> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.authentication.provider'
>> , 'BUILTIN'); CALL
>> SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('Derby.user.username',
>> 'password');
>>
>> Which seems to work, but then if I enable requireAuthentication in
>> derby.properties, I can no longer log in.
>
> Hi Robert,
>
> The property names should start with 'derby' (all lowercase), not 'Derby'. Does it work as expected if you change the case of the initial letter?
>
> --
> Knut Anders
>
>