8.2.1.1. Setting up a proxy server

You will also need to configure your ODBC data source within the Windows ODBC Control Panel. For our megalithic database, let's call the ODBC data source archaeo.

We can test that this data source is correctly configured using the DBI Shell dbish locally on the Windows machine:

dbish dbi:ODBC:archaeo

or via a short script that can be run on your Windows machine:

use DBI;
$dbh = DBI->connect( "dbi:ODBC:archaeo", "username", "password" );
$dbh->disconnect(  );

If dbish connects, or if no errors occur when executing the script, it looks like everything's installed and configured correctly.

perl -MCPAN -e 'install Bundle::DBI'

The crucial information required by dbiproxy is the port number to which the proxy server should listen for incoming proxy client connections. If the port number is 3333, we can run the proxy server with the following command:

dbiproxy --localport 3333

This will start up the server; it's now waiting for connections. If you want to verify that the server is indeed up and running, you can run it with the --debug flag and the optional --logfile flag to specify where the debug output will go.

For example:

dbiproxy --localport 3333 --debug

will produce debug output either in the command prompt window on a Windows machine, and or via syslog(1) on a Unix machine. On Unix workstations, you can redirect the output to the current terminal with:

dbiproxy --localport 3333 --debug --logfile /dev/tty

This should behave correctly under most modern Unix platforms.

8.2.1.2. Connecting to the proxy server

For example, the ODBC test script above connects directly via the DBD::ODBC module with the following DBI->connect( ) call:

$dbh = DBI->connect( "dbi:ODBC:archaeo", "username", "password" );

This is fine for local connections, but how do we translate that into something the proxy server can use?

$dsn   = "dbi:ODBC:archaeo";
$proxy = "hostname=fowliswester;port=3333";
$dbh = DBI->connect( "dbi:Proxy:$proxy;dsn=$dsn", '', '' );

This looks quite long-winded, but it's a very compact and portable way to make a pass-through connection to a remote database by proxy.

Once you have connected to the proxy server and it connects to the desired data source, a valid database handle will be returned, allowing you to issue queries exactly as if you had connected directly to that database. Therefore, when using a proxy server, only the DBI->connect( ) call will vary -- which is exactly the same behavior as changing from one database to another.

dbi:Proxy:hostname=fowliswester;port=3333

The value contained within the DBI_AUTOPROXY value has the DSN specified in the DBI->connect( ) call concatenated to it to produce the correct proxy DSN. For example:

$ENV{DBI_AUTOPROXY} = 'dbi:Proxy:hostname=fowliswester;port=3333';
$dbh = DBI->connect( "dbi:ODBC:archaeo", "username", "password" );

would result in the script attempting a connection to the DSN of:

dbi:Proxy:hostname=fowliswester;port=3333;dsn=dbi:ODBC:archaeo

The other important point to stress regarding the client is that you do not need to install any database drivers whatsoever. The database drivers are used only by the proxy server.

8.2.2.1. Access configuration

The configuration files for these modules are expressed as Perl scripts in which various options are set. The most useful options are those that allow you to specify access lists. Access lists allow you to control which machines may connect to the proxy server, and the mode that the network transport between these machines and the proxy server operates in.

For example, if you had a secure corporate LAN containing a database server and client PCs, you might say that the client PCs could connect to the central database via a proxy server without any authentication or encryption. That is, a PC connected to the LAN is trusted.

However, computers in employees' houses that need access to the database are not trusted, as the data flowing across the phone line might be somehow intercepted by competitors. Therefore, the network transport between these machines and the central database server is encrypted.

A sample configuration file for the proxy server might look like:

{
    facility => 'daemon',
    pidfile  => '/var/dbiproxy/dbiproxy.pid',
    user  => 'nobody',
    group => 'nobody',
    localport => '3333',
    mode      => 'fork',

    # Access control
    clients => [
        # Accept the local LAN ( 192.168.1.* )
        {
            mask   => '^192\.168\.1\.\d+$',
            accept => 1
        },
        # Accept our off-site machines ( 192.168.2.* ) but with a cipher
        {
            mask   => '^192\.168\.2\.\d+$',
            accept => 1,
            # We'll discuss secure encryption ciphers shortly
            cipher => Crypt::IDEA->new( 'be39893df23f98a2' )
        },
        # Deny everything else
        {
            mask   => '.*',
            accept => 0
        }
    ]
}

dbiproxy --configfile <filename>

For example, if we had saved the above configuration file as proxy.config, we could start up dbiproxy with the command:

dbiproxy --configfile proxy.config

Furthermore, the DBI::ProxyServer configuration file also allows us to apply access lists to individual types of statements. For example, you might want the workstations of sales operators to be able to query data, but not change it in any way. This can be done using the following configuration options:

# Only allow the given SELECT queries from sales 
# workstations ( 192.168.3.* )
clients => [
    {
        mask   => '^192\.168\.3\.\d+$',
        accept => 1,
        sql    => {
            select => 'SELECT name, mapref FROM megaliths WHERE name = ?'
        }
    },
]

The other statement restriction keys that you can use are insert, update, and delete. For example, if you wished to allow only particular DELETE statements to be executed, you could write the following access control:

sql => {
    delete => 'DELETE FROM megaliths WHERE id = ?'
}

This control would refuse any DELETE statements that did not conform to the given control mask, such as someone maliciously executing DELETE FROM megaliths.

8.2.2.2. Compression

In the previous example, we discussed the possibility of a user querying the database via a modem line and proxy server. Suppose the user executes a query that returns 100,000 rows of data, each row being around 1 KB. That's a lot of information to pull across a slow network connection.

--compression gzip

which specifies that the GNU gzip compression method should be used.

In order for your client to be able to send and receive compressed data from the DBI proxy server, you also must tell the proxy driver to use a compressed data stream. This is done by specifying the additional DSN key/value pair of compression=gzip when connecting to the database. For example:

$proxyloc = 'hostname=fowliswester;port=3333';
$compression = 'compression=gzip';
$dsn = 'dbi:ODBC:archaeo';
$dbh = DBI->connect( "dbi:Proxy:$proxyloc;$compression;dsn=$dsn", 
                     "username", "password" );

The trade-off is the cost in CPU time for the proxy server and proxy client to compress and decompress the data, respectively. From a client perspective, this is probably not an issue, but the proxy server might be affected, especially if several large queries are being executed simultaneously, with each requiring compression.

However, compression is a useful and transparent feature that can increase the efficiency of your networks and databases when using DBI proxying.

8.2.2.3. Ciphers

This functionality is useful if you are implementing a secure networked database environment where database operations might be occurring over nonsecure network links, such as a phone line through a public ISP. For example, an employee at home might use his or her own ISP to access a secure company database. Or you might wish to make an e-commerce transaction between two participating financial institutions.

Therefore, to support encryption via DBI proxying, we need to configure both the client connecting to the proxy server to use encryption and also configure the server to use the same encryption.

The configuration of the client is trivial and is simply a case of specifying additional arguments to the DBI->connect( ) call. For example, to use Crypt::IDEA as the underlying ciphering method, we can write:

use Crypt::IDEA;

### The key is a random, but long, hexadecimal number!
$key = 'b3a6d83ef3187ac4';

### Connect to the proxy server
$dbh = DBI->connect( "dbi:Proxy:cipher=IDEA;key=$key;...", '', '' );

For example, if we were transmitting sensitive but not confidential data from our internal database to someone's home PC, we might wish to use the relatively low-grade encryption offered by Crypt::DES. However, if more confidential data was being transmitted, we might wish to switch over to using the stronger but slower encryption of Crypt::IDEA.

require Crypt::IDEA;

### The key to encrypt data with
$key = 'b3a6d83ef3187ac4';

{
    clients => [ {
        'accept' => 1,
        'cipher' => IDEA->new( pack( "H*", $key ) )
      } ]
}