MySQL remote access limitation

Discussion in 'Developers' Forum' started by Croydon, May 30, 2009.

  1. Croydon

    Croydon HowtoForge Supporter

    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<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(=> 'n',=> 'y')
            ),
        
    ### insert this
        
    'remote_ips' => array (
          
    'datatype'  => 'TEXT',
          
    'formtype'  => 'TEXT',
          
    'validators'  => array (  => 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.
     

    Attached Files:

    Last edited: May 30, 2009

Share This Page