You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ch...@apache.org on 2008/08/29 16:22:17 UTC
svn commit: r690270 [2/2] - in /db/derby/docs/trunk/src/devguide:
derbydev.ditamap rdevcsecure26537.dita rdevcsecureclientexample.dita
rdevcsecuresqlauthclientex.dita rdevcsecuresqlauthembeddedex.dita
rdevcsecuresqlauthexs.dita
Added: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita?rev=690270&view=auto
==============================================================================
--- db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita (added)
+++ db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita Fri Aug 29 07:22:16 2008
@@ -0,0 +1,498 @@
+<?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="rdevcsecuresqlauthembeddedex" xml:lang="en-us">
+<title>User authentication and SQL authorization embedded example</title>
+<shortdesc>The following program,
+<codeph>AuthExampleEmbeddedSQLAuth.java</codeph>, shows 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 embedded
+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="rdevcsecure26537.dita#rdevcsecure26537"></xref>, which does not use
+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>sqlAuthEmbDB</codeph>, using the embedded 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 stops and restarts the database so that the
+authentication and SQL authorization changes can take effect.</li>
+<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>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 AuthExampleEmbeddedSQLAuth.java</userinput></p>
+<p>When you run <codeph>AuthExampleEmbeddedSQLAuth</codeph>, make
+sure that <codeph>%DERBY_HOME%\lib\derby.jar</codeph> (or
+<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 AuthExampleEmbeddedSQLAuth</userinput></p>
+</section>
+<example><title>Source code for <codeph>AuthExampleEmbeddedSQLAuth.java</codeph></title>
+<codeblock>import java.sql.*;
+
+public class AuthExampleEmbeddedSQLAuth {
+
+ public static void main(String[] args) {
+
+ String driver = "org.apache.derby.jdbc.EmbeddedDriver";
+ String dbName="sqlAuthEmbDB";
+ String dbOwner="mary";
+ String connectionURL = "jdbc:derby:" + dbName +
+ ";user=" + dbOwner + ";create=true";
+ Connection conn = null;
+
+ // Load the driver. This code is not needed if you are using
+ // JDK 6, because in that environment the driver is loaded
+ // automatically when the application requests a connection.
+ try {
+ Class.forName(driver);
+ System.out.println(driver + " loaded.");
+ } catch (java.lang.ClassNotFoundException ce) {
+ System.err.print("ClassNotFoundException: ");
+ System.err.println(ce.getMessage());
+ System.out.println("\n Make sure your CLASSPATH variable " +
+ "contains %DERBY_HOME%\\lib\\derby.jar " +
+ "(${DERBY_HOME}/lib/derby.jar).\n");
+ } 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:" + dbName +
+ ";user=mary;shutdown=true");
+ } catch (SQLException se) {
+ if ( !se.getSQLState().equals("08006") ) {
+ throw se;
+ }
+ }
+ 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;
+
+ // 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. 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 {
+
+ String dbName="sqlAuthEmbDB";
+ String dbOwner="mary";
+ String connectionURL = "jdbc:derby:" + 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");
+
+ try {
+ DriverManager.getConnection("jdbc:derby:;shutdown=true");
+ } catch (SQLException se) {
+ if ( !se.getSQLState().equals("XJ015") ) {
+ throw se;
+ }
+ }
+
+ System.out.println("Derby system shut down normally");
+ } catch (SQLException 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(setProperty + 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>
+</refbody>
+</reference>
Propchange: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita?rev=690270&view=auto
==============================================================================
--- db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita (added)
+++ db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita Fri Aug 29 07:22:16 2008
@@ -0,0 +1,36 @@
+<?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="rdevcsecuresqlauthexs" xml:lang="en-us">
+<title>User authentication examples using SQL authorization</title>
+<shortdesc>
+The following examples show how to use SQL authorization in
+conjunction with <ph conref="../conrefs.dita#prod/productshortname"></ph>'s
+built-in user authentication and user authorization.
+</shortdesc>
+<prolog></prolog>
+<refbody>
+<section>
+<p>These examples are based on the examples in
+<xref href="rdevcsecureclientexample.dita#rdevcsecureclientexample"></xref> and
+<xref href="rdevcsecure26537.dita#rdevcsecure26537"></xref>.</p>
+</section>
+</refbody>
+</reference>
Propchange: db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita
------------------------------------------------------------------------------
svn:eol-style = native