Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 32764 invoked from network); 29 Aug 2008 14:23:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 29 Aug 2008 14:23:10 -0000 Received: (qmail 90568 invoked by uid 500); 29 Aug 2008 14:23:08 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 90530 invoked by uid 500); 29 Aug 2008 14:23:08 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 90521 invoked by uid 99); 29 Aug 2008 14:23:08 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 Aug 2008 07:23:08 -0700 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 Aug 2008 14:22:17 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id F11B4238899F; Fri, 29 Aug 2008 07:22:17 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r690270 [1/2] - in /db/derby/docs/trunk/src/devguide: derbydev.ditamap rdevcsecure26537.dita rdevcsecureclientexample.dita rdevcsecuresqlauthclientex.dita rdevcsecuresqlauthembeddedex.dita rdevcsecuresqlauthexs.dita Date: Fri, 29 Aug 2008 14:22:17 -0000 To: derby-commits@db.apache.org From: chaase3@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080829142217.F11B4238899F@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: chaase3 Date: Fri Aug 29 07:22:16 2008 New Revision: 690270 URL: http://svn.apache.org/viewvc?rev=690270&view=rev Log: DERBY-3200: Developer's Guide: Add examples showing use of SQL authorization with user authentication Added these examples (two topics plus an introductory topic) and also modified the existing authentication/authorization examples. Patch: DERBY-3200-6.diff Added: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita (with props) db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita (with props) db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita (with props) Modified: db/derby/docs/trunk/src/devguide/derbydev.ditamap db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita Modified: db/derby/docs/trunk/src/devguide/derbydev.ditamap URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/derbydev.ditamap?rev=690270&r1=690269&r2=690270&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/derbydev.ditamap (original) +++ db/derby/docs/trunk/src/devguide/derbydev.ditamap Fri Aug 29 07:22:16 2008 @@ -2259,6 +2259,10 @@ + + + + Modified: db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita?rev=690270&r1=690269&r2=690270&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita (original) +++ db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita Fri Aug 29 07:22:16 2008 @@ -23,21 +23,24 @@ The following program, AuthExampleEmbedded.java, shows how to turn on, use, and turn off user authentication using 's -built-in user authentication and user authorization. +built-in user authentication and user authorization using the embedded +driver.

See for information on using SQL authorization, which allows you to use ANSI SQL -Standard GRANT and REVOKE statements.

+Standard GRANT and REVOKE statements. See + +for an example similar to this one that uses SQL authorization.

The program does the following:

  1. Starts and creates -a database named jdbcDemoDB, using the embedded driver.
    2. +a database named authEmbDB, using the embedded driver.
  2. Sets database properties that create users with different levels of access (read-only and full access), require authentication, and set the default access level to no access.
    3. -
  3. Closes the connection, then stops and restarts Derby so that the +
  4. Closes the connection, then stops and restarts the database so that the authentication changes can take effect.
  5. Tries to connect to the database without a username and password, raising an exception.
    6. @@ -45,45 +48,50 @@ succeeds, but an attempt to create a table raises an exception.
  6. Connects to the database as a user with full access; this user can create and populate a table.
    7. -
  7. Removes the table, then turns off authentication and removes the users.
    8. -
  8. Closes the connection and shuts down +
  9. Deletes the table.
    10. +
  10. Closes the connection, shuts down the database, then shuts down .

Make sure that the javac command is in your path, then compile the program as follows:

javac AuthExampleEmbedded.java

-

Before you run AuthExampleEmbedded, make +

When you run AuthExampleEmbedded, make sure that %DERBY_HOME%\lib\derby.jar (or -$DERBY_HOME/lib/derby.jar) is in your classpath. Then use -the following command:

-

java AuthExampleEmbedded

+$DERBY_HOME/lib/derby.jar) is in your classpath. For example, +you might use the following command:

+

java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derby.jar AuthExampleEmbedded

Source code for <codeph>AuthExampleEmbedded.java</codeph> - -import java.sql.*; +import java.sql.*; public class AuthExampleEmbedded { public static void main(String[] args) { String driver = "org.apache.derby.jdbc.EmbeddedDriver"; - String dbName="jdbcDemoDB"; + String dbName="authEmbDB"; String connectionURL = "jdbc:derby:" + dbName + ";create=true"; Connection conn = null; - // Load the driver + // 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 e) { + } catch (java.lang.ClassNotFoundException ce) { System.err.print("ClassNotFoundException: "); - System.err.println(e.getMessage()); + 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"); + "contains %DERBY_HOME%\\lib\\derby.jar " + + "(${DERBY_HOME}/lib/derby.jar).\n"); + } catch (Exception ee) { + errorPrintAndExit(ee); } - // Start the database and set up users, then close database + // Create and boot the database and set up users, then shut down + // the database as one of the users with full access try { System.out.println("Trying to connect to " + connectionURL); conn = DriverManager.getConnection(connectionURL); @@ -91,70 +99,59 @@ turnOnBuiltInUsers(conn); - // shut down the database + // close the connection conn.close(); System.out.println("Closed connection"); - /* In embedded mode, an application should shut down Derby. - Shutdown throws the XJ015 exception to confirm success. */ - boolean gotSQLExc = false; + /* Shut down the database to make static properties take + * effect. Because the default connection mode is now + * noAccess, you must specify a user that has access. But + * because requireAuthentication does not take effect until + * you restart the database, the password is not checked. + * + * Database shutdown throws the 08006 exception to confirm + * success. + */ try { - DriverManager.getConnection("jdbc:derby:;shutdown=true"); + DriverManager.getConnection("jdbc:derby:" + dbName + + ";user=sa;password=badpass;shutdown=true"); } catch (SQLException se) { - if ( se.getSQLState().equals("XJ015") ) { - gotSQLExc = true; + if ( !se.getSQLState().equals("08006") ) { + throw se; } } - if (!gotSQLExc) { - System.out.println("Database did not shut down normally"); - } else { - System.out.println("Database shut down normally"); - } - - // force garbage collection to unload the EmbeddedDriver - // so Derby can be restarted - System.gc(); - } catch (Throwable e) { - errorPrint(e); - System.exit(1); + System.out.println("Database shut down normally"); + } catch (SQLException e) { + errorPrintAndExit(e); } // Restart database and confirm that unauthorized users cannot // access it connectionURL = "jdbc:derby:" + dbName; - // Load the driver again - try { - Class.forName(driver).newInstance(); - System.out.println(driver + " loaded."); - } catch (java.lang.ClassNotFoundException e) { - System.err.print("ClassNotFoundException: "); - System.err.println(e.getMessage()); - System.out.println("\n Make sure your CLASSPATH variable " + - "contains %DERBY_HOME%\\lib\\derby.jar (${DERBY_HOME}/lib/derby.jar). \n"); - } catch (Exception ee) { - errorPrint(ee); - } - // Try to log in with no username or password try { - // this should fail + // connection attempt should fail System.out.println("Trying to connect to " + connectionURL + " without username or password"); conn = DriverManager.getConnection(connectionURL); - System.out.println("Connected to database " + dbName); - - // if it doesn't, close statement and connection - conn.close(); - System.out.println("Closed connection"); - } catch (Throwable e) { - errorPrint(e); + System.out.println( + "ERROR: Unexpectedly connected to database " + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("08004")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + } else { + errorPrintAndExit(e); + } } // Log in as a user with read-only access try { // connection should succeed, but create table should fail - String newURL = connectionURL + ";user=guest;password=java5w6x"; + String newURL = connectionURL + + ";user=guest;password=java5w6x"; System.out.println("Trying to connect to " + newURL); conn = DriverManager.getConnection(newURL); System.out.println("Connected to database " + dbName + @@ -162,20 +159,30 @@ Statement s = conn.createStatement(); s.executeUpdate("CREATE TABLE t1(C1 VARCHAR(6))"); - - // if it doesn't, close statement and connection - s.close(); - conn.close(); - System.out.println("Closed connection"); - } catch (Throwable e) { - errorPrint(e); + System.out.println( + "ERROR: Unexpectedly allowed to modify database " + + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("25503")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + try { + conn.close(); + } catch (SQLException ee) { + errorPrintAndExit(ee); + } + } else { + errorPrintAndExit(e); + } } // Log in as a user with full access // Create, update, and query table try { // this should succeed - String newURL = connectionURL + ";user=mary;password=little7xylamb"; + String newURL = connectionURL + + ";user=mary;password=little7xylamb"; System.out.println("Trying to connect to " + newURL); conn = DriverManager.getConnection(newURL); System.out.println("Connected to database " + dbName); @@ -188,191 +195,165 @@ ResultSet rs = s.executeQuery("SELECT * FROM T1"); rs.next(); System.out.println("Value of T1/C1 is " + rs.getString(1)); + s.executeUpdate("DROP TABLE T1"); s.close(); - } catch (Throwable e) { - errorPrint(e); + } catch (SQLException e) { + errorPrintAndExit(e); } - // Remove table, then remove users previously created - // If you don't remove the table, you will never be able to find it, - // because it was created by a user who no longer exists and with a - // default connection mode of noAccess try { - //Statement s = conn.createStatement(); - //s.executeUpdate("DROP TABLE T1"); - //System.out.println("Removed table T1"); - //s.close(); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + errorPrintAndExit(e); + } + } + + /** + * Close connection and shut down database. Since this is embedded + * mode, we must also shut down the Derby system. + * + * @param conn a connection to the database + */ + public static void cleanUpAndShutDown (Connection conn) + throws SQLException { - turnOffBuiltInUsers(conn); + String dbName="authEmbDB"; + String connectionURL = "jdbc:derby:" + dbName; + try { conn.close(); System.out.println("Closed connection"); - // Shut down the database - /* In embedded mode, an application should shut down Derby. - Shutdown throws the XJ015 exception to confirm success. */ - boolean gotSQLExc = false; + // As mary, shut down the database. try { - DriverManager.getConnection("jdbc:derby:;shutdown=true"); + String newURL = connectionURL + + ";user=mary;password=little7xylamb;shutdown=true"; + DriverManager.getConnection(newURL); } catch (SQLException se) { - if ( se.getSQLState().equals("XJ015") ) { - gotSQLExc = true; + if ( !se.getSQLState().equals("08006") ) { + throw se; } } - if (!gotSQLExc) { - System.out.println("Database did not shut down normally"); - } else { - System.out.println("Database shut down normally"); + System.out.println("Database shut down normally"); + + try { + DriverManager.getConnection("jdbc:derby:;shutdown=true"); + } catch (SQLException se) { + if ( !se.getSQLState().equals("XJ015") ) { + throw se; + } } - } catch (Throwable e) { - errorPrint(e); + + System.out.println("Derby system shut down normally"); + } catch (SQLException e) { + errorPrintAndExit(e); } } /** * Turn on built-in user authentication and user authorization. * - * @param conn a connection to the database. + * @param conn a connection to the database */ - public static void turnOnBuiltInUsers(Connection conn) throws SQLException { + public static void turnOnBuiltInUsers(Connection conn) + throws SQLException { + + String setProperty = + "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY("; + String getProperty = + "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY("; + String requireAuth = "'derby.connection.requireAuthentication'"; + String defaultConnMode = + "'derby.database.defaultConnectionMode'"; + String fullAccessUsers = "'derby.database.fullAccessUsers'"; + String readOnlyAccessUsers = + "'derby.database.readOnlyAccessUsers'"; + String provider = "'derby.authentication.provider'"; + String propertiesOnly = "'derby.database.propertiesOnly'"; + System.out.println("Turning on authentication."); Statement s = conn.createStatement(); - // Setting and Confirming requireAuthentication - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication', 'true')"); - ResultSet rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication')"); + // Set and confirm requireAuthentication + s.executeUpdate(setProperty + requireAuth + ", 'true')"); + ResultSet rs = s.executeQuery(getProperty + requireAuth + ")"); rs.next(); System.out.println("Value of requireAuthentication is " + rs.getString(1)); - // Setting authentication scheme to Derby - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.authentication.provider', 'BUILTIN')"); - - // Creating some sample users - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.sa', 'ajaxj3x9')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.guest', 'java5w6x')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.mary', 'little7xylamb')"); - - // Setting default connection mode to no access - // (user authorization) - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode', 'noAccess')"); - // Confirming default connection mode - rs = s.executeQuery ( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode')"); - rs.next(); - System.out.println("Value of defaultConnectionMode is " + - rs.getString(1)); - // Defining read-write users - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.fullAccessUsers', 'sa,mary')"); - - // Defining read-only users - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.readOnlyAccessUsers', 'guest')"); - - // Confirming full-access users - rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.fullAccessUsers')"); - rs.next(); - System.out.println("Value of fullAccessUsers is " + rs.getString(1)); + // Set authentication scheme to Derby builtin + s.executeUpdate(setProperty + provider + ", 'BUILTIN')"); + + // Create some sample users + s.executeUpdate( + setProperty + "'derby.user.sa', 'ajaxj3x9')"); + s.executeUpdate( + setProperty + "'derby.user.guest', 'java5w6x')"); + s.executeUpdate( + setProperty + "'derby.user.mary', 'little7xylamb')"); + + // Define noAccess as default connection mode + s.executeUpdate( + setProperty + defaultConnMode + ", 'noAccess')"); - // Confirming read-only users - rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.readOnlyAccessUsers')"); + // Confirm default connection mode + rs = s.executeQuery(getProperty + defaultConnMode + ")"); rs.next(); - System.out.println("Value of readOnlyAccessUsers is " + + System.out.println("Value of defaultConnectionMode is " + rs.getString(1)); - // We would set the following property to TRUE only - // when we were ready to deploy. - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.propertiesOnly', 'false')"); - s.close(); - } - - /** - * Turn off built-in user authentication and user authorization. - * - * @param conn a connection to the database. - */ - public static void turnOffBuiltInUsers (Connection conn) throws SQLException { - Statement s = conn.createStatement(); - System.out.println("Turning off authentication."); + // Define read-write user + s.executeUpdate( + setProperty + fullAccessUsers + ", 'sa,mary')"); + + // Define read-only user + s.executeUpdate( + setProperty + readOnlyAccessUsers + ", 'guest')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication', 'false')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.authentication.provider', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.sa', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.guest', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.mary', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode', 'fullAccess')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.fullAccessUsers', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.readOnlyAccessUsers', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.propertiesOnly', 'false')"); - - // Confirming requireAuthentication - ResultSet rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication')"); + // Confirm full-access users + rs = s.executeQuery(getProperty + fullAccessUsers + ")"); rs.next(); - System.out.println("Value of requireAuthentication is " + - rs.getString(1)); + System.out.println( + "Value of fullAccessUsers is " + rs.getString(1)); - // Confirming default connection mode - rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode')"); + // Confirm read-only users + rs = s.executeQuery(getProperty + readOnlyAccessUsers + ")"); rs.next(); - System.out.println("Value of defaultConnectionMode is " + - rs.getString(1)); - System.out.println("Turned off all the user-related properties"); + System.out.println( + "Value of readOnlyAccessUsers is " + rs.getString(1)); + + // We would set the following property to TRUE only when we were + // ready to deploy. Setting it to FALSE means that we can always + // override using system properties if we accidentally paint + // ourselves into a corner. + s.executeUpdate(setProperty + propertiesOnly + ", 'false')"); s.close(); } /** Exception reporting methods * with special handling of SQLExceptions */ - static void errorPrint(Throwable e) { + static void errorPrintAndExit(Throwable e) { if (e instanceof SQLException) SQLExceptionPrint((SQLException)e); else { System.out.println("A non-SQL error occurred."); e.printStackTrace(); } - } // END errorPrint + System.exit(1); + } - // Iterates through a stack of SQLExceptions + // Iterate through a stack of SQLExceptions static void SQLExceptionPrint(SQLException sqle) { while (sqle != null) { System.out.println("\n---SQLException Caught---\n"); System.out.println("SQLState: " + (sqle).getSQLState()); System.out.println("Severity: " + (sqle).getErrorCode()); System.out.println("Message: " + (sqle).getMessage()); - sqle.printStackTrace(); sqle = sqle.getNextException(); } - } // END SQLExceptionPrint -} - + } +} Modified: db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita?rev=690270&r1=690269&r2=690270&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita (original) +++ db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita Fri Aug 29 07:22:16 2008 @@ -25,22 +25,25 @@ AuthExampleClient2.java, shows how to turn on, use, and turn off user authentication using 's -built-in user authentication and user authorization. +built-in user authentication and user authorization using the client +driver.

See for information on using SQL authorization, which allows you to use ANSI SQL -Standard GRANT and REVOKE statements.

+Standard GRANT and REVOKE statements. See + +for examples similar to these that use SQL authorization.

The first program, AuthExampleClient1.java, does the following:

    -
  1. Creates a database named jdbcDemoDB, using the client +
  2. Creates a database named authClientDB, using the client driver.
  3. Sets database properties that create users with different levels of access (read-only and full access), require authentication, and set the default access level to no access.
    4. -
  4. Closes the connection.
    5. +
  5. Closes the connection and shuts down the database.

The second program, AuthExampleClient2.java, does the following:

@@ -51,51 +54,57 @@ succeeds, but an attempt to create a table raises an exception.
  • Connects to the database as a user with full access; this user can create and populate a table.
    • -
  • Removes the table, then turns off authentication and removes the users.
    • -
  • Closes the connection.
    • +
  • Removes the table.
    • +
  • Closes the connection and shuts down the database.

    • Make sure that the javac command is in your path, then compile the programs as follows:

    javac AuthExampleClient1.java

    javac AuthExampleClient2.java

    -

    Before you run the programs, make sure that -%DERBY_HOME%\lib\derbyclient.jar (or -$DERBY_HOME/lib/derbyclient.jar) is in your classpath. Then -start the Network +

    Before you run the programs, start the + Network Server as described in "Activity 4: Create and run a JDBC program using the client driver and Network Server" in -. Run the programs as -follows:

    -

    java AuthExampleClient1

    -

    java AuthExampleClient2

    +. When you run the +programs, make sure that +%DERBY_HOME%\lib\derbyclient.jar (or +$DERBY_HOME/lib/derbyclient.jar) is in your classpath. Run the +programs using commands like the following:

    +

    java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient1

    +

    java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient2

    Source code for <codeph>AuthExampleClient1.java</codeph> - -import java.sql.*; +import java.sql.*; public class AuthExampleClient1 { public static void main(String[] args) { String driver = "org.apache.derby.jdbc.ClientDriver"; - String dbName="jdbcDemoDB"; + String dbName="authClientDB"; String connectionURL = "jdbc:derby://localhost:1527/" + dbName + ";create=true"; Connection conn = null; - // Load the driver + // 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 e) { + } catch (java.lang.ClassNotFoundException ce) { System.err.print("ClassNotFoundException: "); - System.err.println(e.getMessage()); + 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"); + "contains %DERBY_HOME%\\lib\\derbyclient.jar " + + "(${DERBY_HOME}/lib/derbyclient.jar).\n"); + } catch (Exception ee) { + errorPrintAndExit(ee); } - // Start the database and set up users, then close connection + // Create and boot the database and set up users, then shut down + // the database as one of the users with full access try { System.out.println("Trying to connect to " + connectionURL); conn = DriverManager.getConnection(connectionURL); @@ -103,13 +112,31 @@ turnOnBuiltInUsers(conn); - // shut down the database + // Close connection conn.close(); System.out.println("Closed connection"); + /* Shut down the database to make static properties take + * effect. Because the default connection mode is now + * noAccess, you must specify a user that has access. But + * because requireAuthentication does not take effect until + * you restart the database, the password is not checked. + * + * Database shutdown throws the 08006 exception to confirm + * success. + */ + try { + DriverManager.getConnection( + "jdbc:derby://localhost:1527/" + dbName + + ";user=sa;password=badpass;shutdown=true"); + } catch (SQLException se) { + if ( !se.getSQLState().equals("08006") ) { + throw se; + } + } + System.out.println("Database shut down normally"); } catch (Throwable e) { - errorPrint(e); - System.exit(1); + errorPrintAndExit(e); } } @@ -118,68 +145,77 @@ * * @param conn a connection to the database. */ - public static void turnOnBuiltInUsers(Connection conn) throws SQLException { + public static void turnOnBuiltInUsers(Connection conn) + throws SQLException { + + String setProperty = + "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY("; + String getProperty = + "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY("; + String requireAuth = "'derby.connection.requireAuthentication'"; + String defaultConnMode = + "'derby.database.defaultConnectionMode'"; + String fullAccessUsers = "'derby.database.fullAccessUsers'"; + String readOnlyAccessUsers = + "'derby.database.readOnlyAccessUsers'"; + String provider = "'derby.authentication.provider'"; + String propertiesOnly = "'derby.database.propertiesOnly'"; + System.out.println("Turning on authentication."); Statement s = conn.createStatement(); - // Setting and Confirming requireAuthentication - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication', 'true')"); - ResultSet rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication')"); + // Set and confirm requireAuthentication + s.executeUpdate(setProperty + requireAuth + ", 'true')"); + ResultSet rs = s.executeQuery(getProperty + requireAuth + ")"); rs.next(); System.out.println("Value of requireAuthentication is " + rs.getString(1)); - // Setting authentication scheme to Derby - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.authentication.provider', 'BUILTIN')"); - // Creating some sample users - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.sa', 'ajaxj3x9')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.guest', 'java5w6x')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.mary', 'little7xylamb')"); + // Set authentication scheme to Derby builtin + s.executeUpdate(setProperty + provider + ", 'BUILTIN')"); - // Setting default connection mode to no access - // (user authorization) - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode', 'noAccess')"); - // Confirming default connection mode - rs = s.executeQuery ( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode')"); + // Create some sample users + s.executeUpdate( + setProperty + "'derby.user.sa', 'ajaxj3x9')"); + s.executeUpdate( + setProperty + "'derby.user.guest', 'java5w6x')"); + s.executeUpdate( + setProperty + "'derby.user.mary', 'little7xylamb')"); + + // Define noAccess as default connection mode + s.executeUpdate( + setProperty + defaultConnMode + ", 'noAccess')"); + + // Confirm default connection mode + rs = s.executeQuery(getProperty + defaultConnMode + ")"); rs.next(); System.out.println("Value of defaultConnectionMode is " + rs.getString(1)); - // Defining read-write users - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.fullAccessUsers', 'sa,mary')"); - - // Defining read-only users - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.readOnlyAccessUsers', 'guest')"); + // Define read-write users + s.executeUpdate( + setProperty + fullAccessUsers + ", 'sa,mary')"); + + // Define read-only user + s.executeUpdate( + setProperty + readOnlyAccessUsers + ", 'guest')"); - // Confirming full-access users - rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.fullAccessUsers')"); + // Confirm full-access users + rs = s.executeQuery(getProperty + fullAccessUsers + ")"); rs.next(); - System.out.println("Value of fullAccessUsers is " + rs.getString(1)); + System.out.println( + "Value of fullAccessUsers is " + rs.getString(1)); - // Confirming read-only users - rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.readOnlyAccessUsers')"); + // Confirm read-only users + rs = s.executeQuery(getProperty + readOnlyAccessUsers + ")"); rs.next(); - System.out.println("Value of readOnlyAccessUsers is " + - rs.getString(1)); + System.out.println( + "Value of readOnlyAccessUsers is " + rs.getString(1)); - // We would set the following property to TRUE only - // when we were ready to deploy. + // We would set the following property to TRUE only when we were + // ready to deploy. Setting it to FALSE means that we can always + // override using system properties if we accidentally paint + // ourselves into a corner. s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.database.propertiesOnly', 'false')"); s.close(); @@ -188,14 +224,15 @@ /** Exception reporting methods * with special handling of SQLExceptions */ - static void errorPrint(Throwable e) { + static void errorPrintAndExit(Throwable e) { if (e instanceof SQLException) SQLExceptionPrint((SQLException)e); else { System.out.println("A non-SQL error occurred."); e.printStackTrace(); } - } // END errorPrint + System.exit(1); + } // Iterates through a stack of SQLExceptions static void SQLExceptionPrint(SQLException sqle) { @@ -204,60 +241,64 @@ System.out.println("SQLState: " + (sqle).getSQLState()); System.out.println("Severity: " + (sqle).getErrorCode()); System.out.println("Message: " + (sqle).getMessage()); - sqle.printStackTrace(); sqle = sqle.getNextException(); } - } // END SQLExceptionPrint -} - + } +} Source code for <codeph>AuthExampleClient2.java</codeph> - -import java.sql.*; +import java.sql.*; public class AuthExampleClient2 { public static void main(String[] args) { String driver = "org.apache.derby.jdbc.ClientDriver"; - String dbName="jdbcDemoDB"; + String dbName="authClientDB"; String connectionURL = "jdbc:derby://localhost:1527/" + dbName; Connection conn = null; // Restart database and confirm that unauthorized users cannot // access it - // Load the driver + // 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).newInstance(); + Class.forName(driver); System.out.println(driver + " loaded."); - } catch (java.lang.ClassNotFoundException e) { + } catch (java.lang.ClassNotFoundException ce) { System.err.print("ClassNotFoundException: "); - System.err.println(e.getMessage()); + 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"); + "contains %DERBY_HOME%\\lib\\derbyclient.jar " + + "(${DERBY_HOME}/lib/derbyclient.jar). \n"); } catch (Exception ee) { - errorPrint(ee); + errorPrintAndExit(ee); } // Try to log in with no username or password try { - // this should fail + // connection attempt should fail System.out.println("Trying to connect to " + connectionURL + " without username or password"); conn = DriverManager.getConnection(connectionURL); - System.out.println("Connected to database " + dbName); - - // if it doesn't, close statement and connection - conn.close(); - System.out.println("Closed connection"); - } catch (Throwable e) { - errorPrint(e); + System.out.println( + "ERROR: Unexpectedly connected to database " + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("08004")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + } else { + errorPrintAndExit(e); + } } // Log in as a user with read-only access try { // connection should succeed, but create table should fail - String newURL = connectionURL + ";user=guest;password=java5w6x"; + String newURL = connectionURL + + ";user=guest;password=java5w6x"; System.out.println("Trying to connect to " + newURL); conn = DriverManager.getConnection(newURL); System.out.println("Connected to database " + dbName + @@ -265,20 +306,30 @@ Statement s = conn.createStatement(); s.executeUpdate("CREATE TABLE t1(C1 VARCHAR(6))"); - - // if it doesn't, close statement and connection - s.close(); - conn.close(); - System.out.println("Closed connection"); - } catch (Throwable e) { - errorPrint(e); + System.out.println( + "ERROR: Unexpectedly allowed to modify database " + + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("25503")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + try { + conn.close(); + } catch (SQLException ee) { + errorPrintAndExit(ee); + } + } else { + errorPrintAndExit(e); + } } // Log in as a user with full access // Create, update, and query table try { // this should succeed - String newURL = connectionURL + ";user=mary;password=little7xylamb"; + String newURL = connectionURL + + ";user=mary;password=little7xylamb"; System.out.println("Trying to connect to " + newURL); conn = DriverManager.getConnection(newURL); System.out.println("Connected to database " + dbName); @@ -291,89 +342,63 @@ ResultSet rs = s.executeQuery("SELECT * FROM T1"); rs.next(); System.out.println("Value of T1/C1 is " + rs.getString(1)); + s.executeUpdate("DROP TABLE T1"); s.close(); - } catch (Throwable e) { - errorPrint(e); + } catch (SQLException e) { + errorPrintAndExit(e); } - // Remove table, then remove users previously created - // If you don't remove the table, you will never be able to find it, - // because it was created by a user who no longer exists and with a - // default connection mode of noAccess try { - Statement s = conn.createStatement(); - s.executeUpdate("DROP TABLE T1"); - System.out.println("Removed table T1"); - s.close(); - - turnOffBuiltInUsers(conn); - - conn.close(); - System.out.println("Closed connection"); - } catch (Throwable e) { - errorPrint(e); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + errorPrintAndExit(e); } } - /** - * Turn off built-in user authentication and user authorization. + /** + * Close connection and shut down database. * - * @param conn a connection to the database. + * @param conn a connection to the database */ - public static void turnOffBuiltInUsers (Connection conn) throws SQLException { - Statement s = conn.createStatement(); - System.out.println("Turning off authentication."); + public static void cleanUpAndShutDown (Connection conn) + throws SQLException { - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication', 'false')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.authentication.provider', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.sa', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.guest', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.user.mary', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode', 'fullAccess')"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.fullAccessUsers', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.readOnlyAccessUsers', null)"); - s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + - "'derby.database.propertiesOnly', 'false')"); + String dbName="authClientDB"; + String connectionURL = "jdbc:derby://localhost:1527/" + dbName; - // Confirming requireAuthentication - ResultSet rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.connection.requireAuthentication')"); - rs.next(); - System.out.println("Value of requireAuthentication is " + - rs.getString(1)); + try { + conn.close(); + System.out.println("Closed connection"); - // Confirming default connection mode - rs = s.executeQuery( - "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + - "'derby.database.defaultConnectionMode')"); - rs.next(); - System.out.println("Value of defaultConnectionMode is " + - rs.getString(1)); - System.out.println("Turned off all the user-related properties"); - s.close(); + // As mary, shut down the database. + try { + String newURL = connectionURL + + ";user=mary;password=little7xylamb;shutdown=true"; + DriverManager.getConnection(newURL); + } catch (SQLException se) { + if ( !se.getSQLState().equals("08006") ) { + throw se; + } + } + System.out.println("Database shut down normally"); + } catch (SQLException e) { + errorPrintAndExit(e); + } } /** Exception reporting methods * with special handling of SQLExceptions */ - static void errorPrint(Throwable e) { + static void errorPrintAndExit(Throwable e) { if (e instanceof SQLException) SQLExceptionPrint((SQLException)e); else { System.out.println("A non-SQL error occurred."); e.printStackTrace(); } - } // END errorPrint + System.exit(1); + } // Iterates through a stack of SQLExceptions static void SQLExceptionPrint(SQLException sqle) { @@ -382,11 +407,9 @@ System.out.println("SQLState: " + (sqle).getSQLState()); System.out.println("Severity: " + (sqle).getErrorCode()); System.out.println("Message: " + (sqle).getMessage()); - sqle.printStackTrace(); sqle = sqle.getNextException(); } - } // END SQLExceptionPrint -} - + } +}     Added: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita?rev=690270&view=auto ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita (added) +++ db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita Fri Aug 29 07:22:16 2008 @@ -0,0 +1,552 @@ + + + + + +User authentication and SQL authorization client example +The following pair of programs, +AuthExampleClientSQLAuth1.java and +AuthExampleClientSQLAuth2.java, show how to use SQL +authorization, in addition to +'s +built-in user authentication and user authorization, with the client driver. + + +
    +

    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:

    +
      +
    1. 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.
      2. +
    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. +
    3. Closes the connection, then shuts down the database so that the +authentication and SQL authorization changes can take effect.
      4. +
    +

    The second program, AuthExampleClientSQLAuth2.java, does the +following:

    +
      +
    1. Tries to connect to the database without a username and password, raising +an exception.
      2. +
    2. Tries to connect to the database as a user with no access, raising an +exception.
      3. +
    3. Connects to the database as a user with read-only access; the connection +succeeds, but an attempt to create a table raises an exception.
      4. +
    4. 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.
      5. +
    5. 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.
      6. +
    6. Connects to the database again as mary, who then deletes +the table.
      7. +
    7. Shuts down the database.
      8. +
    +

    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 "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. Run the +programs using commands like the following:

    +

    java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClientSQLAuth1

    +

    java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClientSQLAuth2

    +
    +Source code for <codeph>AuthExampleClientSQLAuth1.java</codeph> +import java.sql.*; + +public class AuthExampleClientSQLAuth1 { + + public static void main(String[] args) { + + String driver = "org.apache.derby.jdbc.ClientDriver"; + String dbName="sqlAuthClientDB"; + String dbOwner="mary"; + String connectionURL = "jdbc:derby://localhost:1527/" + 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\\derbyclient.jar " + + "(${DERBY_HOME}/lib/derbyclient.jar).\n"); + } catch (Exception ee) { + errorPrintAndExit(ee); + } + + // Create and boot the database as user mary (who then becomes + // the database owner), set up users and then shut down the + // database + try { + System.out.println("Trying to connect to " + connectionURL); + conn = DriverManager.getConnection(connectionURL); + System.out.println("Connected to database " + connectionURL); + + turnOnBuiltInUsers(conn); + + // Close connection + conn.close(); + System.out.println("Closed connection"); + + /* Shut down the database to make static properties take + * effect. Because the default connection mode is now + * noAccess, you must specify a user that has access. But + * because requireAuthentication and sqlAuthorization do not + * take effect until you restart the database, you do not + * need to specify a password. + * + * Database shutdown throws the 08006 exception to confirm + * success. + */ + try { + DriverManager.getConnection( + "jdbc:derby://localhost:1527/" + dbName + + ";user=mary;shutdown=true"); + } catch (SQLException se) { + if ( !se.getSQLState().equals("08006") ) { + throw se; + } + } + System.out.println("Database shut down normally"); + } catch (Throwable e) { + errorPrintAndExit(e); + } + } + + /** + * Turn on built-in user authentication and SQL authorization. + * + * Default connection mode is fullAccess, but SQL authorization + * restricts access to the owners of database objects. + * + * @param conn a connection to the database + */ + public static void turnOnBuiltInUsers(Connection conn) + throws SQLException { + + String setProperty = + "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY("; + String getProperty = + "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY("; + String requireAuth = "'derby.connection.requireAuthentication'"; + String sqlAuthorization = "'derby.database.sqlAuthorization'"; + String defaultConnMode = + "'derby.database.defaultConnectionMode'"; + String fullAccessUsers = "'derby.database.fullAccessUsers'"; + String readOnlyAccessUsers = + "'derby.database.readOnlyAccessUsers'"; + String provider = "'derby.authentication.provider'"; + String propertiesOnly = "'derby.database.propertiesOnly'"; + + System.out.println( + "Turning on authentication and SQL authorization."); + Statement s = conn.createStatement(); + + // Set requireAuthentication + s.executeUpdate(setProperty + requireAuth + ", 'true')"); + // Set sqlAuthorization + s.executeUpdate(setProperty + sqlAuthorization + ", 'true')"); + + // Retrieve and display property values + ResultSet rs = s.executeQuery(getProperty + requireAuth + ")"); + rs.next(); + System.out.println( + "Value of requireAuthentication is " + rs.getString(1)); + + rs = s.executeQuery(getProperty + sqlAuthorization + ")"); + rs.next(); + System.out.println( + "Value of sqlAuthorization is " + rs.getString(1)); + + // Set authentication scheme to Derby builtin + s.executeUpdate(setProperty + provider + ", 'BUILTIN')"); + + // Create some sample users + s.executeUpdate( + setProperty + "'derby.user.sa', 'ajaxj3x9')"); + s.executeUpdate( + setProperty + "'derby.user.guest', 'java5w6x')"); + s.executeUpdate( + setProperty + "'derby.user.mary', 'little7xylamb')"); + s.executeUpdate( + setProperty + "'derby.user.sqlsam', 'light8q9bulb')"); + + // Define noAccess as default connection mode + s.executeUpdate( + setProperty + defaultConnMode + ", 'noAccess')"); + + // Confirm default connection mode + rs = s.executeQuery(getProperty + defaultConnMode + ")"); + rs.next(); + System.out.println("Value of defaultConnectionMode is " + + rs.getString(1)); + + // Define read-write users + s.executeUpdate( + setProperty + fullAccessUsers + ", 'sqlsam,mary')"); + + // Define read-only user + s.executeUpdate( + setProperty + readOnlyAccessUsers + ", 'guest')"); + + // Therefore, user sa has no access + + // Confirm full-access users + rs = s.executeQuery(getProperty + fullAccessUsers + ")"); + rs.next(); + System.out.println( + "Value of fullAccessUsers is " + rs.getString(1)); + + // Confirm read-only users + rs = s.executeQuery(getProperty + readOnlyAccessUsers + ")"); + rs.next(); + System.out.println( + "Value of readOnlyAccessUsers is " + rs.getString(1)); + + // We would set the following property to TRUE only when we were + // ready to deploy. Setting it to FALSE means that we can always + // override using system properties if we accidentally paint + // ourselves into a corner. + s.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + + "'derby.database.propertiesOnly', 'false')"); + s.close(); + } + + /** Exception reporting methods + * with special handling of SQLExceptions + */ + static void errorPrintAndExit(Throwable e) { + if (e instanceof SQLException) + SQLExceptionPrint((SQLException)e); + else { + System.out.println("A non-SQL error occurred."); + e.printStackTrace(); + } + System.exit(1); + } + + // Iterate through a stack of SQLExceptions + static void SQLExceptionPrint(SQLException sqle) { + while (sqle != null) { + System.out.println("\n---SQLException Caught---\n"); + System.out.println("SQLState: " + (sqle).getSQLState()); + System.out.println("Severity: " + (sqle).getErrorCode()); + System.out.println("Message: " + (sqle).getMessage()); + sqle = sqle.getNextException(); + } + } +} +Source code for <codeph>AuthExampleClientSQLAuth2.java</codeph> +import java.sql.*; + +public class AuthExampleClientSQLAuth2 { + + public static void main(String[] args) { + + String driver = "org.apache.derby.jdbc.ClientDriver"; + String dbName="sqlAuthClientDB"; + String dbOwner="mary"; + String connectionURL = "jdbc:derby://localhost:1527/" + dbName; + Connection conn = null; + + // Restart database and confirm that unauthorized users cannot + // access it + + // 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\\derbyclient.jar " + + "(${DERBY_HOME}/lib/derbyclient.jar). \n"); + } catch (Exception ee) { + errorPrintAndExit(ee); + } + + // Try to log in with no username or password + try { + // connection attempt should fail + System.out.println("Trying to connect to " + connectionURL + + " without username or password"); + conn = DriverManager.getConnection(connectionURL); + System.out.println( + "ERROR: Unexpectedly connected to database " + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("08004")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + } else { + errorPrintAndExit(e); + } + } + + // Try to log in as a valid user with noAccess + try { + // connection attempt should fail + String newURL = connectionURL + ";user=sa;password=ajaxj3x9"; + System.out.println("Trying to connect to " + newURL); + conn = DriverManager.getConnection(newURL); + System.out.println( + "ERROR: Unexpectedly allowed to connect to database " + + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("08004")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + } else { + errorPrintAndExit(e); + } + } + + // Log in as a user with read-only access + try { + // connection should succeed, but create table should fail + String newURL = connectionURL + + ";user=guest;password=java5w6x"; + System.out.println("Trying to connect to " + newURL); + conn = DriverManager.getConnection(newURL); + System.out.println("Connected to database " + dbName + + " with read-only access"); + + Statement s = conn.createStatement(); + s.executeUpdate( + "CREATE TABLE accessibletbl(textcol VARCHAR(6))"); + System.out.println( + "ERROR: Unexpectedly allowed to modify database " + + dbName); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("25503")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + try { + conn.close(); + } catch (SQLException ee) { + errorPrintAndExit(ee); + } + } else { + errorPrintAndExit(e); + } + } + + // Log in as a user with full access + // Create, update, and query table + // Grant select and insert privileges to another user + try { + // this should succeed + String newURL = connectionURL + + ";user=mary;password=little7xylamb"; + System.out.println("Trying to connect to " + newURL); + conn = DriverManager.getConnection(newURL); + System.out.println("Connected to database " + dbName); + + Statement s = conn.createStatement(); + + s.executeUpdate( + "CREATE TABLE accessibletbl(textcol VARCHAR(6))"); + System.out.println("Created table accessibletbl"); + s.executeUpdate("INSERT INTO accessibletbl VALUES('hello')"); + + ResultSet rs = s.executeQuery("SELECT * FROM accessibletbl"); + rs.next(); + System.out.println("Value of accessibletbl/textcol is " + + rs.getString(1)); + + // grant insert privileges on table to user sqlsam + s.executeUpdate( + "GRANT SELECT, INSERT ON accessibletbl TO sqlsam"); + System.out.println( + "Granted select/insert privileges to sqlsam"); + + s.close(); + conn.close(); + } catch (SQLException e) { + errorPrintAndExit(e); + } + + // Log in as user with select and insert privileges on the table, + // but not delete privileges + try { + String newURL = + connectionURL + ";user=sqlsam;password=light8q9bulb"; + System.out.println("Trying to connect to " + newURL); + conn = DriverManager.getConnection(newURL); + System.out.println("Connected to database " + dbName); + + // look at table + Statement s = conn.createStatement(); + ResultSet rs = + s.executeQuery("SELECT * FROM mary.accessibletbl"); + rs.next(); + System.out.println("Value of accessibletbl/textcol is " + + rs.getString(1)); + + s.executeUpdate( + "INSERT INTO mary.accessibletbl VALUES('sam')"); + System.out.println("Inserted string into table"); + + rs = s.executeQuery("SELECT * FROM mary.accessibletbl"); + while (rs.next()) { + System.out.println("Value of accessibletbl/textcol is " + + rs.getString(1)); + } + s.close(); + } catch (SQLException e) { + errorPrintAndExit(e); + } + + try { + Statement s = conn.createStatement(); + + // this should fail + s.executeUpdate("DELETE FROM mary.accessibletbl " + + "WHERE textcol = 'hello'"); + System.out.println("ERROR: Unexpectedly allowed to DELETE " + + "table mary.accessibletbl"); + cleanUpAndShutDown(conn); + } catch (SQLException e) { + if (e.getSQLState().equals("42500")) { + System.out.println("Correct behavior: SQLException: " + + e.getMessage()); + try { + conn.close(); + } catch (SQLException ee) { + errorPrintAndExit(ee); + } + } else { + errorPrintAndExit(e); + } + } + + /* Log in again as mary, delete table + */ + try { + String newURL = connectionURL + + ";user=mary;password=little7xylamb"; + System.out.println("Trying to connect to " + newURL); + conn = DriverManager.getConnection(newURL); + System.out.println("Connected to database " + dbName); + + Statement s = conn.createStatement(); + s.executeUpdate("DROP TABLE accessibletbl"); + System.out.println("Removed table accessibletbl"); + s.close(); + } catch (SQLException e) { + errorPrintAndExit(e); + } + + try { + cleanUpAndShutDown(conn); + } catch (SQLException e) { + errorPrintAndExit(e); + } + } + + /** Close connection and shut down database. + * + * @param conn a connection to the database + */ + public static void cleanUpAndShutDown (Connection conn) + throws SQLException { + + String dbName="sqlAuthClientDB"; + String dbOwner="mary"; + String connectionURL = "jdbc:derby://localhost:1527/" + dbName; + + try { + conn.close(); + System.out.println("Closed connection"); + + // As mary, the database owner, shut down the database. + try { + String newURL = connectionURL + ";user=" + dbOwner + + ";password=little7xylamb;shutdown=true"; + DriverManager.getConnection(newURL); + } catch (SQLException se) { + if ( !se.getSQLState().equals("08006") ) { + throw se; + } + } + System.out.println("Database shut down normally"); + } catch (SQLException e) { + errorPrintAndExit(e); + } + } + + /** Exception reporting methods + * with special handling of SQLExceptions + */ + static void errorPrintAndExit(Throwable e) { + if (e instanceof SQLException) + SQLExceptionPrint((SQLException)e); + else { + System.out.println("A non-SQL error occurred."); + e.printStackTrace(); + } + System.exit(1); + } + + // Iterate through a stack of SQLExceptions + static void SQLExceptionPrint(SQLException sqle) { + while (sqle != null) { + System.out.println("\n---SQLException Caught---\n"); + System.out.println("SQLState: " + (sqle).getSQLState()); + System.out.println("Severity: " + (sqle).getErrorCode()); + System.out.println("Message: " + (sqle).getMessage()); + sqle = sqle.getNextException(); + } + } +} +     +     Propchange: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita ------------------------------------------------------------------------------ svn:eol-style = native