User authentication and authorization extended examples
The following two examples from the sample database show
how to turn on and turn off user authentication using 's
built-in user authentication and user authorization.
'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.
/**
* 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
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();
}
}