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 (herse.apache.org: local policy)
Date: Wed, 21 Feb 2007 11:24:44 -0500
From: Kim Haase <Camilla.Haase@Sun.COM>
Subject: Re: Security in Derby
In-reply-to: <7921d3e40702201526vd691fddyb97250c5e6c50181@mail.gmail.com>
Sender: Camilla.Haase@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Reply-to: Camilla.Haase@Sun.COM
Message-id: <45DC724C.7080605@Sun.COM>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <e1d263a70702200922m5291e6ccw4cef77d42408f07e@mail.gmail.com>
 <45DB38A6.40906@sun.com>
 <e1d263a70702201030i2442db4ci2c300bc7c5c5a028@mail.gmail.com>
 <45DB40E9.9050400@bristowhill.com>
 <e1d263a70702201050ga5091bcs66818c29ddd0cd81@mail.gmail.com>
 <54ac72d70702201117j4a01a9d6k24d0728a4c9510bf@mail.gmail.com>
 <e1d263a70702201127y60c5dc80mb21941e230176674@mail.gmail.com>
 <7921d3e40702201526vd691fddyb97250c5e6c50181@mail.gmail.com>
User-Agent: Mail/News 1.5.0.5 (X11/20060813)

It seems that you can configure the network server in three different ways?

1) Setting the derby.drda.securityMechanism property on the command 
line, in the derby.properties file, or in the .bat or .ksh file, as 
documented in 
http://db.apache.org/derby/docs/dev/adminguide/adminguide-single.html#tadminconfigsettingnetwrokserverproperties

2) In a JDBC program, calling the 
org.apache.derby.jdbc.ClientDataSource.setSecurityMechanism method

3) Specifying the connection URL property securityMechanism

With the first two you use symbols --

derby.drda.securityMechanism=STRONG_PASSWORD_SUBSTITUTE_SECURITY

and (I guess)

   org.apache.derby.jdbc.ClientDataSource ds =
      new org.apache.derby.jdbc.ClientDataSource();
   ds.setSecurityMechanism(
org.apache.derby.jdbc.ClientDataSource.STRONG_PASSWORD_SUBSTITUTE_SECURITY);

But with the last one it seems you have to use a numeric literal? This 
seems unfortunate. I wonder if that is why it is not documented?

Kim

Francois Orsini wrote:
> To add on Andrew's excellent info:
> 
> You can also configure the Derby network server to only accept 
> connections with secure authentication, by setting the 
> derby.drda.securityMechanism server property.
> 
> http://db.apache.org/derby/docs/dev/adminguide/adminguide-single.html#tadminconfigsettingnetwrokserverproperties
> derby.drda.securityMechanism=STRONG_PASSWORD_SUBSTITUTE_SECURITY
> 
> Strong (network) password authentication works for the IBM and SUN JVMs.
> 
> Yes, the documentation is lacking examples of securityMechanism being 
> set on the connection URL
> 
> --francois
> 
> On 2/20/07, *Alexander Trauzzi* <atrauzzi@gmail.com 
> <mailto:atrauzzi@gmail.com>> wrote:
> 
>     This is all very helpful.  I will make a few notes however that you
>     can take away from this...
> 
>     The method to implement encrypted passwords is far too complicated
>     and involved for someone like myself who just wants to set up a
>     secure server.
>     This is mainly because nothing is said about what has to be done
>     client side to ensure that the password being transmitted is what
>     the server will end up wanting.  Does the derby client network
>     driver automatically encrypt passwords once the property is placed
>     in the connection string?
> 
>     It seems like there is a glut of property settings being used here
>     on both client and server side and my wires are getting crossed
>     trying to keep them straight.  Ultimately this is impacting how easy
>     it is to configure a derby server.  Perhaps is there an index of
>     connection string and server properties somewhere?
> 
>     How do I distinguish when a setting is for a database, or the system?
> 
>     Do properties that I set via a configuration file get retained
>     between loads - as in, is it a needed only once thing?  Can I delete
>     the file and still have it's settings taking effect on a database
>     between loads?  Entire DB instance?
> 
>     - Alexander Trauzzi
> 
> 
>     On 2/20/07, *Andrew McIntyre * < mcintyre.a@gmail.com
>     <mailto:mcintyre.a@gmail.com>> wrote:
> 
>         On 2/20/07, Alexander Trauzzi < atrauzzi@gmail.com
>         <mailto:atrauzzi@gmail.com>> wrote:
>         >  Awesome, I'm glad I can collect all these hidden links, but
>         the issue of per
>         >  user authentication, similar to what MySQL and other databases
>         offer is
>         >  still blocking me.
> 
>         Perhaps the issue here is that the information you are looking
>         for is
>         not in the Server and Administration guide, but in the Developer's
>         guide?
> 
>         Have you read through the "Derby and Security" section here:
> 
>         http://db.apache.org/derby/docs/10.2/devguide/
> 
>         Specifically, to use Derby's built-in user authentication and
>         create
>         the users and passwords for a database:
> 
>         http://db.apache.org/derby/docs/10.2/devguide/cdevcsecure42374.html
>         <http://db.apache.org/derby/docs/10.2/devguide/cdevcsecure42374.html>
>         http://db.apache.org/derby/docs/10.2/devguide/cdevcsecure21547.html
>         http://db.apache.org/derby/docs/10.2/devguide/cdevcsecure864642.html
>         <http://db.apache.org/derby/docs/10.2/devguide/cdevcsecure864642.html>
> 
>         e.g., to create a user 'andrew' with password of 'newderbyuser' you
>         can call, in ij:
> 
>         CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
>             'derby.user.andrew', 'newderbyuser')
> 
>         Then to enable the builtin authentication, you need to start the
>         database with the following properties set:
> 
>         derby.connection.requireAuthentication=true
>         derby.authentication.provider=BUILTIN
> 
>         You can set these properties at the database level by calling
>         the set
>         database property procedure, e.g. in ij: CALL
>         SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY (
>         'derby.connection.requireAuthentication', 'true' );
> 
>         and then shutting down the database engine and reconnecting to the
>         database, as the derby.connection.requireAuthentication property
>         is a
>         static property. For a discussion of static vs. dynamic properties,
>         see the Tuning guide, specifically:
> 
>         http://db.apache.org/derby/docs/10.2/tuning/ctunsetprop44147.html
>         http://db.apache.org/derby/docs/10.2/tuning/rtunproper27467.html
>         <http://db.apache.org/derby/docs/10.2/tuning/rtunproper27467.html>
> 
>         If you are connecting to the database over a network, you should
>         consider using a non-cleartext password security mechanism,
>         discussed
>         in the server and admin guide here:
> 
>         http://db.apache.org/derby/docs/10.2/adminguide/cadminapps49914.html
> 
>         Anyway now that all the necessary properties are set, you need to
>         connect to the database with the user and password you set, since
>         authentication is enabled, by adding the appropriate user and
>         password
>         attributes to the JDBC URL, e.g. in ij:
> 
>         connect 'jdbc:derby:myDB;user=andrew;password=newderbyuser';
> 
>         Note that this puts the user by default into the schema of their own
>         username. e.g. if you create a table foo after you log in, it
>         will be
>         in the ANDREW schema, i.e. ANDREW.FOO.
> 
>         You can find further details for setting up user authentication
>         in the
>         PDF that was linked earlier, and in the manuals, but those are the
>         basics.
> 
>         Did that help?
> 
>         cheers,
>         andrew
> 
> 
> 
> 
>     -- 
>     _________________________________________________
> 
>         Alexander Trauzzi 
> 
>