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
