User authentication and SQL authorization embedded example

This example, AuthExampleEmbeddedSQLAuth.java,
shows how to use SQL authorization, in addition to
Derby's
built-in user authentication and user authorization, with the embedded
driver.

This example is a single long program. A similar example that uses the client
driver, in
User authentication and SQL authorization client example,
uses one program to set properties and a second program to perform database
operations. Either example would work equally well in the other format.

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.

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 stops and restarts the database so that the
authentication and SQL authorization changes can take effect.

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.

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: