MySQL remote access limitation

[Croydon]

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
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

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)
'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)
$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

<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

<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
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.