1DBI::ProxyServer(3) User Contributed Perl Documentation DBI::ProxyServer(3)
2
6 DBI::ProxyServer - a server for the DBD::Proxy driver
7
9 use DBI::ProxyServer;
10 DBI::ProxyServer::main(@ARGV);
11
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 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 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 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
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 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 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 {
62 'chroot' => $rootdir,
63 ...
64 }
65 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 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 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 debug (--debug)
83 Turn debugging mode on. Mainly this asserts that logging messages
84 of level "debug" are created.
85 facility (--facility=mode)
87 (UNIX only) Facility to use for Sys::Syslog. The default is daemon.
88 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 GID's can be passed as group names or numeric values.
96 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 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 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 mode (--mode=modename)
113 The server can run in three different modes, depending on the
114 environment.
115 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 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 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 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 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 UID's can be passed as group names or numeric values.
144 version (--version)
146 Supresses startup of the server; instead the version string will be
147 printed and the program exits immediately.
148
150 DBI::ProxyServer is built on RPC::PlServer which is, in turn, built on
151 Net::Daemon.
152 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
159 The configuration file is just that of RPC::PlServer or Net::Daemon
160 with some additional attributes in the client list.
161 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 The important thing is that you finally return a hash ref of option
168 name/value pairs. The possible options are listed above.
169 All possibilities of Net::Daemon and RPC::PlServer apply, in particular
171 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 Additionally the server offers you query restrictions. Suggest the
178 following client list:
179 '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 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 $sth->prepare("select");
200 or
202 $sth->prepare("insert");
204 which in fact are "SELECT * FROM foo" or "INSERT INTO foo VALUES (?, ?,
206 ?)".
207
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 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 Create a configuration file "proxy_oracle.cfg" at the dbproxy-server:
219 {
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 # 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 # 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 # rule: internal_bad_guy_1
256 {
257 mask => '^10\.95\.81\.1$',
258 accept => 0,
259 },
260 # 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 # 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 Start the proxyserver like this:
290 rem well-set Oracle_home needed for Oracle
292 set ORACLE_HOME=d:\oracle\ora81
293 dbiproxy --configfile proxy_oracle.cfg
294 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 dbish "dbi:Proxy:hostname=oracle.zdf;port=12400;dsn=dbi:Oracle:e01" informationdesk xxx
300 There will be a shell-prompt:
302 informationdesk@dbi...> alive
304 Current statement buffer (enter '/'...):
306 alive
307 informationdesk@dbi...> /
309 COUNT(*)
310 '1'
311 [1 rows of 1 fields returned]
312 Testing the connection with a perl-script
314 Create a perl-script like this:
315 # file: oratest.pl
317 # call me like this: perl oratest.pl user password
318 use strict;
320 use DBI;
321 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 my $dbh = DBI->connect( $dsn, $user, $pass )
333 || die "connect did not work: $DBI::errstr";
334 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 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 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 $dbh->disconnect;
355 exit;
356 sub show_result {
359 my $cur = shift;
360 unless ($cur->execute()) {
361 print "Could not execute\n";
362 return;
363 }
364 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 The result
377 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 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 Controlling which person at which machine is allowed to access
410 · "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 · "accept" tells the dbiproxy-server wether ip-adresse like in "mask"
416 are allowed to connect or not (0/1)
417 · "users" is a reference to a list of usernames which must be
419 matched, this is NOT a regular expression.
420 Controlling which SQL-statements are allowed
422 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 If you include an sql-section in your config-file like this:
428 sql => {
430 alive => 'select count(*) from dual',
431 statistic_area => 'select count(*) from e01admin.e01e203 where geb_bezei like ?',
432 }
433 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 my $sql = "alive";
440 my $cur = $dbh->prepare($sql);
441 ...
442 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 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 The result is this query:
455 select count(*) from e01admin.e01e203
457 where geb_bezei like '905%'
458 Don't try to put parameters into the sql-query like this:
460 # 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 Problems
470 · I don't know how to restrict users to special databases.
471 · I don't know how to pass query-parameters via dbish
473
475 Copyright (c) 1997 Jochen Wiedmann
476 Am Eisteich 9
477 72555 Metzingen
478 Germany
479 Email: joe@ispsoft.de
481 Phone: +49 7123 14881
482 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
489 dbiproxy, DBD::Proxy, DBI, RPC::PlServer, RPC::PlClient, Net::Daemon,
490 Net::Daemon::Log, Sys::Syslog, Win32::EventLog, syslog
491perl v5.10.1 2009-06-05 DBI::ProxyServer(3)