's
built-in authentication mechanism is suitable only for development and testing
purposes. It is strongly recommended that production systems rely on LDAP or a
user-defined class for authentication. It is also strongly recommended that
production systems protect network connections with SSL/TLS.
This example uses one program to set properties and a second program to
perform database operations. A similar example that uses the embedded
driver, in
,
is a single long program. Either example would work equally well in the other
format.
See for more
information on using SQL authorization, which allows you to use ANSI SQL
Standard GRANT and REVOKE statements. Compare this example to the one in
,
which does not use SQL authorization.
The first program, AuthExampleClientSQLAuth1.java, does the
following:
- Creates a database named sqlAuthClientDB, using the client
driver. The connection URL creates the database as the user
mary, who is therefore the database owner. After SQL
authorization is enabled, only the database owner will have the right to set and
read database properties.
- Sets database properties that create users with different levels of access
(no access, read-only access, and full access), that require authentication, and
that turn on SQL authorization. The users mary and
sqlsam have full access.
- Closes the connection, then shuts down the database so that the
authentication and SQL authorization changes can take effect.
The second program, AuthExampleClientSQLAuth2.java, does the
following:
- Tries to connect to the database without a username and password, raising
an exception.
- Tries to connect to the database as a user with no access, raising an
exception.
- Connects to the database as a user with read-only access; the connection
succeeds, but an attempt to create a table raises an exception.
- Connects to the database as mary, who has full access; this
user creates and populates a table. This user also grants select and insert
privileges on this table to another user.
- Connects to the database as sqlsam, the user who has been
granted select and insert privileges by mary. This user has
full (that is, read-write) access on the connection level, but has limited
powers for this table because SQL authorization is active. The user successfully
performs select and insert operations on the table, but an attempt to delete a
row from the table raises an exception.
- Connects to the database again as mary, who then deletes
the table.
- Shuts down the database.
Make sure that the javac command is in your path, then
compile the programs as follows:
javac AuthExampleClientSQLAuth1.java
javac AuthExampleClientSQLAuth2.java
Before you run the programs, start the
Network
Server as described in step 2 of "Activity 4: Create and run a JDBC program
using the client driver and Network Server" in
. When you run the
programs, make sure that
%DERBY_HOME%\lib\derbyclient.jar (or
$DERBY_HOME/lib/derbyclient.jar) is in your classpath. For
example, you might use the following commands on a UNIX system:
java -cp .:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClientSQLAuth1
java -cp .:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClientSQLAuth2