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

Parent topic: User authentication example in a single-user, embedded environment