db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chaa...@apache.org
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 GMT
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 @@
 <topicref href="rdevcsecure26537.dita" navtitle="User authentication and authorization embedded example">
 </topicref>
 </topicref>
+<topicref href="rdevcsecuresqlauthexs.dita" navtitle="User authentication examples using SQL authorization">
+<topicref href="rdevcsecuresqlauthclientex.dita" navtitle="User authentication and SQL authorization client example"></topicref>
+<topicref href="rdevcsecuresqlauthembeddedex.dita" navtitle="User authentication and SQL authorization embedded example"></topicref>
+</topicref>
 </topicref>
 <topicref href="cdevcbabejdfj.dita" navtitle="Running Derby under a security manager">
 <topicref href="cdevbabejgjd.dita" navtitle="Granting permissions to Derby">

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 @@
 <shortdesc>The following program, <codeph>AuthExampleEmbedded.java</codeph>,
 shows how to turn on, use, and turn off user authentication using
 <ph conref="../conrefs.dita#prod/productshortname"></ph>'s
-built-in user authentication and user authorization.</shortdesc>
+built-in user authentication and user authorization using the embedded
+driver.</shortdesc>
 <prolog></prolog>
 <refbody>
 <section>
 <p>See <xref href="cdevcsecure36595.dita#cdevcsecure36595"></xref> for
 information on using SQL authorization, which allows you to use ANSI SQL
-Standard GRANT and REVOKE statements.</p>
+Standard GRANT and REVOKE statements. See 
+<xref href="rdevcsecuresqlauthembeddedex.dita#rdevcsecuresqlauthembeddedex"></xref>
+for an example similar to this one that uses SQL authorization.</p>
 <p>The program does the following:</p>
 <ol>
 <li>Starts <ph conref="../conrefs.dita#prod/productshortname"></ph> and creates
-a database named <codeph>jdbcDemoDB</codeph>, using the embedded driver.</li>
+a database named <codeph>authEmbDB</codeph>, using the embedded driver.</li>
 <li>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.</li>
-<li>Closes the connection, then stops and restarts Derby so that the
+<li>Closes the connection, then stops and restarts the database so that the
 authentication changes can take effect.</li>
 <li>Tries to connect to the database without a username and password, raising
 an exception.</li>
@@ -45,45 +48,50 @@
 succeeds, but an attempt to create a table raises an exception.</li>
 <li>Connects to the database as a user with full access; this user can create
 and populate a table.</li>
-<li>Removes the table, then turns off authentication and removes the users.</li>
-<li>Closes the connection and shuts down
+<li>Deletes the table.</li>
+<li>Closes the connection, shuts down the database, then shuts down
 <ph conref="../conrefs.dita#prod/productshortname"></ph>.</li>
 </ol>
 <p>Make sure that the <codeph>javac</codeph> command is in your path, then
 compile the program as follows:
 </p>
 <p><userinput>javac AuthExampleEmbedded.java</userinput></p>
-<p>Before you run <codeph>AuthExampleEmbedded</codeph>, make
+<p>When you run <codeph>AuthExampleEmbedded</codeph>, make
 sure that <codeph>%DERBY_HOME%\lib\derby.jar</codeph> (or
-<codeph>$DERBY_HOME/lib/derby.jar</codeph>) is in your classpath. Then use
-the following command:</p>
-<p><userinput>java AuthExampleEmbedded</userinput></p>
+<codeph>$DERBY_HOME/lib/derby.jar</codeph>) is in your classpath. For example,
+you might use the following command:</p>
+<p><userinput>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derby.jar AuthExampleEmbedded</userinput></p>
 </section>
 <example><title>Source code for <codeph>AuthExampleEmbedded.java</codeph></title>
-<codeblock>
-import java.sql.*;
+<codeblock>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
-}
-</codeblock></example>
+    }
+}</codeblock></example>
 </refbody>
 </reference>

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 @@
 <codeph>AuthExampleClient2.java</codeph>, shows how to turn on, use, and turn
 off user authentication using
 <ph conref="../conrefs.dita#prod/productshortname"></ph>'s
-built-in user authentication and user authorization. </shortdesc>
+built-in user authentication and user authorization using the client
+driver.</shortdesc>
 <prolog></prolog>
 <refbody>
 <section>
 <p>See <xref href="cdevcsecure36595.dita#cdevcsecure36595"></xref> for
 information on using SQL authorization, which allows you to use ANSI SQL
-Standard GRANT and REVOKE statements.</p>
+Standard GRANT and REVOKE statements. See
+<xref href="rdevcsecuresqlauthclientex.dita#rdevcsecuresqlauthclientex"></xref>
+for examples similar to these that use SQL authorization.</p>
 <p>The first program, <codeph>AuthExampleClient1.java</codeph>, does the
 following:</p>
 <ol>
-<li>Creates a database named <codeph>jdbcDemoDB</codeph>, using the client
+<li>Creates a database named <codeph>authClientDB</codeph>, using the client
 driver.</li>
 <li>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.</li>
-<li>Closes the connection.</li>
+<li>Closes the connection and shuts down the database.</li>
 </ol>
 <p>The second program, <codeph>AuthExampleClient2.java</codeph>, does the
 following:</p>
@@ -51,51 +54,57 @@
 succeeds, but an attempt to create a table raises an exception.</li>
 <li>Connects to the database as a user with full access; this user can create
 and populate a table.</li>
-<li>Removes the table, then turns off authentication and removes the users.</li>
-<li>Closes the connection.</li>
+<li>Removes the table.</li>
+<li>Closes the connection and shuts down the database.</li>
 </ol>
 <p>Make sure that the <codeph>javac</codeph> command is in your path, then
 compile the programs as follows:
 </p>
 <p><userinput>javac AuthExampleClient1.java</userinput></p>
 <p><userinput>javac AuthExampleClient2.java</userinput></p>
-<p>Before you run the programs, make sure that
-<codeph>%DERBY_HOME%\lib\derbyclient.jar</codeph> (or
-<codeph>$DERBY_HOME/lib/derbyclient.jar</codeph>) is in your classpath. Then
-start the <ph conref="../conrefs.dita#prod/productshortname"></ph> Network
+<p>Before you run the programs, start the
+<ph conref="../conrefs.dita#prod/productshortname"></ph> Network
 Server as described in "Activity 4: Create and run a JDBC program using the
 client driver and Network Server" in
-<ph conref="../conrefs.dita#pub/citgetstart"></ph>. Run the programs as
-follows:</p>
-<p><userinput>java AuthExampleClient1</userinput></p>
-<p><userinput>java AuthExampleClient2</userinput></p>
+<ph conref="../conrefs.dita#pub/citgetstart"></ph>. When you run the
+programs, make sure that
+<codeph>%DERBY_HOME%\lib\derbyclient.jar</codeph> (or
+<codeph>$DERBY_HOME/lib/derbyclient.jar</codeph>) is in your classpath. Run the
+programs using commands like the following:</p>
+<p><userinput>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient1</userinput></p>
+<p><userinput>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClient2</userinput></p>
 </section>
 <example><title>Source code for <codeph>AuthExampleClient1.java</codeph></title>
-<codeblock>
-import java.sql.*;
+<codeblock>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
-}
-</codeblock></example>
+    }
+}</codeblock></example>
 <example><title>Source code for <codeph>AuthExampleClient2.java</codeph></title>
-<codeblock>
-import java.sql.*;
+<codeblock>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
-}
-</codeblock></example>
+    }
+}</codeblock></example>
 </refbody>
 </reference>

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 @@
+<?xml version="1.0" encoding="utf-8"?>
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<!-- 
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at      
+
+   http://www.apache.org/licenses/LICENSE-2.0  
+
+Unless required by applicable law or agreed to in writing, software  
+distributed under the License is distributed on an "AS IS" BASIS,  
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  
+See the License for the specific language governing permissions and  
+limitations under the License.
+-->
+<reference id="rdevcsecuresqlauthclientex" xml:lang="en-us">
+<title>User authentication and SQL authorization client example</title>
+<shortdesc>The following pair of programs,
+<codeph>AuthExampleClientSQLAuth1.java</codeph> and
+<codeph>AuthExampleClientSQLAuth2.java</codeph>, show how to use SQL
+authorization, in addition to 
+<ph conref="../conrefs.dita#prod/productshortname"></ph>'s
+built-in user authentication and user authorization, with the client driver.</shortdesc>
+<prolog></prolog>
+<refbody>
+<section>
+<p>See <xref href="cdevcsecure36595.dita#cdevcsecure36595"></xref> 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
+<xref href="rdevcsecureclientexample.dita#rdevcsecureclientexample"></xref>,
+which does not use SQL authorization.</p>
+<p>The first program, <codeph>AuthExampleClientSQLAuth1.java</codeph>, does the
+following:</p>
+<ol>
+<li>Creates a database named <codeph>sqlAuthClientDB</codeph>, using the client
+driver. The connection URL creates the database as the user
+<codeph>mary</codeph>, 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.</li>
+<li>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 <codeph>mary</codeph> and
+<codeph>sqlsam</codeph> have full access.</li>
+<li>Closes the connection, then shuts down the database so that the
+authentication and SQL authorization changes can take effect.</li>
+</ol>
+<p>The second program, <codeph>AuthExampleClientSQLAuth2.java</codeph>, does the
+following:</p>
+<ol>
+<li>Tries to connect to the database without a username and password, raising
+an exception.</li>
+<li>Tries to connect to the database as a user with no access, raising an
+exception.</li>
+<li>Connects to the database as a user with read-only access; the connection
+succeeds, but an attempt to create a table raises an exception.</li>
+<li>Connects to the database as <codeph>mary</codeph>, 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.</li>
+<li>Connects to the database as <codeph>sqlsam</codeph>, the user who has been
+granted select and insert privileges by <codeph>mary</codeph>. 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.</li>
+<li>Connects to the database again as <codeph>mary</codeph>, who then deletes
+the table.</li>
+<li>Shuts down the database.</li>
+</ol>
+<p>Make sure that the <codeph>javac</codeph> command is in your path, then
+compile the programs as follows:
+</p>
+<p><userinput>javac AuthExampleClientSQLAuth1.java</userinput></p>
+<p><userinput>javac AuthExampleClientSQLAuth2.java</userinput></p>
+<p>Before you run the programs, start the
+<ph conref="../conrefs.dita#prod/productshortname"></ph> Network
+Server as described in "Activity 4: Create and run a JDBC program using the
+client driver and Network Server" in
+<ph conref="../conrefs.dita#pub/citgetstart"></ph>. When you run the
+programs, make sure that
+<codeph>%DERBY_HOME%\lib\derbyclient.jar</codeph> (or
+<codeph>$DERBY_HOME/lib/derbyclient.jar</codeph>) is in your classpath. Run the
+programs using commands like the following:</p>
+<p><userinput>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClientSQLAuth1</userinput></p>
+<p><userinput>java -cp ${CLASSPATH}:${DERBY_HOME}/lib/derbyclient.jar AuthExampleClientSQLAuth2</userinput></p>
+</section>
+<example><title>Source code for <codeph>AuthExampleClientSQLAuth1.java</codeph></title>
+<codeblock>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();
+        }
+    }
+}</codeblock></example>
+<example><title>Source code for <codeph>AuthExampleClientSQLAuth2.java</codeph></title>
+<codeblock>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();
+        }
+    }
+}</codeblock></example>
+</refbody>
+</reference>

Propchange: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message