1DBI::ProxyServer(3)   User Contributed Perl Documentation  DBI::ProxyServer(3)
2
3
4

NAME

6       DBI::ProxyServer - a server for the DBD::Proxy driver
7

SYNOPSIS

9           use DBI::ProxyServer;
10           DBI::ProxyServer::main(@ARGV);
11

DESCRIPTION

13       DBI::Proxy Server is a module for implementing a proxy for the DBI
14       proxy driver, DBD::Proxy. It allows access to databases over the
15       network if the DBMS does not offer networked operations. But the proxy
16       server might be useful for you, even if you have a DBMS with integrated
17       network functionality: It can be used as a DBI proxy in a firewalled
18       environment.
19
20       DBI::ProxyServer runs as a daemon on the machine with the DBMS or on
21       the firewall. The client connects to the agent using the DBI driver
22       DBD::Proxy, thus in the exactly same way than using DBD::mysql,
23       DBD::mSQL or any other DBI driver.
24
25       The agent is implemented as a RPC::PlServer application. Thus you have
26       access to all the possibilities of this module, in particular
27       encryption and a similar configuration file. DBI::ProxyServer adds the
28       possibility of query restrictions: You can define a set of queries that
29       a client may execute and restrict access to those. (Requires a DBI
30       driver that supports parameter binding.) See "CONFIGURATION FILE".
31
32       The provided driver script, dbiproxy, may either be used as it is or
33       used as the basis for a local version modified to meet your needs.
34

OPTIONS

36       When calling the DBI::ProxyServer::main() function, you supply an array
37       of options. These options are parsed by the Getopt::Long module.  The
38       ProxyServer inherits all of RPC::PlServer's and hence Net::Daemon's
39       options and option handling, in particular the ability to read options
40       from either the command line or a config file. See RPC::PlServer. See
41       Net::Daemon. Available options include
42
43       chroot (--chroot=dir)
44           (UNIX only)  After doing a bind(), change root directory to the
45           given directory by doing a chroot(). This is useful for security,
46           but it restricts the environment a lot. For example, you need to
47           load DBI drivers in the config file or you have to create hard
48           links to Unix sockets, if your drivers are using them. For example,
49           with MySQL, a config file might contain the following lines:
50
51               my $rootdir = '/var/dbiproxy';
52               my $unixsockdir = '/tmp';
53               my $unixsockfile = 'mysql.sock';
54               foreach $dir ($rootdir, "$rootdir$unixsockdir") {
55                   mkdir 0755, $dir;
56               }
57               link("$unixsockdir/$unixsockfile",
58                    "$rootdir$unixsockdir/$unixsockfile");
59               require DBD::mysql;
60
61               {
62                   'chroot' => $rootdir,
63                   ...
64               }
65
66           If you don't know chroot(), think of an FTP server where you can
67           see a certain directory tree only after logging in. See also the
68           --group and --user options.
69
70       clients
71           An array ref with a list of clients. Clients are hash refs, the
72           attributes accept (0 for denying access and 1 for permitting) and
73           mask, a Perl regular expression for the clients IP number or its
74           host name.
75
76       configfile (--configfile=file)
77           Config files are assumed to return a single hash ref that overrides
78           the arguments of the new method. However, command line arguments in
79           turn take precedence over the config file. See the "CONFIGURATION
80           FILE" section below for details on the config file.
81
82       debug (--debug)
83           Turn debugging mode on. Mainly this asserts that logging messages
84           of level "debug" are created.
85
86       facility (--facility=mode)
87           (UNIX only) Facility to use for Sys::Syslog. The default is daemon.
88
89       group (--group=gid)
90           After doing a bind(), change the real and effective GID to the
91           given.  This is useful, if you want your server to bind to a
92           privileged port (<1024), but don't want the server to execute as
93           root. See also the --user option.
94
95           GID's can be passed as group names or numeric values.
96
97       localaddr (--localaddr=ip)
98           By default a daemon is listening to any IP number that a machine
99           has. This attribute allows to restrict the server to the given IP
100           number.
101
102       localport (--localport=port)
103           This attribute sets the port on which the daemon is listening. It
104           must be given somehow, as there's no default.
105
106       logfile (--logfile=file)
107           Be default logging messages will be written to the syslog (Unix) or
108           to the event log (Windows NT). On other operating systems you need
109           to specify a log file. The special value "STDERR" forces logging to
110           stderr. See Net::Daemon::Log for details.
111
112       mode (--mode=modename)
113           The server can run in three different modes, depending on the
114           environment.
115
116           If you are running Perl 5.005 and did compile it for threads, then
117           the server will create a new thread for each connection. The thread
118           will execute the server's Run() method and then terminate. This
119           mode is the default, you can force it with "--mode=threads".
120
121           If threads are not available, but you have a working fork(), then
122           the server will behave similar by creating a new process for each
123           connection.  This mode will be used automatically in the absence of
124           threads or if you use the "--mode=fork" option.
125
126           Finally there's a single-connection mode: If the server has
127           accepted a connection, he will enter the Run() method. No other
128           connections are accepted until the Run() method returns (if the
129           client disconnects).  This operation mode is useful if you have
130           neither threads nor fork(), for example on the Macintosh. For
131           debugging purposes you can force this mode with "--mode=single".
132
133       pidfile (--pidfile=file)
134           (UNIX only) If this option is present, a PID file will be created
135           at the given location. Default is to not create a pidfile.
136
137       user (--user=uid)
138           After doing a bind(), change the real and effective UID to the
139           given.  This is useful, if you want your server to bind to a
140           privileged port (<1024), but don't want the server to execute as
141           root. See also the --group and the --chroot options.
142
143           UID's can be passed as group names or numeric values.
144
145       version (--version)
146           Supresses startup of the server; instead the version string will be
147           printed and the program exits immediately.
148

SHUTDOWN

150       DBI::ProxyServer is built on RPC::PlServer which is, in turn, built on
151       Net::Daemon.
152
153       You should refer to Net::Daemon for how to shutdown the server, except
154       that you can't because it's not currently documented there (as of
155       v0.43).  The bottom-line is that it seems that there's no support for
156       graceful shutdown.
157

CONFIGURATION FILE

159       The configuration file is just that of RPC::PlServer or Net::Daemon
160       with some additional attributes in the client list.
161
162       The config file is a Perl script. At the top of the file you may
163       include arbitraty Perl source, for example load drivers at the start
164       (useful to enhance performance), prepare a chroot environment and so
165       on.
166
167       The important thing is that you finally return a hash ref of option
168       name/value pairs. The possible options are listed above.
169
170       All possibilities of Net::Daemon and RPC::PlServer apply, in particular
171
172       Host and/or User dependent access control
173       Host and/or User dependent encryption
174       Changing UID and/or GID after binding to the port
175       Running in a chroot() environment
176
177       Additionally the server offers you query restrictions. Suggest the
178       following client list:
179
180           'clients' => [
181               { 'mask' => '^admin\.company\.com$',
182                 'accept' => 1,
183                 'users' => [ 'root', 'wwwrun' ],
184               },
185               {
186                 'mask' => '^admin\.company\.com$',
187                 'accept' => 1,
188                 'users' => [ 'root', 'wwwrun' ],
189                 'sql' => {
190                      'select' => 'SELECT * FROM foo',
191                      'insert' => 'INSERT INTO foo VALUES (?, ?, ?)'
192                      }
193               }
194
195       then only the users root and wwwrun may connect from admin.company.com,
196       executing arbitrary queries, but only wwwrun may connect from other
197       hosts and is restricted to
198
199           $sth->prepare("select");
200
201       or
202
203           $sth->prepare("insert");
204
205       which in fact are "SELECT * FROM foo" or "INSERT INTO foo VALUES (?, ?,
206       ?)".
207

Proxyserver Configuration file (bigger example)

209       This section tells you how to restrict a DBI-Proxy: Not every user from
210       every workstation shall be able to execute every query.
211
212       There is a perl program "dbiproxy" which runs on a machine which is
213       able to connect to all the databases we wish to reach. All Perl-DBD-
214       drivers must be installed on this machine. You can also reach databases
215       for which drivers are not available on the machine where you run the
216       programm querying the database, e.g. ask MS-Access-database from Linux.
217
218       Create a configuration file "proxy_oracle.cfg" at the dbproxy-server:
219
220           {
221               # This shall run in a shell or a DOS-window
222               # facility => 'daemon',
223               pidfile => 'your_dbiproxy.pid',
224               logfile => 1,
225               debug => 0,
226               mode => 'single',
227               localport => '12400',
228
229               # Access control, the first match in this list wins!
230               # So the order is important
231               clients => [
232                       # hint to organize:
233                       # the most specialized rules for single machines/users are 1st
234                       # then the denying rules
235                       # the the rules about whole networks
236
237                       # rule: internal_webserver
238                       # desc: to get statistical information
239                       {
240                               # this IP-address only is meant
241                               mask => '^10\.95\.81\.243$',
242                               # accept (not defer) connections like this
243                               accept => 1,
244                               # only users from this list
245                               # are allowed to log on
246                               users => [ 'informationdesk' ],
247                               # only this statistical query is allowed
248                               # to get results for a web-query
249                               sql => {
250                                       alive => 'select count(*) from dual',
251                                       statistic_area => 'select count(*) from e01admin.e01e203 where geb_bezei like ?',
252                               }
253                       },
254
255                       # rule: internal_bad_guy_1
256                       {
257                               mask => '^10\.95\.81\.1$',
258                               accept => 0,
259                       },
260
261                       # rule: employee_workplace
262                       # desc: get detailled informations
263                       {
264                               # any IP-address is meant here
265                               mask => '^10\.95\.81\.(\d+)$',
266                               # accept (not defer) connections like this
267                               accept => 1,
268                               # only users from this list
269                               # are allowed to log on
270                               users => [ 'informationdesk', 'lippmann' ],
271                               # all these queries are allowed:
272                               sql => {
273                                       search_city => 'select ort_nr, plz, ort from e01admin.e01e200 where plz like ?',
274                                       search_area => 'select gebiettyp, geb_bezei from e01admin.e01e203 where geb_bezei like ? or geb_bezei like ?',
275                               }
276                       },
277
278                       # rule: internal_bad_guy_2
279                       # This does NOT work, because rule "employee_workplace" hits
280                       # with its ip-address-mask of the whole network
281                       {
282                               # don't accept connection from this ip-address
283                               mask => '^10\.95\.81\.5$',
284                               accept => 0,
285                       }
286               ]
287           }
288
289       Start the proxyserver like this:
290
291               rem well-set Oracle_home needed for Oracle
292               set ORACLE_HOME=d:\oracle\ora81
293               dbiproxy --configfile proxy_oracle.cfg
294
295   Testing the connection from a remote machine
296       Call a programm "dbish" from your commandline. I take the machine from
297       rule "internal_webserver"
298
299               dbish "dbi:Proxy:hostname=oracle.zdf;port=12400;dsn=dbi:Oracle:e01" informationdesk xxx
300
301       There will be a shell-prompt:
302
303               informationdesk@dbi...> alive
304
305               Current statement buffer (enter '/'...):
306               alive
307
308               informationdesk@dbi...> /
309               COUNT(*)
310               '1'
311               [1 rows of 1 fields returned]
312
313   Testing the connection with a perl-script
314       Create a perl-script like this:
315
316               # file: oratest.pl
317               # call me like this: perl oratest.pl user password
318
319               use strict;
320               use DBI;
321
322               my $user = shift || die "Usage: $0 user password";
323               my $pass = shift || die "Usage: $0 user password";
324               my $config = {
325                       dsn_at_proxy => "dbi:Oracle:e01",
326                       proxy => "hostname=oechsle.zdf;port=12400",
327               };
328               my $dsn = sprintf "dbi:Proxy:%s;dsn=%s",
329                       $config->{proxy},
330                       $config->{dsn_at_proxy};
331
332               my $dbh = DBI->connect( $dsn, $user, $pass )
333                       || die "connect did not work: $DBI::errstr";
334
335               my $sql = "search_city";
336               printf "%s\n%s\n%s\n", "="x40, $sql, "="x40;
337               my $cur = $dbh->prepare($sql);
338               $cur->bind_param(1,'905%');
339               &show_result ($cur);
340
341               my $sql = "search_area";
342               printf "%s\n%s\n%s\n", "="x40, $sql, "="x40;
343               my $cur = $dbh->prepare($sql);
344               $cur->bind_param(1,'Pfarr%');
345               $cur->bind_param(2,'Bronnamberg%');
346               &show_result ($cur);
347
348               my $sql = "statistic_area";
349               printf "%s\n%s\n%s\n", "="x40, $sql, "="x40;
350               my $cur = $dbh->prepare($sql);
351               $cur->bind_param(1,'Pfarr%');
352               &show_result ($cur);
353
354               $dbh->disconnect;
355               exit;
356
357
358               sub show_result {
359                       my $cur = shift;
360                       unless ($cur->execute()) {
361                               print "Could not execute\n";
362                               return;
363                       }
364
365                       my $rownum = 0;
366                       while (my @row = $cur->fetchrow_array()) {
367                               printf "Row is: %s\n", join(", ",@row);
368                               if ($rownum++ > 5) {
369                                       print "... and so on\n";
370                                       last;
371                               }
372                       }
373                       $cur->finish;
374               }
375
376       The result
377
378               C:\>perl oratest.pl informationdesk xxx
379               ========================================
380               search_city
381               ========================================
382               Row is: 3322, 9050, Chemnitz
383               Row is: 3678, 9051, Chemnitz
384               Row is: 10447, 9051, Chemnitz
385               Row is: 12128, 9051, Chemnitz
386               Row is: 10954, 90513, Zirndorf
387               Row is: 5808, 90513, Zirndorf
388               Row is: 5715, 90513, Zirndorf
389               ... and so on
390               ========================================
391               search_area
392               ========================================
393               Row is: 101, Bronnamberg
394               Row is: 400, Pfarramt Zirndorf
395               Row is: 400, Pfarramt Rosstal
396               Row is: 400, Pfarramt Oberasbach
397               Row is: 401, Pfarramt Zirndorf
398               Row is: 401, Pfarramt Rosstal
399               ========================================
400               statistic_area
401               ========================================
402               DBD::Proxy::st execute failed: Server returned error: Failed to execute method CallMethod: Unknown SQL query: statistic_area at E:/Perl/site/lib/DBI/ProxyServer.pm line 258.
403               Could not execute
404
405   How the configuration works
406       The most important section to control access to your dbi-proxy is
407       "client=>" in the file "proxy_oracle.cfg":
408
409       Controlling which person at which machine is allowed to access
410
411       ·   "mask" is a perl regular expression against the plain ip-address of
412           the machine which wishes to connect _or_ the reverse-lookup from a
413           nameserver.
414
415       ·   "accept" tells the dbiproxy-server wether ip-adresse like in "mask"
416           are allowed to connect or not (0/1)
417
418       ·   "users" is a reference to a list of usernames which must be
419           matched, this is NOT a regular expression.
420
421       Controlling which SQL-statements are allowed
422
423       You can put every SQL-statement you like in simply ommiting "sql =>
424       ...", but the more important thing is to restrict the connection so
425       that only allowed queries are possible.
426
427       If you include an sql-section in your config-file like this:
428
429               sql => {
430                       alive => 'select count(*) from dual',
431                       statistic_area => 'select count(*) from e01admin.e01e203 where geb_bezei like ?',
432               }
433
434       The user is allowed to put two queries against the dbi-proxy. The
435       queries are _not_ "select count(*)...", the queries are "alive" and
436       "statistic_area"! These keywords are replaced by the real query. So you
437       can run a query for "alive":
438
439               my $sql = "alive";
440               my $cur = $dbh->prepare($sql);
441               ...
442
443       The flexibility is that you can put parameters in the where-part of the
444       query so the query are not static. Simply replace a value in the where-
445       part of the query through a question mark and bind it as a parameter to
446       the query.
447
448               my $sql = "statistic_area";
449               my $cur = $dbh->prepare($sql);
450               $cur->bind_param(1,'905%');
451               # A second parameter would be called like this:
452               # $cur->bind_param(2,'98%');
453
454       The result is this query:
455
456               select count(*) from e01admin.e01e203
457               where geb_bezei like '905%'
458
459       Don't try to put parameters into the sql-query like this:
460
461               # Does not work like you think.
462               # Only the first word of the query is parsed,
463               # so it's changed to "statistic_area", the rest is omitted.
464               # You _have_ to work with $cur->bind_param.
465               my $sql = "statistic_area 905%";
466               my $cur = $dbh->prepare($sql);
467               ...
468
469   Problems
470       ·   I don't know how to restrict users to special databases.
471
472       ·   I don't know how to pass query-parameters via dbish
473

AUTHOR

475           Copyright (c) 1997    Jochen Wiedmann
476                                 Am Eisteich 9
477                                 72555 Metzingen
478                                 Germany
479
480                                 Email: joe@ispsoft.de
481                                 Phone: +49 7123 14881
482
483       The DBI::ProxyServer module is free software; you can redistribute it
484       and/or modify it under the same terms as Perl itself. In particular
485       permission is granted to Tim Bunce for distributing this as a part of
486       the DBI.
487

SEE ALSO

489       dbiproxy, DBD::Proxy, DBI, RPC::PlServer, RPC::PlClient, Net::Daemon,
490       Net::Daemon::Log, Sys::Syslog, Win32::EventLog, syslog
491
492
493
494perl v5.10.1                      2009-06-05               DBI::ProxyServer(3)
Impressum