Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 0D86B9188 for ; Fri, 23 Mar 2012 19:20:05 +0000 (UTC) Received: (qmail 92265 invoked by uid 500); 23 Mar 2012 19:20:04 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 92249 invoked by uid 500); 23 Mar 2012 19:20:04 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 92241 invoked by uid 99); 23 Mar 2012 19:20:04 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 23 Mar 2012 19:20:04 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,T_FILL_THIS_FORM_SHORT X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 23 Mar 2012 19:19:51 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 67AC82388860; Fri, 23 Mar 2012 19:19:28 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1304566 [1/2] - /db/derby/docs/trunk/src/devguide/ Date: Fri, 23 Mar 2012 19:19:27 -0000 To: derby-commits@db.apache.org From: chaase3@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120323191928.67AC82388860@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: chaase3 Date: Fri Mar 23 19:19:26 2012 New Revision: 1304566 URL: http://svn.apache.org/viewvc?rev=1304566&view=rev Log: DERBY-5522 Document the NATIVE authentication scheme. In Developer's Guide, added two new topics, removed 6 obsolete ones, and modified 18. Patch: DERBY-5522-devguide-3.diff Added: db/derby/docs/trunk/src/devguide/cdevcsecurenativeauth.dita (with props) db/derby/docs/trunk/src/devguide/rdevcsecurenativeauthex.dita (with props) Removed: db/derby/docs/trunk/src/devguide/rdevcsecure13713.dita db/derby/docs/trunk/src/devguide/rdevcsecure26537.dita db/derby/docs/trunk/src/devguide/rdevcsecureclientexample.dita db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthclientex.dita db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthembeddedex.dita db/derby/docs/trunk/src/devguide/rdevcsecuresqlauthexs.dita Modified: db/derby/docs/trunk/src/devguide/cdevcsecure21547.dita db/derby/docs/trunk/src/devguide/cdevcsecure24458.dita db/derby/docs/trunk/src/devguide/cdevcsecure36127.dita db/derby/docs/trunk/src/devguide/cdevcsecure36595.dita db/derby/docs/trunk/src/devguide/cdevcsecure37817.dita db/derby/docs/trunk/src/devguide/cdevcsecure42374.dita db/derby/docs/trunk/src/devguide/cdevcsecure51876.dita db/derby/docs/trunk/src/devguide/cdevcsecure865818.dita db/derby/docs/trunk/src/devguide/cdevcsecure865880.dita db/derby/docs/trunk/src/devguide/cdevcsecure866060.dita db/derby/docs/trunk/src/devguide/cdevcsecureDbOwner.dita db/derby/docs/trunk/src/devguide/cdevsetprop824451.dita db/derby/docs/trunk/src/devguide/derbydev.ditamap db/derby/docs/trunk/src/devguide/rdevcsecure125.dita db/derby/docs/trunk/src/devguide/rdevcsecure557.dita db/derby/docs/trunk/src/devguide/rdevcsecure766.dita db/derby/docs/trunk/src/devguide/tdevcsecure81850.dita db/derby/docs/trunk/src/devguide/tdevcsecure82556.dita Modified: db/derby/docs/trunk/src/devguide/cdevcsecure21547.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure21547.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure21547.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure21547.dita Fri Mar 23 19:19:26 2012 @@ -19,28 +19,31 @@ See the License for the specific languag limitations under the License. --> -Built-in Derby users +BUILTIN Derby users provides -a simple, built-in repository of user names and passwords. +a simple repository of user names and passwords using the BUILTIN +authentication mechanism. -usersbuilt-in repository -passwordsbuilt-in repository +usersBUILTIN repository +passwordsBUILTIN repository 's -built-in authentication mechanism is suitable only for development and testing -purposes. It is strongly recommended that production systems rely on LDAP or a -user-defined class for authentication. It is also strongly recommended that -production systems protect network connections with SSL/TLS. -

To use the built-in repository, set derby.authentication.provider to BUILTIN. -Using built-in users is an alternative to using an external directory service -such as LDAP.

+BUILTIN authentication mechanism is suitable only for development and testing +purposes, and it will no longer be documented in future releases. It is strongly +recommended that production systems rely on NATIVE authentication, an +external directory service such as LDAP, or a user-defined class for +authentication. It is also strongly recommended that production systems protect +network connections with SSL/TLS. +

To use the BUILTIN repository, set +derby.authentication.provider to BUILTIN.

derby.authentication.provider=BUILTIN

You can create user names and passwords for users -by specifying them with the derby.user.UserName property.

+by specifying them with the derby.user.UserName property.

These user names are case-sensitive for user authentication. User names -are SQL92Identifiers. Delimited identifiers are allowed: derby.user."FRed"=java +are SQL92Identifiers. Delimited identifiers are allowed: +derby.user."FRed"=java For passwords, it is a good idea not to use words that would be easily guessed, such as a login name or simple words or numbers. A password should be a mix of numbers and upper- and lowercase letters. Modified: db/derby/docs/trunk/src/devguide/cdevcsecure24458.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure24458.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure24458.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure24458.dita Fri Mar 23 19:19:26 2012 @@ -39,10 +39,12 @@ type his name that way. Con
  • Within the user authorization system, Fred becomes a case-insensitive authorization identifier. Fred is known as FRED.
    • -
  • When specifying which users are authorized to access the accounting database, -you must list Fred's authorization identifier, FRED (which you can -type as FRED, FREd, or fred, since the system automatically -converts it to all-uppercase). derby.fullAccessUsers=sa,FRED,mary
    • +
  • When you use the SYSCS_UTIL.SYSCS_CREATE_USER system +procedure to create the NATIVE authentication user Fred, you specify his name +in uppercase to match the way it is stored in the database: +CALL SYSCS_UTIL.SYSCS_CREATE_USER('FRED', 'flintstone'); + +

    • Let's take a second example, where Fred has a slightly different name within the user authentication system.

    @@ -56,9 +58,12 @@ the double quotes when passing the name
  • Within the user authorization system, Fred becomes a case-sensitive authorization identifier. Fred is known as Fred!.
    • -
  • When specifying which users are authorized to access the accounting database, -you must list Fred's authorization identifier, "Fred!" (which you must -always delimit with double quotation marks). derby.fullAccessUsers=sa,"Fred!",manager
    • +
  • When you use the SYSCS_UTIL.SYSCS_CREATE_USER system +procedure to create the NATIVE authentication user Fred!, you specify his name +exactly as it is stored in the database: +CALL SYSCS_UTIL.SYSCS_CREATE_USER('Fred!', 'flintstone'); + +

    • As shown in the first example, your external authentication system may be case-sensitive, whereas the authorization identifier within may Modified: db/derby/docs/trunk/src/devguide/cdevcsecure36127.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure36127.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure36127.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure36127.dita Fri Mar 23 19:19:26 2012 @@ -20,18 +20,24 @@ limitations under the License. --> Enabling user authentication -To enable user authentication, set the derby.connection.requireAuthentication property -to true. Otherwise, does -not require a user name and password. You can set this property as a system-wide -property or as a database-wide property. + +If you use NATIVE authentication, you do not need to set the +derby.connection.requireAuthentication property. When you +create a database with NATIVE authentication, simply specify a username and +password, and that user becomes the database owner. User authenticationenabling -

    For a multi-user product, you would typically set it for the system in -the derby.properties file for your server, since it is in a trusted -environment.

    +

    If you do not use NATIVE authentication, you must set the +derby.connection.requireAuthentication property to true to +enable user authentication; if you do not set this property, + does +not require a user name and password. You can set this property as a system-wide +property or as a database-wide property. For a multi-user product, you would +typically set it for the system in the derby.properties file +for your server, since it is in a trusted environment.

    If you start a system with user authentication enabled but without defining at least one user, you will not be able to shut down the system gracefully. When is Modified: db/derby/docs/trunk/src/devguide/cdevcsecure36595.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure36595.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure36595.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure36595.dita Fri Mar 23 19:19:26 2012 @@ -34,29 +34,32 @@ propertydescription -

    There are two types of user authorization in , -connection authorization and SQL authorization. Connection authorization specifies -the access that users have to connect to a system or database. SQL authorization controls -the permissions that users have on database objects or for SQL actions. You -can set the user authorization properties in as -system-level properties or database-level properties.

    +

    There are two types of user authorization in +, connection +authorization and SQL authorization:

    +
      +
    • Connection authorization specifies the coarse-grained access +that users have to connect to a system or database.
      • +
    • SQL authorization controls the fine-grained permissions that +users have on database objects or for SQL actions.
      • +
    +

    You can set the user authorization properties in + as system-level +properties or database-level properties.

    Set system-level user authorizations when you are developing applications, or when you want to specify a secure default authorization for all users to connect to all of the databases in the system.

    +If you use NATIVE authentication, fine-grained SQL +authorization is automatically enabled, and by default, all users enjoy full +coarse-grained access to the database. In this situation, fine-grained SQL +authorization cannot be turned off. However, you can still adjust coarse-grained +access to the database. +
    User authorization properties

    There are several properties that you can set to control database-level -user authorizations. Some of the properties are general properties that set -the access mode for all users. Other properties are user specific properties -that set the type of access for specific user IDs.

    -

    The properties that affect authorization are:

      -
    • The derby.database.defaultConnectionMode property controls -the default access mode. Use the derby.database.defaultConnectionMode property -to specify the default connection access that users have when they connect -to the database. If you do not explicitly set the derby.database.defaultConnectionMode property, -the default user authorization for a database is fullAccess, -which is read-write access.
      • -
    • The derby.database.fullAccessUsers and derby.database.readOnlyAccessUsers properties -are user specific properties. Use these properties to specify the user IDs -that have read-write access and read-only access to a database.
      • +user authorizations. Some of the properties are general properties that set the +access mode for all users. Other properties are user specific properties that +set the type of access for specific user IDs.

      +

      The following properties affect authorization:

      • The derby.database.sqlAuthorization property enables SQL standard authorization. Use the derby.database.sqlAuthorization property to specify if object owners can grant and revoke permission for users to perform @@ -64,41 +67,59 @@ SQL actions on database objects. The def is FALSE. When the derby.database.sqlAuthorization property is set to TRUE, object owners can use the GRANT and REVOKE SQL statements to set the user permissions for specific database objects or -for specific SQL actions.
        • +for specific SQL actions. +
      • The derby.database.defaultConnectionMode property controls +the default coarse-grained access mode. This property specifies the +default connection access that users have when they connect to the database. If +you do not explicitly set the +derby.database.defaultConnectionMode property, the default +coarse-grained connection authorization for a database is +fullAccess, which is read-write access.
        • +
      • The derby.database.fullAccessUsers and +derby.database.readOnlyAccessUsers properties +are additional coarse-grained access properties. These properties can be used to +specify the user IDs that have read-write access and read-only access to a +database.

      -

      If you do not specify the user authorizations for a specific user ID, the -user ID inherits whatever authorization is set as the default user authorization -for the database.

      +

      If you do not specify the coarse-grained user authorizations for a specific +user ID, that user ID inherits the database's default coarse-grained connection +authorization.

      If you set the derby.database.defaultConnectionMode property to noAccess or readOnlyAccess, you should allow at least one user read-write access. Otherwise, depending on the default connection authorization you specify, you will configure the database so that it cannot be accessed or changed. -
      How user authorization properties work together

      The derby.database.defaultConnectionMode property +

      +
      How user authorization properties work together +

      The derby.database.defaultConnectionMode property and the derby.database.sqlAuthorization property work together. The default settings for these properties allow anyone to access and drop the database objects that you create. You can change the default access mode by specifying different settings for these properties.

        -
      • When the derby.database.sqlAuthorization property is FALSE, +
      • When the derby.database.sqlAuthorization property is +FALSE, the ability to read from or write to database objects is determined by the setting for the derby.database.defaultConnectionMode property. If the derby.database.defaultConnectionMode property is set to readOnlyAccess, users can access all of the database objects but they cannot update or drop the objects.
        • -
      • When the derby.database.sqlAuthorization property is TRUE, +
      • When the derby.database.sqlAuthorization property is +TRUE, the ability to read from or write to database objects is further restricted to the owner of the database objects. The owner must grant permission for others to access the database objects. No one but the owner of an object or the database owner can drop the object.
        • -
      • The access mode specified for the derby.database.defaultConnectionMode property -overrides the permissions that are granted by the owner of a database object. +
      • The coarse-grained access mode specified for the +derby.database.defaultConnectionMode property +supplements the permissions that are granted by the owner of a database object. For example, if a user is granted INSERT privileges on a table but the user only has read-only connection authorization, the user cannot insert data into the table.

      -
      Changes to connection authorization settings

      Connection +

      Changes to connection authorization settings +

      Connection authorization properties are fixed for the duration of a connection. If you change the connection authorization properties during a connection, those changes are not in affect until you establish a new connection.

      Modified: db/derby/docs/trunk/src/devguide/cdevcsecure37817.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure37817.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure37817.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure37817.dita Fri Mar 23 19:19:26 2012 @@ -22,9 +22,10 @@ limitations under the License. Defining users provides several ways to define the repository of users and passwords. To specify which -of these services to use with your system, -set the property derby.authentication.provider to the appropriate value -as discussed here. +of these services to use with your + system, set the +property derby.authentication.provider to an appropriate +value. Usersdefining for a system Authentication providerspecifying @@ -35,9 +36,10 @@ as discussed here. Setting the property as a database-wide property creates users for a single database only.

        -
      • : . -This includes Windows NT domain user authentication through the Netscape NT -Synchronization Service.
        • +
        • +
      • : +. +
      Modified: db/derby/docs/trunk/src/devguide/cdevcsecure42374.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure42374.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure42374.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure42374.dita Fri Mar 23 19:19:26 2012 @@ -22,11 +22,11 @@ limitations under the License. Working with user authentication provides support for user authentication and user authorization. User -authentication means that - authenticates the name -and password for a user before allowing that user access to the system. -User authorization allows access to a particular -database. You are strongly urged to implement both authentication and +authentication determines whether a user is a valid user. It establishes +the user's identity. +User authorization determines what operations a user's established +identity can perform. +You are strongly urged to implement both authentication and authorization on any multi-user database used in production. user authenticationoverview @@ -34,29 +34,37 @@ authorization on any multi-user database -

      When user authentication is enabled (which it is not by default), the user -requesting a connection must provide a valid name and password, which verifies against the -repository of users defined for the system. After authenticates -the user, it grants the user access to the system -but not necessarily access to the database made in the connection request. -In the system, access -to a database is determined by user -authorization.

      +

      When user authentication is enabled (by default, it is not enabled), +the user that requests a connection must provide a valid name and password, +which verifies against +the repository of users defined for the system. After + authenticates the user +as valid, user +authorization determines what operations the user can perform on the +database to which the user is requesting a connection.

      For user authentication, allows -you to provide a repository of users in a number of different ways. For example, -you can hook up to -an external directory service elsewhere in your enterprise, create your own -directory service, or use 's -simple mechanism for creating a built-in repository of users.

      +you to provide a repository of users in a number of different ways:

      +
        +
      • You can use 's +NATIVE authentication mechanism to store user credentials in a database. See + for +details.
        • +
      • You can hook up to +an external directory service elsewhere in your enterprise.
        • +
      • You can create your own directory service.
        • +
      • You can use 's +simple BUILTIN mechanism for creating a repository of users. +
      's -built-in authentication mechanism is suitable only for development and testing -purposes. It is strongly recommended that production systems rely on an -external directory service such as LDAP or a user-defined class for +BUILTIN authentication mechanism is suitable only for development and testing +purposes, and it will no longer be documented in future releases. It is strongly +recommended that production systems rely on NATIVE authentication, an +external directory service such as LDAP, or a user-defined class for authentication. It is also strongly recommended that production systems protect network connections with SSL/TLS.

      You can define a repository of users for a particular database or for an -entire system, depending on whether you use system-wide or database-wide properties.

      +entire system, depending on whether you use system-wide or database-wide +properties.

      When user authentication is enabled and uses an external directory service, the architecture looks something like that Modified: db/derby/docs/trunk/src/devguide/cdevcsecure51876.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure51876.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure51876.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure51876.dita Fri Mar 23 19:19:26 2012 @@ -20,7 +20,7 @@ limitations under the License. --> User authentication and authorization examples -This section provides examples on using user authentication and +This section provides examples that show user authentication and authorization in in either a client/server environment or in an embedded environment. Modified: db/derby/docs/trunk/src/devguide/cdevcsecure865818.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure865818.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure865818.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure865818.dita Fri Mar 23 19:19:26 2012 @@ -20,15 +20,17 @@ limitations under the License. --> Setting the default connection access mode -Use the derby.database.defaultConnectionMode property -to specify the default type of access that users have when they connect to -the database. +You can use the derby.database.defaultConnectionMode +property to specify the default type of access that users have when they connect +to the database. databasescontrolling access to derby.database.ConnectionMode propertypropertiesdefaultConnectionMode +

      If you use SQL authorization (the default with NATIVE authentication), you +typically do not use this property.

      The valid settings for the derby.database.defaultConnectionMode property are:

      • noAccess
        • Modified: db/derby/docs/trunk/src/devguide/cdevcsecure865880.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure865880.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure865880.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure865880.dita Fri Mar 23 19:19:26 2012 @@ -20,9 +20,10 @@ limitations under the License. --> Setting access for individual users -Use the derby.database.fullAccessUsers and derby.database.readOnlyAccessUsers properties -to specify the user IDs that have read-write access and read-only access to -a database. +You can use the derby.database.fullAccessUsers and +derby.database.readOnlyAccessUsers properties to specify the +user IDs that have read-write access and read-only access to a +database. databasesaccess for individual users, setting derby.database.fullAccessUsers propertypropertiesderby.database.fullAccessUsers @@ -30,6 +31,8 @@ a database. +

        If you use SQL authorization (the default with NATIVE authentication), you +typically do not use these properties.

        You can specify multiple user IDs by using a comma-separated list, with no spaces between the comma and the next user ID.

        To set the user authorizations for individual users, specify the access Modified: db/derby/docs/trunk/src/devguide/cdevcsecure866060.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecure866060.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecure866060.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecure866060.dita Fri Mar 23 19:19:26 2012 @@ -19,34 +19,37 @@ limitations under the License. --> Setting the SQL standard authorization mode -Use the derby.database.sqlAuthorization property -to enable SQL standard authorization. +If you use NATIVE authentication, SQL standard authorization is +automatically enabled. Otherwise, use the +derby.database.sqlAuthorization property to enable SQL standard +authorization. databasesSQL standard authorization, setting derby.database.sqlAuthorization propertypropertiesderby.database.sqlAuthorization -

        The derby.database.sqlAuthorization property controls -the ability for object owners to grant and revoke permission for users to -perform actions on database objects. It also controls the ability for users -to create, set, and drop roles.

        +

        If SQL standard authorization mode is enabled, object owners can grant and +revoke permission for other users to perform actions on database objects. SQL +standard authorization mode also controls users' ability to create, set, and +drop roles.

        The valid settings for the derby.database.sqlAuthorization property are:

        • TRUE
        • FALSE
          • -

        The default setting for the derby.database.sqlAuthorization property -is FALSE.

        +

      The default setting for the +derby.database.sqlAuthorization property is +FALSE, unless NATIVE authentication is enabled.

      The derby.database.sqlAuthorization property is usable only if the property derby.connection.requireAuthentication is also set to true, since SQL authorization is of no value unless authentication is -also enabled.

      +also enabled. (With NATIVE authentication, both are enabled automatically.)

      After you set the derby.database.sqlAuthorization property to TRUE, you cannot set the property back to FALSE.

      You can set the derby.database.sqlAuthorization property as a system property or as a database property. If you set this property as a system property before you create the databases, all new databases will -automatically have SQL authorization enabled. If the databases already exists, +automatically have SQL authorization enabled. If the databases already exist, you can set this property only as a database property.

      To enable SQL standard authorization for the entire system, set the derby.database.sqlAuthorization property as a system property:

      derby.database.sqlAuthorization=true Modified: db/derby/docs/trunk/src/devguide/cdevcsecureDbOwner.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecureDbOwner.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecureDbOwner.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevcsecureDbOwner.dita Fri Mar 23 19:19:26 2012 @@ -25,7 +25,8 @@ limitations under the License. The term database owner refers to the current authorization identifier when the database is created, that is, the user creating - the database. If you enable or plan to enable SQL authorization, + the database. If you use NATIVE authentication, or if you manually enable + or plan to enable SQL authorization, controlling the identity of the database owner becomes important. Added: db/derby/docs/trunk/src/devguide/cdevcsecurenativeauth.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevcsecurenativeauth.dita?rev=1304566&view=auto ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevcsecurenativeauth.dita (added) +++ db/derby/docs/trunk/src/devguide/cdevcsecurenativeauth.dita Fri Mar 23 19:19:26 2012 @@ -0,0 +1,171 @@ + + + + + +Using NATIVE authentication +'s simplest +authentication mechanism is NATIVE authentication. + +user authenticationNATIVE authentication +NATIVE authenticationoverview + + + +

      When you use NATIVE authentication, user names and encrypted passwords are +stored in a database. You can specify a dedicated credentials database +for this purpose, or you can store the credentials in the same database you use +for your application data. The credentials are stored in the SYSUSERS system +table of the database.

      +

      To specify NATIVE authentication, specify one of the following values for the +derby.authentication.provider property:

      +
        +
      • NATIVE:credentialsDB +

        This value tells to +use credentialsDB, a dedicated database, to store user credentials. This +value must be set by using system-wide Java Virtual Machine (JVM) properties or +by using the derby.properties file; it cannot be set in the +database by using the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY +procedure. When this system-wide value is set, credentialsDB is used to +authenticate all operations. Individual databases can override this directive by +specifying their own value for +derby.authentication.provider.

        +

        The value of credentialsDB must be a valid name for a database.

        +
        • +
      • NATIVE:credentialsDB:LOCAL +

        This value tells to +use credentialsDB for system-wide operations, but to use an individual +database's SYSUSERS system table to authenticate connections to that database. +This value must be set by using system-wide JVM properties or by using the +derby.properties file; it cannot be set in the database by +using the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system +procedure.

        +
        • +
      • NATIVE::LOCAL +

        This value tells to +use an individual database's SYSUSERS system table to authenticate connections +to it. This value is never explicitly set by an application. It is only set by + itself when it marks a +database as a credentials database.

        +
        • +
      +
      Working with a credentials database +

      With NATIVE authentication, a database can become a credentials database +in any of the following ways:

      +
        +
      • When the database is being created, it is identified as the credentials +database by the system-level property setting +derby.authentication.provider=NATIVE:credentialsDB.
        • +
      • When the database is being created, LOCAL authentication of connections is +specified by the system-level property setting +derby.authentication.provider=NATIVE:credentialsDB:LOCAL.
        • +
      • When the database already exists, the +database owner +calls the SYSCS_UTIL.SYSCS_CREATE_USER system procedure to +store the database owner's credentials in the database. If the database owner +calls this procedure to store another user's credentials first, an error is +raised.
        • +
      +

      When a database becomes a credentials database, the following things +happen:

      +
        +
      • The value of derby.authentication.provider=NATIVE::LOCAL +is stored in the database, marking it as a credentials database.
        • +
      • From this point forward, the value of +derby.authentication.provider cannot be overridden or changed +for connections to this database.
        • +
      • If the database is being newly created, the database owner's credentials +(provided in the connection arguments) are stored in the database's SYSUSERS +system table.
        • +
      • All future connections to the database are authenticated against the +credentials in its SYSUSERS system table.
        • +
      +
      +
      NATIVE authentication and other database properties +

      When NATIVE authentication is enabled, + behaves as if the +derby.connection.requireAuthentication and +derby.database.sqlAuthorization properties are also set. That +is, a user name and password must be specified whenever a user connects to a +database, and object owners control access to database objects. See + for more +information, and see + for an +example of the use of NATIVE authentication.

      +

      For maximum security, the passwords that users specify when they connect to +databases have an expiration date that you can modify by using the property +derby.authentication.native.passwordLifetimeMillis. The +password of the database owner never expires. By default, ordinary user +passwords expire after 31 days.

      +

      If a password is about to expire, or if the database owner's password is +near what would be the expiration date, + issues a warning that +the password will soon expire (or, in the database owner's case, that the +password is stale). By default, the warning is issued if the password is due to +expire in one-eighth of the password's lifetime. For example, if the password +has a 31-day lifetime, the warning will be issued 3.875 days before the +expiration date. You can change this proportion by using the property +derby.authentication.native.passwordLifetimeThreshold.

      +

      Use the derby.authentication.builtin.algorithm property to +change the way passwords are encrypted when they are stored in the SYSUSERS +system table. The default algorithm is SHA-256.

      +
      Managing users and passwords +

      To manage users and passwords, + provides a group of +system procedures:

      +
        +
      • To create users for a database, the database owner calls +SYSCS_UTIL.SYSCS_CREATE_USER, which takes a user name and +password as arguments. This procedure can also be executed by a user or role +to which the database owner has granted sufficient privileges.
        • +
      • To remove a user, the database owner calls +SYSCS_UTIL.SYSCS_DROP_USER, which takes one argument, +the user name of the user. This procedure can also be executed by a user or role +to which the database owner has granted sufficient privileges.
        • +
      • To reset a forgotten or expired password, the database owner calls +SYSCS_UTIL.SYSCS_RESET_PASSWORD, with a user name and +password as arguments. This procedure can also be executed by a user or role +to which the database owner has granted sufficient privileges.
        • +
      • To change a user's own password, any user can call the system procedure +SYSCS_UTIL.SYSCS_MODIFY_PASSWORD, which takes only one +argument, the password. Typically, a user calls this procedure when their +password is about to expire.
        • +
      +
      +
      Converting an existing database to use NATIVE authentication +

      If you wish to apply NATIVE authentication to a database that was created +without it, the procedure is slightly different depending on whether you specify +NATIVE:credentialsDB or NATIVE:credentialsDB:LOCAL:

      +
        +
      • If you specify NATIVE:credentialsDB, add users of the existing +database to the credentialsDB. Typically, you would specify uppercase +user names and case-sensitive passwords. For instance, if the old database was +created without any authentication, then its default user name is APP, and you +would do the following: +CALL SYSCS_UTIL.SYSCS_CREATE_USER('APP', 'app');
        • +
      • If you plan to specify NATIVE:credentialsDB:LOCAL, first connect to +the existing database as its database owner using its old authentication scheme. +Call SYSCS_UTIL.SYSCS_CREATE_USER to add credentials for the +database owner. For example, if the existing database was created with no +authentication, the database owner is APP, and you would add credentials for +APP as shown above.
        • +
      +
      +       +       Propchange: db/derby/docs/trunk/src/devguide/cdevcsecurenativeauth.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/devguide/cdevsetprop824451.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevsetprop824451.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/cdevsetprop824451.dita (original) +++ db/derby/docs/trunk/src/devguide/cdevsetprop824451.dita Fri Mar 23 19:19:26 2012 @@ -49,13 +49,15 @@ Conglomerates created earlier are unaffe

      Database-wide properties are stored in the database and are simpler for deployment, in the sense that they follow the database. Database-wide properties are also recommended for security reasons when you use - built-in user + BUILTIN user authentication (see ). System-wide properties can be more practical during the development process.

      's -built-in authentication mechanism is suitable only for development and testing -purposes. It is strongly recommended that production systems rely on LDAP or a -user-defined class for authentication. It is also strongly recommended that -production systems protect network connections with SSL/TLS.

      +BUILTIN authentication mechanism is suitable only for development and testing +purposes, and it will no longer be documented in future releases. It is strongly +recommended that production systems rely on NATIVE authentication, an external +directory service such as LDAP, or a user-defined class for authentication. It +is also strongly recommended that production systems protect network connections +with SSL/TLS.

       Modified: db/derby/docs/trunk/src/devguide/derbydev.ditamap URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/derbydev.ditamap?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/derbydev.ditamap (original) +++ db/derby/docs/trunk/src/devguide/derbydev.ditamap Fri Mar 23 19:19:26 2012 @@ -1300,14 +1300,14 @@ system"> - + + - @@ -1316,43 +1316,45 @@ system"> - + + + + - - - - + - + + + + - + + - - @@ -1361,6 +1363,7 @@ system"> + @@ -1531,16 +1534,6 @@ system"> - - - - - - - - - - @@ -2045,6 +2038,8 @@ with updatable result sets"> + + @@ -2090,11 +2085,6 @@ with updatable result sets"> - - - - - @@ -2102,6 +2092,11 @@ with updatable result sets"> + + + + + @@ -2131,17 +2126,9 @@ with updatable result sets"> - - - - - - - + - - - + Modified: db/derby/docs/trunk/src/devguide/rdevcsecure125.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecure125.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecure125.dita (original) +++ db/derby/docs/trunk/src/devguide/rdevcsecure125.dita Fri Mar 23 19:19:26 2012 @@ -19,7 +19,7 @@ See the License for the specific languag limitations under the License. --> -User authentication example in a client/server environment +Setting LDAP user authentication properties in a client/server environment In this example, is running in a user-designed application server. Modified: db/derby/docs/trunk/src/devguide/rdevcsecure557.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecure557.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecure557.dita (original) +++ db/derby/docs/trunk/src/devguide/rdevcsecure557.dita Fri Mar 23 19:19:26 2012 @@ -20,15 +20,21 @@ limitations under the License. --> List of user authentication properties -The following table summarizes the various properties related to user -authentication. +The following table summarizes the + properties related to +user authentication. user authenticationproperties, list of +
      +

      For details on these properties, see the +.

      +
      User authentication properties +This table lists and describes the properties related to user authentication. @@ -40,48 +46,67 @@ list of -derby.connection.requireAuthentication -Turns on user authentication. +derby.authentication.provider +Specifies the kind of user authentication to use. -derby.authentication.provider -Specifies the kind of user authentication to use. +derby.authentication.builtin.algorithm +Specifies the message digest algorithm to use to protect the +passwords that are stored in the database when using NATIVE or BUILTIN +authentication. + + +derby.authentication.native.passwordLifetimeMillis +Specifies the number of milliseconds that a password used for +NATIVE authentication remans valid. -derby.authentication.server +derby.authentication.native.passwordLifetimeThreshold +Specifies the threshold that triggers a password-expiration +warning for NATIVE authentication. + + +derby.connection.requireAuthentication +Turns on user authentication. If NATIVE authentication is +used, behaves as if +this property is set to TRUE. + + +derby.authentication.server For LDAP user authentication, specifies the location of the server. -derby.authentication.ldap.searchAuthDN, derby.authentication.ldap.searchAuthPW, -derby.authentication.ldap.searchFilter, and derby.authentication.ldap.searchBase +derby.authentication.ldap.searchAuthDN, +derby.authentication.ldap.searchAuthPW, +derby.authentication.ldap.searchFilter, and +derby.authentication.ldap.searchBase Configures the way that DN searches are performed. -derby.user.UserName -Creates a user name and password for the built-in user +derby.user.UserName +Creates a user name and password for the BUILTIN user repository in . -derby.authentication.builtin.algorithm -Specifies the message digest algorithm to use to protect the -passwords that are stored in the database when using built-in -authentication. - - -java.naming.* +java.naming.* JNDI properties. See Appendix A in the JNDI API reference -for more information about these properties. +(http://download.oracle.com/javase/1.5.0/docs/guide/jndi/spec/jn +di/properties.html) for more information about these properties.
      -

      's -built-in authentication mechanism is suitable only for development and testing -purposes. It is strongly recommended that production systems rely on LDAP or a -user-defined class for authentication. It is also strongly recommended that -production systems protect network connections with SSL/TLS.

      +'s +BUILTIN authentication mechanism is suitable only for development and testing +purposes, and it will no longer be documented in future releases. It is strongly +recommended that production systems rely on NATIVE authentication, an +external directory service such as LDAP, or a user-defined class for +authentication. It is also strongly recommended that production systems protect +network connections with SSL/TLS.
      Modified: db/derby/docs/trunk/src/devguide/rdevcsecure766.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecure766.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecure766.dita (original) +++ db/derby/docs/trunk/src/devguide/rdevcsecure766.dita Fri Mar 23 19:19:26 2012 @@ -44,7 +44,8 @@ Connection conn = DriverManager.getConne
      The password is not encrypted. When you are using in the context of a server framework, the framework should be responsible for encrypting the password across the network. If your framework does not encrypt -the password, consider using SSL.
      +the password, it is strongly recommended that you protect network connections +with SSL/TLS.

      For information about the treatment of user names within the system, see .

      Added: db/derby/docs/trunk/src/devguide/rdevcsecurenativeauthex.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/rdevcsecurenativeauthex.dita?rev=1304566&view=auto ============================================================================== --- db/derby/docs/trunk/src/devguide/rdevcsecurenativeauthex.dita (added) +++ db/derby/docs/trunk/src/devguide/rdevcsecurenativeauthex.dita Fri Mar 23 19:19:26 2012 @@ -0,0 +1,740 @@ + + + + + +NATIVE authentication and SQL authorization example +This example consists of the program +NativeAuthenticationExample.java, which shows how to use +'s NATIVE user +authentication and SQL authorization with either the embedded or the client +driver. + + +
      +It is strongly recommended that, in addition to using +'s NATIVE +authentication mechanism, LDAP, or a user-defined class for authentication, +production systems protect network connections with SSL/TLS. +

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

      +

      The program does the following:

      +
        +
      1. Uses a system property to set the authentication provider to +NATIVE:nativeAuthDB:LOCAL, meaning that +nativeAuthDB is the credentials database and that all user +credentials are stored there.
        2. +
      2. If you are running the program using the client driver, starts the Network +Server.
        3. +
      3. Creates a database named nativeAuthDB as the user +sysadm, who is therefore the database owner. Only the database +owner has the right to set and read database properties.
        4. +
      4. Calls the SYSCS_UTIL.SYSCS_CREATE_USER system procedure +to create several users: noacc, guest, and +sqlsam. The user sysadm has already been +created automatically.
        5. +
      5. Creates the roles adder and viewer.
        6. +
      6. Grants the role adder to sqlsam, and +grants the role viewer to guest.
        7. +
      7. Creates a table, accessibletbl, and inserts a value into +it.
        8. +
      8. Grants SELECT and INSERT privileges on accessibletbl to +adder.
        9. +
      9. Tries to connect to the database without supplying credentials, and fails, +as expected.
        10. +
      10. Connects to the database as a user who has not been granted any +privileges. The connection succeeds, but the user does not attempt to perform +any operations, since no operations would be permitted.
        11. +
      11. Connects to the database as guest, who has the role +viewer.
        12. +
      12. Sets the current role to viewer; the user succeeds in +executing a SELECT statement on the table, but cannot execute an INSERT +statement.
        13. +
      13. Connects to the database as sqlsam, who has the role +adder.
        14. +
      14. Sets the current role to adder; the user succeeds in +executing both a SELECT and an INSERT statement, but is unable to execute a +DELETE statement.
        15. +
      15. Using the connection of the database owner sysadm, deletes +the table, the two roles, and the three users created previously.
        16. +
      16. If you are running the program using the client driver, shuts down the +Network Server.
        17. +
      17. Closes the connection and shuts down +, using the database +owner's credentials.
        18. +
      +

      The instructions for compiling and running the program are in the comment +at the beginning of the program. DERBY_LIB is the directory +that contains the jar +files, typically DERBY_HOME/lib.

      +
      +Source code for <codeph>NativeAuthenticationExample.java</codeph> +// does not use derby.properties + +import java.io.PrintWriter; +import java.sql.*; + +import org.apache.derby.drda.NetworkServerControl; + +/* + * <p> + * This program showcases how SQL authorization is automatically turned + * on when you run with NATIVE authentication. You can run this program + * either embedded or client server. + * </p> + * + * <p> + * Here's how you compile the program: + * </p> + * + * <pre> + * javac -cp ${DERBY_LIB}/derbynet.jar NativeAuthenticationExample.java + * </pre> + * + * <p> + * Here's how you run the program embedded: + * </p> + * + * <pre> + * java -cp ${DERBY_LIB}/derby.jar:. NativeAuthenticationExample embedded + * </pre> + * + * <p> + * Here's how you run the program client/server: + * </p> + * + * <pre> + * java -cp \ + * ${DERBY_LIB}/derby.jar:${DERBY_LIB}/derbynet.jar:${DERBY_LIB}/derbyclient.jar:. \ + * NativeAuthenticationExample client + * </pre> + */ +public class NativeAuthenticationExample +{ + ///////////////////////////////////////////////////////////////////// + // + // CONSTANTS + // + ///////////////////////////////////////////////////////////////////// + + private static final String DB_NAME="nativeAuthDB"; + + // stored as SYSADM + private static final String DB_OWNER="sysadm"; + private static final String DB_OWNER_PASSWORD="shh123ihtybb87m"; + + private static final String USER_WITHOUT_ROLE="NOACC"; + private static final String + USER_WITHOUT_ROLE_PASSWORD="ajaxj3x9"; + + private static final String READER="GUEST"; + private static final String READER_PASSWORD="java5w6x"; + + private static final String WRITER="SQLSAM"; + private static final String WRITER_PASSWORD="light8q9bulb"; + + private static final String EMBEDDED = "embedded"; + private static final String CLIENT = "client"; + + ///////////////////////////////////////////////////////////////////// + // + // STATE + // + ///////////////////////////////////////////////////////////////////// + + private boolean _runningEmbedded; + private NetworkServerControl _server; + + ///////////////////////////////////////////////////////////////////// + // + // ENTRY POINT + // + ///////////////////////////////////////////////////////////////////// + + public static void main( String... args ) + { + NativeAuthenticationExample demo = parseArgs( args ); + + if ( demo != null ) + { + demo.execute(); + } + else + { + println( "Bad command line args." ); + } + } + + private static NativeAuthenticationExample parseArgs( String... args ) + { + if ( (args == null) || (args.length != 1) ) + { + return null; + } + + String mode = args[ 0 ]; + + if ( EMBEDDED.equals( mode ) ) + { + return new NativeAuthenticationExample( true ); + } + else if ( CLIENT.equals( mode ) ) + { + return new NativeAuthenticationExample( false ); + } + else + { + return null; + } + } + + ///////////////////////////////////////////////////////////////////// + // + // CONSTRUCTOR + // + ///////////////////////////////////////////////////////////////////// + + private NativeAuthenticationExample( boolean runningEmbedded ) + { + _runningEmbedded = runningEmbedded; + } + + ///////////////////////////////////////////////////////////////////// + // + // FEATURE SHOWCASE + // + ///////////////////////////////////////////////////////////////////// + + /** + * Run all of the experiments + */ + private void execute() + { + try + { + String authenticationProvider = + "NATIVE:" + DB_NAME + ":LOCAL"; + + // this turns on NATIVE authentication as well as + // SQL authorization + println( "Setting authentication provider to " + + authenticationProvider ); + System.setProperty( "derby.authentication.provider", + authenticationProvider ); + + if ( !_runningEmbedded ) + { + startServer(); + } + + Connection dboConn = createDatabase(); + + createUsers( dboConn ); + createRoles( dboConn ); + createTable( dboConn ); + + tryToConnectWithoutCredentials(); //should fail + + // a valid user can connect even if they haven't been + // assigned any roles + getConnection( USER_WITHOUT_ROLE, + USER_WITHOUT_ROLE_PASSWORD ); + + verifyReaderPrivileges(); + verifyWriterPrivileges(); + + println( "Using database owner connection again" ); + + dropTable( dboConn ); + dropRoles( dboConn ); + dropUsers( dboConn ); + + cleanUpAndShutDown(); + + } catch (Exception e) + { + errorPrintAndExit( e ); + } + } + + /** + * Create more users. Note that the credentials for the database + * owner were stored in the database automatically when the + * database was created. + */ + public void createUsers( Connection conn ) + throws SQLException + { + println( "Storing some sample users in the database." ); + + PreparedStatement ps = prepare + ( conn, "call syscs_util.syscs_create_user( ?, ? )" ); + + createUser( ps, USER_WITHOUT_ROLE, USER_WITHOUT_ROLE_PASSWORD ); + createUser( ps, READER, READER_PASSWORD ); + createUser( ps, WRITER, WRITER_PASSWORD ); + + ps.close(); + } + + private void createUser( PreparedStatement ps, String userName, + String password ) + throws SQLException + { + println( "Creating user " + userName ); + ps.setString( 1, userName ); + ps.setString( 2, password ); + ps.execute(); + } + + /** + * Create roles and grant them privileges. + */ + private void createRoles( Connection conn ) + throws SQLException + { + println( "Creating roles and granting privileges to them..." ); + + execute( conn, "CREATE ROLE adder" ); + execute( conn, "CREATE ROLE viewer" ); + + execute( conn, "GRANT adder TO " + WRITER ); + execute( conn, "GRANT viewer TO " + READER ); + } + + /** + * Create and populate a table and grant privileges related to it. + */ + private void createTable( Connection conn ) + throws SQLException + { + println("Creating table accessibletbl..."); + execute( conn, + "CREATE TABLE accessibletbl(textcol VARCHAR(6))" ); + execute( conn, "INSERT INTO accessibletbl VALUES('hello')" ); + + println( "Granting select/insert privileges to adder..." ); + execute( conn, + "GRANT SELECT, INSERT ON accessibletbl TO adder" ); + + println( "Granting select privileges to viewer" ); + execute( conn, "GRANT SELECT ON accessibletbl TO viewer" ); + } + + /** + * Drop users except for database owner. + */ + public void dropUsers( Connection conn ) + throws SQLException + { + println( "Dropping sample users from the database..." ); + + PreparedStatement ps = prepare + ( conn, "call syscs_util.syscs_drop_user( ? )" ); + + dropUser( ps, USER_WITHOUT_ROLE ); + dropUser( ps, READER ); + dropUser( ps, WRITER ); + + ps.close(); + } + + private void dropUser( PreparedStatement ps, String userName ) + throws SQLException + { + println( "Dropping user " + userName ); + ps.setString( 1, userName ); + ps.execute(); + } + + /** + * Drop roles. + */ + private void dropRoles( Connection conn ) + throws SQLException + { + println( "Dropping roles..." ); + + execute( conn, "DROP ROLE adder" ); + execute( conn, "DROP ROLE viewer" ); + } + + /** + * Drop the table. + */ + private void dropTable( Connection conn ) + throws SQLException + { + execute( conn, "DROP TABLE accessibletbl" ); + } + + /** + * Try to connect without supplying credentials + */ + private void tryToConnectWithoutCredentials() + throws Exception + { + println( "Trying to connect without supplying credentials..." ); + + try { + getConnection( null, null ); + println( "ERROR: Unexpectedly connected to database " + + DB_NAME ); + cleanUpAndShutDown(); + } catch (SQLException e) + { + if ( e.getSQLState().equals("08004") ) + { + println + ( + "As expected, could not get a connection without " + + "supplying credentials." + ); + } else + { + errorPrintAndExit( e ); + } + } + } + + /** + * Verify that the READER user can select but not insert + */ + private void verifyReaderPrivileges() + throws Exception + { + Connection readerConn = getConnection( READER, + READER_PASSWORD ); + + println( "Setting role to VIEWER" ); + execute( readerConn, "SET ROLE VIEWER" ); + + readRow( readerConn ); // should succeed + + try { + writeRow( readerConn ); + println( "ERROR: Unexpectedly allowed to insert into table" ); + cleanUpAndShutDown(); + } catch (SQLException e) + { + if ( e.getSQLState().equals("42500") ) + { + println( "As expected, failed to insert row." ); + } + else + { + errorPrintAndExit(e); + } + } + + readerConn.close(); + } + + /** + * Verify that the WRITER can read and write but not delete + */ + private void verifyWriterPrivileges() + throws Exception + { + Connection writerConn = getConnection( WRITER, + WRITER_PASSWORD ); + + // set role to ADDER + println( "Setting role to ADDER" ); + execute( writerConn, "SET ROLE ADDER" ); + + // should succeed + readRow( writerConn ); + writeRow( writerConn ); + + try { + deleteRow( writerConn ); // should fail + + println( "ERROR: Unexpectedly allowed to DELETE." ); + cleanUpAndShutDown(); + } catch (SQLException e) + { + if ( e.getSQLState().equals("42500") ) + { + println( "As expected, failed to delete rows." ); + } + else + { + errorPrintAndExit(e); + } + } + + writerConn.close(); + } + + private void readRow( Connection conn ) throws SQLException + { + PreparedStatement ps = prepare + ( conn, "SELECT * FROM sysadm.accessibletbl" ); + ResultSet rs = ps.executeQuery(); + while( rs.next() ) + { + println + ( "Value of sysadm.accessibletbl/textcol = " + + rs.getString( 1 ) ); + } + rs.close(); + ps.close(); + } + + private void writeRow( Connection conn ) throws SQLException + { + execute( conn, + "INSERT INTO sysadm.accessibletbl VALUES('guest')" ); + } + + private void deleteRow( Connection conn ) throws SQLException + { + execute( conn, "DELETE FROM sysadm.accessibletbl" ); + } + + ///////////////////////////////////////////////////////////////////// + // + // SQL HELPERS + // + ///////////////////////////////////////////////////////////////////// + + /** + * Execute a statement + */ + private void execute( Connection conn, String text ) + throws SQLException + { + PreparedStatement ps = prepare( conn, text ); + + ps.execute(); + ps.close(); + } + + /** + * Prepare a statement + */ + private PreparedStatement prepare( Connection conn, String text ) + throws SQLException + { + println( " Preparing: " + text ); + return conn.prepareStatement( text ); + } + + ///////////////////////////////////////////////////////////////////// + // + // CONNECTION MANAGEMENT + // + ///////////////////////////////////////////////////////////////////// + + /** + * Create the database + */ + private Connection createDatabase() + throws SQLException + { + String connectionURL = getConnectionURL + ( DB_NAME, DB_OWNER, DB_OWNER_PASSWORD, true, false ); + + println( "Creating database via this URL: " + connectionURL ); + + return DriverManager.getConnection( connectionURL ); + } + + /** + * Shut down the engine and exit. + */ + private void cleanUpAndShutDown() + throws Exception + { + // Shut down the server before the engine. this is so that + // we can authenticate the shutdown credentials in the + // booted database. + if ( _server != null ) + { + stopServer(); + } + + // the engine should only be brought down locally + _runningEmbedded = true; + shutdownEngine(); + + System.exit(1); + } + + private void shutdownEngine() + { + String shutdownURL = getConnectionURL + ( null, DB_OWNER, DB_OWNER_PASSWORD, false, true ); + + try + { + println( "Shutting down engine via this URL: " + + shutdownURL ); + DriverManager.getConnection( shutdownURL ); + } catch (SQLException se) + { + if ( se.getSQLState().equals("XJ015") ) + { + println( "Derby engine shut down normally" ); + } + else + { + printSQLException( se ); + } + } + } + + /** + * Get a connection to the database + */ + private Connection getConnection( String userName, String password ) + throws SQLException + { + String connectionURL = getConnectionURL + ( DB_NAME, userName, password, false, false ); + + println( "Getting connection via this URL: " + connectionURL ); + + return DriverManager.getConnection( connectionURL ); + } + + private String getConnectionURL( String dbName, String userName, + String password, boolean createDB, boolean shutdownDB ) + { + String connectionURL = _runningEmbedded ? + "jdbc:derby:" : + "jdbc:derby://localhost:1527/"; + + if ( dbName != null ) + { + connectionURL = connectionURL + DB_NAME; + } + if ( userName != null ) + { + connectionURL = connectionURL + ";user=" + userName; + } + if ( password != null) + { + connectionURL = connectionURL + ";password=" + password; + } + if ( createDB ) + { + connectionURL = connectionURL + ";create=true"; + } + if ( shutdownDB ) + { + connectionURL = connectionURL + ";shutdown=true"; + } + + return connectionURL; + } + + ///////////////////////////////////////////////////////////////////// + // + // SERVER MANAGEMENT + // + ///////////////////////////////////////////////////////////////////// + + /** + * Start the Derby server + */ + private void startServer() + throws Exception + { + _server = new NetworkServerControl( DB_OWNER, + DB_OWNER_PASSWORD ); + + println( "Starting the Derby server..." ); + _server.start( new PrintWriter( System.out ) ); + + // pause to let the server come up + Thread.sleep( 5000L ); + } + + /** + * Shut down the Derby server + */ + private void stopServer() + throws Exception + { + println( "Stopping the Derby server..." ); + _server.shutdown(); + + // pause to let the server come down + Thread.sleep( 5000L ); + } + + ///////////////////////////////////////////////////////////////////// + // + // DIAGNOSTIC PRINTING + // + ///////////////////////////////////////////////////////////////////// + + /** + * Report exceptions and exit. + */ + private void errorPrintAndExit( Throwable e ) + { + if ( e instanceof SQLException ) + { + printSQLException((SQLException) e); + } + else + { + println("A non-SQL error occurred."); + e.printStackTrace(); + } + + System.exit(1); + } + + /** + * Print a list of SQLExceptions. + */ + private void printSQLException( SQLException sqle ) + { + while (sqle != null) + { + println("\n---SQLException Caught---\n"); + println(" SQLState: " + (sqle).getSQLState()); + println(" Severity: " + (sqle).getErrorCode()); + println(" Message: " + (sqle).getMessage()); + + sqle.printStackTrace(); + + sqle = sqle.getNextException(); + } + } + + /** + * Print a diagnostic line to the console + */ + private static void println( String text ) + { + System.out.println( text ); + } +} + +       +       Propchange: db/derby/docs/trunk/src/devguide/rdevcsecurenativeauthex.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/devguide/tdevcsecure81850.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/tdevcsecure81850.dita?rev=1304566&r1=1304565&r2=1304566&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/tdevcsecure81850.dita (original) +++ db/derby/docs/trunk/src/devguide/tdevcsecure81850.dita Fri Mar 23 19:19:26 2012 @@ -37,17 +37,9 @@ be overridden by system properties by se to TRUE. See the for details on this property. To prevent unauthorized users from accessing databases once they -are booted, turn on user authentication for the database and configure user -authorization for the database. -If you are using 's -built-in users, configure each user as a database-level property so that user -names and passwords can be encrypted. -

      's -built-in authentication mechanism is suitable only for development and testing -purposes. It is strongly recommended that production systems rely on LDAP or a -user-defined class for authentication. It is also strongly recommended that -production systems protect network connections with SSL/TLS.

       -       +are booted, turn on user authentication and SQL authorization for the database. +Use NATIVE authentication or, alternatively, LDAP or a user-defined +class.