db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Embretsen <John.Embret...@Sun.COM>
Subject Re: Password Authentication Failure
Date Fri, 25 May 2007 08:57:38 GMT
Hi Dan,

I tried to understand what you are trying to do and what may be causing your 
problems. I think the authentication scheme you have described works as 
expected, and that it is mainly a matter of understanding what the manuals are 
trying to convey. And the manuals may be confusing, so I don't blame you ;)

First, let me try to answer the questions from your first posting:

 > However, I came upon a note on page 9 of "Tuning Derby" dated 12-11-06.
 > This notes says "If you pass in a Properties object as an argument to the
 > DriverManager.getConnection call when connecting to the database, those
 > properties are used as a database connection URL attribute, not as
 > properties
 > of the type discussed in this book".

I think what this means is that you can set connection URL attributes by using 
the Java concept of properties, using a Properties object. The attributes you 
set on the URL are referred to as attributes in the manual. What is referred to 
as properties in Derby are different, and are usually not relevant for a pure 
client application, but only for the JVM running the Derby engine and/or server.

Derby properties are described in the Tuning guide, e.g.:
http://db.apache.org/derby/docs/dev/tuning/ctunproper22250.html
and in the Server and Administration guide, e.g:
http://db.apache.org/derby/docs/dev/adminguide/tadminconfigsettingnetwrokserverproperties.html

Derby connection attributes are described in the Development guide, e.g:

http://db.apache.org/derby/docs/dev/devguide/cdevdvlp51654.html

You can set the _attributes_ using a Properties object in the getConnection() 
call, but if you try to set any of the _properties_ this way, it will probably 
have no effect (at least not in a pure client setting).

 > I am not sure how to interpret that statement. However, it seems to
 > imply that:
 >
 >     1. It is not possible to authenticate using system-wide properties
 > by passing a password name/value pair as an attribute.

I'm not sure I understand. If you specify users and passwords as well as 
authentication provider and the "require authentication" property in a 
derby.properties file before starting the server, these properties are 
system-wide. Supplying user/password pairs as connection attributes (for example 
as part of a Properties object) using the Client Driver will (should) then work.

 >     2. The only way to validate by passing an attribute seems to be at
 > the database level.
 >
 >     3. I am assuming the use of the 'BUILTIN' authentication provider.
 >
 > If I create the database using user=dan & password=danpass during the
 > creation process, I can connect to the database by passing the
 > Properties object
 > and setting derby.connection.requireAuthentication=true. If I don't set
 > the authentication and don't pass the Properties object, I can connect
 > to the database.

Where do you set derby.connection.requireAuthentication=true? If you set it in 
the client application it will to my understanding not have any effect.

If you set derby.connection.requireAuthentication=true in your derby.properties 
file (or some other way in relation to the server JVM), you will not be able to 
connect with invalid user/password or no user/password:

"Connection authentication failure occurred.  Reason: userid or password invalid."

Now, to you second posting:

> I can connect to the database and select the data in the table without 
> problem under both database configurations. If I set 
> 'derby.connection.requireAuthentication=true',
> then I cannot connect at all to the database created without a 
> name/password even using 'APP' and even adding 'derby.user.dan=danpass' 
> in the derby.properties
> file. I can connect and select in the database created with a user 
> name/password as long as I use that name/password combination. If I add 
> another name in the
> derby.properties file, using derby.authentication.provider=BUILTIN I 
> cannot connnect and get the Schema error.

You get the schema error because when you create a table in a database, that 
table will by default be created in a schema that has the same name as the user 
name. This schema will be created if it does not exist. If no user is specified, 
the default schema 'APP' will be used.

So, if you create a database with a URL like this:

jdbc:derby://localhost:1527/mydatabase;create=true;user=dan;password=danpass

and create the table mytable ("CREATE TABLE mytable(a int)"), the table mytable 
will be in the schema 'DAN'.

If you then try to connect to the same database using another (valid) user, e.g.:

jdbc:derby://localhost:1527/mydatabase;create=true;user=john;password=johnpass

you will get the connection, but if you try to perform queries against mytable 
without specifying schema, like this:

SELECT * FROM mytable

then Derby won't know where to find mytable. By default it will look in the 
user's default schema, but since there is no schema called 'JOHN' in this case, 
an exception will be thrown.

If you do

SELECT * FROM DAN.mytable

it will (should) work, even for user john.

If you created the table in the APP schema (no user specified when creating), 
then use

SELECT * FROM APP.mytable


> The connection statement is as follows:
>  
> Properties prop = new Properties();
> prop.set("user","dan");
> prop.set("password","danpass");

Do you mean prop.setProperty(...)? I cannot see a set() method in 
java.util.Properties.

> String driver = "org.apache.derby.jdbc.ClientDriver";
> String dbURL = 
> "jdbc:derby://192.168.1.99:1527/usr/local/derby/database/secondDB";

Looks like you intended the database location to be absolute, but in that case 
the URL is lacking an extra forward slash after the port number. Just mentioning 
this in case you suddenly discover that your database is not where you thought 
it would be.

I hope this helps, but don't hesitate to ask the list if you have further questions!



-- 
John


Mime
View raw message