db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r595345 - in /db/derby/docs/trunk/src/devguide: derbydev.ditamap rdevcsecure26537.dita rdevcsecureclientexample.dita
Date Thu, 15 Nov 2007 16:11:04 GMT
Author: kahatlen
Date: Thu Nov 15 08:11:03 2007
New Revision: 595345

URL: http://svn.apache.org/viewvc?rev=595345&view=rev
Log:
DERBY-1823: Derby Developer's Guide - Issues w/ User authentication
and authorization extended examples section/paragraph

Fix contributed by Kim Haase.

Added:
    db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita   (with props)
Modified:
    db/derby/docs/trunk/src/devguide/derbydev.ditamap
    db/derby/docs/trunk/src/devguide/rdevcsecure26537.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=595345&r1=595344&r2=595345&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/derbydev.ditamap (original)
+++ db/derby/docs/trunk/src/devguide/derbydev.ditamap Thu Nov 15 08:11:03 2007
@@ -2295,9 +2295,11 @@
 </topicref>
 <topicref href="cdevcsecure51876.dita" navtitle="User authentication and authorization
examples">
 <topicref href="rdevcsecure125.dita" navtitle="User authentication example in a client/server
environment">
+<topicref href="rdevcsecureclientexample.dita" navtitle="User authentication and authorization
client example">
+</topicref>
 </topicref>
 <topicref href="rdevcsecure13713.dita" navtitle="User authentication example in a single-user,
embedded environment">
-<topicref href="rdevcsecure26537.dita" navtitle="User authentication and authorization
extended examples">
+<topicref href="rdevcsecure26537.dita" navtitle="User authentication and authorization
embedded example">
 </topicref>
 </topicref>
 </topicref>

Modified: db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita?rev=595345&r1=595344&r2=595345&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita (original)
+++ db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita Thu Nov 15 08:11:03 2007
@@ -19,127 +19,359 @@
 limitations under the License.
 -->
 <reference id="rdevcsecure26537" xml:lang="en-us">
-<title>User authentication and authorization extended examples</title>
-<shortdesc>The following two examples from the <i>sample</i> database show
-how to turn on and turn off user authentication using <ph conref="../conrefs.dita#prod/productshortname"></ph>'s
+<title>User authentication and authorization embedded example</title>
+<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>
 <prolog></prolog>
 <refbody>
-<example> <codeblock>/** 
-	  * Turn on built-in user authentication and user authorization. 
-	  * 
-	  * @param conn a connection to the database.
-	  */
-
-	public static void turnOnBuiltInUsers(Connection conn) throws SQLException { 
-		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')"); 
-		rs.next(); 
-		System.out.println(rs.getString(1)); 
-		// Setting authentication scheme to <ph conref="../conrefs.dita#prod/productshortname"></ph>

-		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(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(rs.getString(1)); 
-
-		// Confirming read-only users 
-		rs = s.executeQuery(
-			"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + 
-			"'derby.database.readOnlyAccessUsers')"); 
-		rs.next(); 
-		System.out.println(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(); 
-	} </codeblock></example>
-<example> <codeblock>/** 
-	  * 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."); 
-
-		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')"); 
-		rs.next(); 
-		System.out.println(rs.getString(1)); 
-
-		// Confirming default connection mode 
-		rs = s.executeQuery(
-			"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" + 
-			"'derby.database.defaultConnectionMode')"); 
-		rs.next(); 
-		System.out.println(rs.getString(1)); 
-		System.out.println("Turned off all the user-related properties."); 
-		s.close(); 
-	}
+<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>
+<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>
+<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
+authentication changes can take effect.</li>
+<li>Tries to connect to the database without a username and password, 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 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
+<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
+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>
+</section>
+<example><title>Source code for <codeph>AuthExampleEmbedded.java</codeph></title>
+<codeblock>
+import java.sql.*;
+
+public class AuthExampleEmbedded {
+
+    public static void main(String[] args) {
+
+        String driver = "org.apache.derby.jdbc.EmbeddedDriver";
+        String dbName="jdbcDemoDB";
+        String connectionURL = "jdbc:derby:" + dbName + ";create=true";
+        Connection conn = null;
+
+        // Load the driver
+        try {
+            Class.forName(driver);
+            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");
+        }
+
+        // Start the database and set up users, then close database
+        try {
+            System.out.println("Trying to connect to " + connectionURL);
+            conn = DriverManager.getConnection(connectionURL);
+            System.out.println("Connected to database " + connectionURL);
+
+            turnOnBuiltInUsers(conn);
+
+            // shut down the database
+            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;
+            try {
+                DriverManager.getConnection("jdbc:derby:;shutdown=true");
+            } catch (SQLException se) {
+                if ( se.getSQLState().equals("XJ015") ) {
+                    gotSQLExc = true;
+                }
+            }
+            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);
+        }
+
+        // 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
+            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);
+        }
+
+        // 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 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);
+        }
+
+        // Log in as a user with full access
+        // Create, update, and query table
+        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 T1(C1 VARCHAR(6))");
+            System.out.println("Created table T1");
+            s.executeUpdate("INSERT INTO T1 VALUES('hello')");
+
+            ResultSet rs = s.executeQuery("SELECT * FROM T1");
+            rs.next();
+            System.out.println("Value of T1/C1 is " + rs.getString(1));
+
+            s.close();
+        } catch (Throwable e) {
+            errorPrint(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");
+
+            // Shut down the database
+            /* In embedded mode, an application should shut down Derby.
+               Shutdown throws the XJ015 exception to confirm success. */
+            boolean gotSQLExc = false;
+            try {
+                DriverManager.getConnection("jdbc:derby:;shutdown=true");
+            } catch (SQLException se) {
+                if ( se.getSQLState().equals("XJ015") ) {
+                    gotSQLExc = true;
+                }
+            }
+            if (!gotSQLExc) {
+                 System.out.println("Database did not shut down normally");
+            } else {
+                 System.out.println("Database shut down normally");
+            }
+        } catch (Throwable e) {
+            errorPrint(e);
+        }
+    }
+
+    /**
+     * Turn on built-in user authentication and user authorization.
+     *
+     * @param conn a connection to the database.
+     */
+    public static void turnOnBuiltInUsers(Connection conn) throws SQLException {
+        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')");
+        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));
+
+        // Confirming read-only users
+        rs = s.executeQuery(
+            "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" +
+            "'derby.database.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.
+        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.");
+
+        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')");
+        rs.next();
+        System.out.println("Value of requireAuthentication is " +
+            rs.getString(1));
+
+        // 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();
+    }
+
+    /** Exception reporting methods
+     *   with special handling of SQLExceptions
+     */
+    static void errorPrint(Throwable e) {
+        if (e instanceof SQLException)
+            SQLExceptionPrint((SQLException)e);
+        else {
+            System.out.println("A non-SQL error occurred.");
+            e.printStackTrace();
+        }
+    }  // END errorPrint
+
+    //  Iterates 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>
 </refbody>

Added: db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita?rev=595345&view=auto
==============================================================================
--- db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita (added)
+++ db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita Thu Nov 15 08:11:03 2007
@@ -0,0 +1,392 @@
+<?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="rdevcsecureclientexample" xml:lang="en-us">
+<title>User authentication and authorization client example</title>
+<shortdesc>The following pair of programs,
+<codeph>AuthExampleClient1.java</codeph> and
+<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>
+<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>
+<p>The first program, <codeph>AuthExampleClient1.java</codeph>, does the
+following:</p>
+<ol>
+<li>Creates a database named <codeph>jdbcDemoDB</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>
+</ol>
+<p>The second program, <codeph>AuthExampleClient2.java</codeph>, does the
+following:</p>
+<ol>
+<li>Tries to connect to the database without a username and password, 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 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>
+</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
+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>
+</section>
+<example><title>Source code for <codeph>AuthExampleClient1.java</codeph></title>
+<codeblock>
+import java.sql.*;
+
+public class AuthExampleClient1 {
+
+    public static void main(String[] args) {
+
+        String driver = "org.apache.derby.jdbc.ClientDriver";
+        String dbName="jdbcDemoDB";
+        String connectionURL = "jdbc:derby://localhost:1527/" + dbName +
+            ";create=true";
+        Connection conn = null;
+
+        // Load the driver
+        try {
+            Class.forName(driver);
+            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");
+        }
+
+        // Start the database and set up users, then close connection
+        try {
+            System.out.println("Trying to connect to " + connectionURL);
+            conn = DriverManager.getConnection(connectionURL);
+            System.out.println("Connected to database " + connectionURL);
+
+            turnOnBuiltInUsers(conn);
+
+            // shut down the database
+            conn.close();
+            System.out.println("Closed connection");
+
+        } catch (Throwable e) {
+            errorPrint(e);
+            System.exit(1);
+        }
+    }
+
+    /**
+     * Turn on built-in user authentication and user authorization.
+     *
+     * @param conn a connection to the database.
+     */
+    public static void turnOnBuiltInUsers(Connection conn) throws SQLException {
+        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')");
+        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));
+
+        // Confirming read-only users
+        rs = s.executeQuery(
+            "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY(" +
+            "'derby.database.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.
+        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 errorPrint(Throwable e) {
+        if (e instanceof SQLException)
+            SQLExceptionPrint((SQLException)e);
+        else {
+            System.out.println("A non-SQL error occurred.");
+            e.printStackTrace();
+        }
+    }  // END errorPrint
+
+    //  Iterates 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>
+<example><title>Source code for <codeph>AuthExampleClient2.java</codeph></title>
+<codeblock>
+import java.sql.*;
+
+public class AuthExampleClient2 {
+
+    public static void main(String[] args) {
+
+        String driver = "org.apache.derby.jdbc.ClientDriver";
+        String dbName="jdbcDemoDB";
+        String connectionURL = "jdbc:derby://localhost:1527/" + dbName;
+        Connection conn = null;
+
+        // Restart database and confirm that unauthorized users cannot
+        //  access it
+
+        // Load the driver
+        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
+            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);
+        }
+
+        // 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 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);
+        }
+
+        // Log in as a user with full access
+        // Create, update, and query table
+        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 T1(C1 VARCHAR(6))");
+            System.out.println("Created table T1");
+            s.executeUpdate("INSERT INTO T1 VALUES('hello')");
+
+            ResultSet rs = s.executeQuery("SELECT * FROM T1");
+            rs.next();
+            System.out.println("Value of T1/C1 is " + rs.getString(1));
+
+            s.close();
+        } catch (Throwable e) {
+            errorPrint(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);
+        }
+    }
+
+    /**
+     * 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.");
+
+        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')");
+        rs.next();
+        System.out.println("Value of requireAuthentication is " +
+            rs.getString(1));
+
+        // 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();
+    }
+
+    /** Exception reporting methods
+     *   with special handling of SQLExceptions
+     */
+    static void errorPrint(Throwable e) {
+        if (e instanceof SQLException)
+            SQLExceptionPrint((SQLException)e);
+        else {
+            System.out.println("A non-SQL error occurred.");
+            e.printStackTrace();
+        }
+    }  // END errorPrint
+
+    //  Iterates 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>
+</refbody>
+</reference>

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



Mime
View raw message