Source code for AuthExampleEmbeddedSQLAuth.java
Derby Developer's Guide
146
The program does the following:
1. Starts Derby and creates a database named
sqlAuthEmbDB
, using the embedded
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.
2. 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.
3. Closes the connection, then stops and restarts the database so that the
authentication and SQL authorization changes can take effect.
4. Tries to connect to the database without a username and password, raising an
exception.
5. Tries to connect to the database as a user with no access, raising an exception.
6. Connects to the database as a user with read-only access; the connection
succeeds, but an attempt to create a table raises an exception.
7. 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.
8. 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.
9. Connects to the database again as
mary
, who then deletes the table.
10. Closes the connection, shuts down the database, then shuts down Derby.
Make sure that the
javac
command is in your path, then compile the program as follows:
javac AuthExampleEmbeddedSQLAuth.java
When you run
AuthExampleEmbeddedSQLAuth
, make sure that
%DERBY_HOME%\lib\derby.jar
(or
$DERBY_HOME/lib/derby.jar
) is in your
classpath. For example, you might use the following command on a Windows system:
java -cp .;%DERBY_HOME%\lib\derby.jar AuthExampleEmbeddedSQLAuth
Source code for
AuthExampleEmbeddedSQLAuth.java
import java.sql.*;
public class AuthExampleEmbeddedSQLAuth {
public static void main(String[] args) {
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbName="sqlAuthEmbDB";
String dbOwner="mary";
String connectionURL = "jdbc:derby:" + dbName +
";user=" + dbOwner + ";create=true";
Connection conn = null;
// Load the driver. This code is not needed if you are using
// JDK 6, because in that environment the driver is loaded
// automatically when the application requests a connection.
try {
Class.forName(driver);
System.out.println(driver + " loaded.");
} catch (java.lang.ClassNotFoundException ce) {
System.err.print("ClassNotFoundException: ");
System.err.println(ce.getMessage());
System.out.println("\n Make sure your CLASSPATH variable " +
"contains %DERBY_HOME%\\lib\\derby.jar " +
"(${DERBY_HOME}/lib/derby.jar).\n");
