dbi::proxyserver.3

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

\&          \*(Aqsql\*(Aq => {
\&               \*(Aqselect\*(Aq => \*(AqSELECT * FROM foo\*(Aq,
\&               \*(Aqinsert\*(Aq => \*(AqINSERT INTO foo VALUES (?, ?, ?)\*(Aq
\&               }
\&        }
.Ve
.PP
then only the users root and wwwrun may connect from admin.company.com,
executing arbitrary queries, but only wwwrun may connect from other
hosts and is restricted to
.PP
.Vb 1
\&    $sth\->prepare("select");
.Ve
.PP
or
.PP
.Vb 1
\&    $sth\->prepare("insert");
.Ve
.PP
which in fact are \*(L"\s-1SELECT\s0 * \s-1FROM\s0 foo\*(R" or \*(L"\s-1INSERT\s0 \s-1INTO\s0 foo \s-1VALUES\s0 (?, ?, ?)\*(R".
.SH "Proxyserver Configuration file (bigger example)"
.IX Header "Proxyserver Configuration file (bigger example)"
This section tells you how to restrict a DBI-Proxy: Not every user from
every workstation shall be able to execute every query.
.PP
There is a perl program \*(L"dbiproxy\*(R" which runs on a machine which is able
to connect to all the databases we wish to reach. All Perl-DBD-drivers must
be installed on this machine. You can also reach databases for which drivers 
are not available on the machine where you run the programm querying the 
database, e.g. ask MS-Access-database from Linux.
.PP
Create a configuration file \*(L"proxy_oracle.cfg\*(R" at the dbproxy-server:
.PP
.Vb 8
\&    {
\&        # This shall run in a shell or a DOS\-window 
\&        # facility => \*(Aqdaemon\*(Aq,
\&        pidfile => \*(Aqyour_dbiproxy.pid\*(Aq,
\&        logfile => 1,
\&        debug => 0,
\&        mode => \*(Aqsingle\*(Aq,
\&        localport => \*(Aq12400\*(Aq,
\&
\&        # Access control, the first match in this list wins!
\&        # So the order is important
\&        clients => [
\&                # hint to organize:
\&                # the most specialized rules for single machines/users are 1st
\&                # then the denying rules
\&                # the the rules about whole networks
\&
\&                # rule: internal_webserver
\&                # desc: to get statistical information
\&                {
\&                        # this IP\-address only is meant
\&                        mask => \*(Aq^10\e.95\e.81\e.243$\*(Aq,
\&                        # accept (not defer) connections like this
\&                        accept => 1,
\&                        # only users from this list 
\&                        # are allowed to log on
\&                        users => [ \*(Aqinformationdesk\*(Aq ],
\&                        # only this statistical query is allowed
\&                        # to get results for a web\-query
\&                        sql => {
\&                                alive => \*(Aqselect count(*) from dual\*(Aq,
\&                                statistic_area => \*(Aqselect count(*) from e01admin.e01e203 where geb_bezei like ?\*(Aq,
\&                        }
\&                },
\&
\&                # rule: internal_bad_guy_1
\&                {
\&                        mask => \*(Aq^10\e.95\e.81\e.1$\*(Aq,
\&                        accept => 0,
\&                },
\&
\&                # rule: employee_workplace
\&                # desc: get detailled informations
\&                {
\&                        # any IP\-address is meant here
\&                        mask => \*(Aq^10\e.95\e.81\e.(\ed+)$\*(Aq,
\&                        # accept (not defer) connections like this
\&                        accept => 1,
\&                        # only users from this list 
\&                        # are allowed to log on
\&                        users => [ \*(Aqinformationdesk\*(Aq, \*(Aqlippmann\*(Aq ],
\&                        # all these queries are allowed:
\&                        sql => {
\&                                search_city => \*(Aqselect ort_nr, plz, ort from e01admin.e01e200 where plz like ?\*(Aq,
\&                                search_area => \*(Aqselect gebiettyp, geb_bezei from e01admin.e01e203 where geb_bezei like ? or geb_bezei like ?\*(Aq,
\&                        }
\&                },
\&
\&                # rule: internal_bad_guy_2 
\&                # This does NOT work, because rule "employee_workplace" hits
\&                # with its ip\-address\-mask of the whole network
\&                {
\&                        # don\*(Aqt accept connection from this ip\-address
\&                        mask => \*(Aq^10\e.95\e.81\e.5$\*(Aq,
\&                        accept => 0,
\&                }
\&        ]
\&    }
.Ve
.PP
Start the proxyserver like this:
.PP
.Vb 3
\&        rem well\-set Oracle_home needed for Oracle
\&        set ORACLE_HOME=d:\eoracle\eora81
\&        dbiproxy \-\-configfile proxy_oracle.cfg
.Ve
.Sh "Testing the connection from a remote machine"
.IX Subsection "Testing the connection from a remote machine"
Call a programm \*(L"dbish\*(R" from your commandline. I take the machine from rule \*(L"internal_webserver\*(R"
.PP
.Vb 1
\&        dbish "dbi:Proxy:hostname=oracle.zdf;port=12400;dsn=dbi:Oracle:e01" informationdesk xxx
.Ve
.PP
There will be a shell-prompt:
.PP
.Vb 1
\&        informationdesk@dbi...> alive
\&
\&        Current statement buffer (enter \*(Aq/\*(Aq...):
\&        alive
\&
\&        informationdesk@dbi...> /
\&        COUNT(*)
\&        \*(Aq1\*(Aq
\&        [1 rows of 1 fields returned]
.Ve
.Sh "Testing the connection with a perl-script"
.IX Subsection "Testing the connection with a perl-script"
Create a perl-script like this:
.PP
.Vb 2
\&        # file: oratest.pl
\&        # call me like this: perl oratest.pl user password
\&
\&        use strict;
\&        use DBI;
\&
\&        my $user = shift || die "Usage: $0 user password";
\&        my $pass = shift || die "Usage: $0 user password";
\&        my $config = {
\&                dsn_at_proxy => "dbi:Oracle:e01",
\&                proxy => "hostname=oechsle.zdf;port=12400",
\&        };
\&        my $dsn = sprintf "dbi:Proxy:%s;dsn=%s",
\&                $config\->{proxy},
\&                $config\->{dsn_at_proxy};
\&
\&        my $dbh = DBI\->connect( $dsn, $user, $pass )
\&                || die "connect did not work: $DBI::errstr";
\&
\&        my $sql = "search_city";
\&        printf "%s\en%s\en%s\en", "="x40, $sql, "="x40;
\&        my $cur = $dbh\->prepare($sql);
\&        $cur\->bind_param(1,\*(Aq905%\*(Aq);
\&        &show_result ($cur);
\&
\&        my $sql = "search_area";
\&        printf "%s\en%s\en%s\en", "="x40, $sql, "="x40;
\&        my $cur = $dbh\->prepare($sql);
\&        $cur\->bind_param(1,\*(AqPfarr%\*(Aq);
\&        $cur\->bind_param(2,\*(AqBronnamberg%\*(Aq);
\&        &show_result ($cur);
\&
\&        my $sql = "statistic_area";
\&        printf "%s\en%s\en%s\en", "="x40, $sql, "="x40;
\&        my $cur = $dbh\->prepare($sql);
\&        $cur\->bind_param(1,\*(AqPfarr%\*(Aq);
\&        &show_result ($cur);
\&
\&        $dbh\->disconnect;
\&        exit;
\&
\&
\&        sub show_result {
\&                my $cur = shift;
\&                unless ($cur\->execute()) {
\&                        print "Could not execute\en"; 
\&                        return; 
\&                }
\&
\&                my $rownum = 0;
\&                while (my @row = $cur\->fetchrow_array()) {
\&                        printf "Row is: %s\en", join(", ",@row);
\&                        if ($rownum++ > 5) {
\&                                print "... and so on\en";
\&                                last;
\&                        }       
\&                }
\&                $cur\->finish;
\&        }
.Ve
.PP
The result
.PP
.Vb 10
\&        C:\e>perl oratest.pl informationdesk xxx
\&        ========================================
\&        search_city
\&        ========================================
\&        Row is: 3322, 9050, Chemnitz
\&        Row is: 3678, 9051, Chemnitz
\&        Row is: 10447, 9051, Chemnitz
\&        Row is: 12128, 9051, Chemnitz
\&        Row is: 10954, 90513, Zirndorf
\&        Row is: 5808, 90513, Zirndorf
\&        Row is: 5715, 90513, Zirndorf
\&        ... and so on
\&        ========================================
\&        search_area
\&        ========================================
\&        Row is: 101, Bronnamberg
\&        Row is: 400, Pfarramt Zirndorf
\&        Row is: 400, Pfarramt Rosstal
\&        Row is: 400, Pfarramt Oberasbach
\&        Row is: 401, Pfarramt Zirndorf
\&        Row is: 401, Pfarramt Rosstal
\&        ========================================
\&        statistic_area
\&        ========================================
\&        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.
\&        Could not execute
.Ve
.Sh "How the configuration works"
.IX Subsection "How the configuration works"
The most important section to control access to your dbi-proxy is \*(L"client=>\*(R"
in the file \*(L"proxy_oracle.cfg\*(R":
.PP
Controlling which person at which machine is allowed to access
.IP "\(bu" 4
\&\*(L"mask\*(R" is a perl regular expression against the plain ip-address of the machine which wishes to connect _or_ the reverse-lookup from a nameserver.
.IP "\(bu" 4
\&\*(L"accept\*(R" tells the dbiproxy-server wether ip-adresse like in \*(L"mask\*(R" are allowed to connect or not (0/1)
.IP "\(bu" 4
\&\*(L"users\*(R" is a reference to a list of usernames which must be matched, this is \s-1NOT\s0 a regular expression.
.PP
Controlling which SQL-statements are allowed
.PP
You can put every SQL-statement you like in simply ommiting \*(L"sql => ...\*(R", but the more important thing is to restrict the connection so that only allowed queries are possible.
.PP
If you include an sql-section in your config-file like this:
.PP
.Vb 4
\&        sql => {
\&                alive => \*(Aqselect count(*) from dual\*(Aq,
\&                statistic_area => \*(Aqselect count(*) from e01admin.e01e203 where geb_bezei like ?\*(Aq,
\&        }
.Ve
.PP
The user is allowed to put two queries against the dbi-proxy. The queries are _not_ \*(L"select count(*)...\*(R", the queries are \*(L"alive\*(R" and \*(L"statistic_area\*(R"! These keywords are replaced by the real query. So you can run a query for \*(L"alive\*(R":
.PP
.Vb 3
\&        my $sql = "alive";
\&        my $cur = $dbh\->prepare($sql);
\&        ...
.Ve
.PP
The flexibility is that you can put parameters in the where-part of the query so the query are not static. Simply replace a value in the where-part of the query through a question mark and bind it as a parameter to the query.
.PP
.Vb 5
\&        my $sql = "statistic_area";
\&        my $cur = $dbh\->prepare($sql);
\&        $cur\->bind_param(1,\*(Aq905%\*(Aq);
\&        # A second parameter would be called like this:
\&        # $cur\->bind_param(2,\*(Aq98%\*(Aq);
.Ve
.PP
The result is this query:
.PP
.Vb 2
\&        select count(*) from e01admin.e01e203 
\&        where geb_bezei like \*(Aq905%\*(Aq
.Ve
.PP
Don't try to put parameters into the sql-query like this:
.PP
.Vb 7
\&        # Does not work like you think.
\&        # Only the first word of the query is parsed,
\&        # so it\*(Aqs changed to "statistic_area", the rest is omitted.
\&        # You _have_ to work with $cur\->bind_param.
\&        my $sql = "statistic_area 905%";
\&        my $cur = $dbh\->prepare($sql);
\&        ...
.Ve
.Sh "Problems"
.IX Subsection "Problems"
.IP "\(bu" 4
I don't know how to restrict users to special databases.
.IP "\(bu" 4
I don't know how to pass query-parameters via dbish
.SH "AUTHOR"
.IX Header "AUTHOR"
.Vb 4
\&    Copyright (c) 1997    Jochen Wiedmann
\&                          Am Eisteich 9
\&                          72555 Metzingen
\&                          Germany
\&
\&                          Email: [e-mail]
\&                          Phone: [phone]
.Ve
.PP
The DBI::ProxyServer module is free software; you can redistribute it
and/or modify it under the same terms as Perl itself. In particular
permission is granted to Tim Bunce for distributing this as a part of
the \s-1DBI\s0.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
dbiproxy, DBD::Proxy, \s-1DBI\s0, RPC::PlServer,
RPC::PlClient, Net::Daemon, Net::Daemon::Log,
Sys::Syslog, Win32::EventLog, syslog