Single-User, Embedded Environment

In this example, Derby is embedded in a single-user application that is deployed in a number of different and potentially insecure ways. For that reason, the application developer has decided to encrypt the database and to turn on user authentication using Derby's built-in user authentication, which will not require connections to an LDAP server. The end-user must know the bootPassword to boot the database and the user name and password to connect to the database. Even if the database ended up in an e-mail, only the intended recipient would be able to access data in the database. The application developer has decided not to use any user authorization features, since each database will accept only a single user. In that situation, the default full-access connection mode is acceptable.

When creating the database, the application developer encrypts the database by using the following connection URL:

jdbc:derby:wombat;create=true;dataEncryption=true;
    bootPassword=sxy90W348HHn

Before deploying the database, the application developer turns on user authentication, sets the authentication provider to BUILTIN, creates a single user and password, and disallows system-wide properties to protect the database-wide security property settings:

SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.connection.requireAuthentication', 'true')
 
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.authentication.provider', 'BUILTIN')
 
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.user.enduser', 'red29PlaNe')
 
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.propertiesOnly', true')

When the user connects (and boots) the database, the user has to provide the bootPassword, the user name, and the password. The following example shows how to provide those in a connection URL, although the application programmer would probably provide GUI windows to allow the end user to type those in:

jdbc:derby:wombat;bootPassword=sxy90W348HHn;
    user=enduser;password=red29PlaNe

The following two examples from the sample database show how to turn on and turn off user authentication using Derby's built-in user authentication and user authorization.

/** 
      * 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 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(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(); 
    } 

/** 
      * 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(); 
    }
}