MySQL remote access limitation

Hi again,

this time i wanted to extend the way a client can set up remote access to his databases so he can limit remote access to one or more ip addresses.

I made the following changes to ispc.


1. Update the ispc database

Code:

ALTER TABLE `web_database` ADD `test` TEXT NOT NULL AFTER `remote_ips` ;

2. Add a new validation class (interface/lib/classes/validate_database.inc.php)

PHP:

<?php

class validate_database {
    
    /*
        Validator function to check if a given list of ips is ok.
    */
    function valid_ip_list($field_name, $field_value, $validator) {
        global $app;
        
    if($_POST["remote_access"] == "y") {
        if(trim($field_value) == "") return;
        
        $values = split(",", $field_value);
        foreach($values as $cur_value) {
            $cur_value = trim($cur_value);
            
            $valid = true;
            if(preg_match("/^[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}(\.)[0-9]{1,3}$/", $cur_value)) {
                $groups = explode(".", $cur_value);
                foreach($groups as $group){
                  if($group<0 OR $group>255)
                  $valid=false;
                }
            } else {
                $valid = false;
            }
            
            if($valid == false) {
                $errmsg = $validator['errmsg'];
                if(isset($app->tform->wordbook[$errmsg])) {
                    return $app->tform->wordbook[$errmsg]."<br>\r\n";
                } else {
                    return $errmsg."<br>\r\n";
                }
            }
        }
    }
  }    
    
}

3. change tform file (interface/web/sites/form/database.tform.php)

PHP:

        'active' => array (
            'datatype'    => 'VARCHAR',
            'formtype'    => 'CHECKBOX',
            'default'    => 'y',
            'value'        => array(0 => 'n',1 => 'y')
        ),
    ### insert this
    'remote_ips' => array (
      'datatype'  => 'TEXT',
      'formtype'  => 'TEXT',
      'validators'  => array (  0 => array (  'type' => 'CUSTOM',
                                              'class' => 'validate_database',
                                              'function' => 'valid_ip_list',
                                              'errmsg' => 'database_remote_error_ips'),
                             ),
      'default' => '',
      'value'   => '',
      'width'   => '60'
    ),
    ### end insert
    ##################################
    # ENDE Datatable fields
    ##################################
    )


4. change lang file (interface/web/sites/lib/lang/en_database.lng)

PHP:

$wb["remote_access_txt"] = 'Remote Access';
###insert this
$wb["remote_ips_txt"] = 'Remote Access IPs (separate by , and leave blank for <i>any</i>)';
$wb["database_remote_error_ips"] = 'At least one of the entered ip addresses is invalid.';
###end insert
$wb["client_txt"] = 'Client';


5. alter template file (interface/web/sites/templates/database_edit.htm)
insert this

HTML:

      <div class="ctrlHolder">
        <label for="remote_ips">{tmpl_var name='remote_ips_txt'}</label>
        <input name="remote_ips" id="remote_ips" value="{tmpl_var name='remote_ips'}" size="60" type="text" class="textInput formLengthHalf" />
      </div>

directly after

HTML:

      <div class="ctrlHolder">
        <p class="label">{tmpl_var name='remote_access_txt'}</p>
          <div class="multiField">
            {tmpl_var name='remote_access'}
          </div>
      </div>

6. apply the attached patch to the "server/plugins-available/mysql_clientdb_plugin.inc.php" or use the attached and modified version.
patching is done (at least on debian linux) with

Code:

patch /path/to/ispc/server/plugins-available/mysql_clientdb_plugin.inc.php /path/to/patchfile/mysql_clientdb_plugin.inc.php.patch

Now you are done (hopefully).
When enabling remote access to a database you can now limit the ip addresses that can access the database by entering a comma separated list of ips into the new field.