Home » RDBMS Server » Networking and Gateways » Oracle Net proxy (DB12.1.0.2)
Oracle Net proxy [message #659139] Sat, 07 January 2017 06:01 Go to next message
John Watson
Messages: 7187
Registered: January 2010
Location: Global Village
Senior Member
I'm looking for a way to proxy, or forward, SQL*Net traffic. The situation is that a user on machine A is running an Oracle Net client (SQL*Plus, or TOAD, for example) to connect to a database on machine B. But we can't permit direct connection: the database server must not be exposed to the world. Usually I would run a Connection Manager on machine C, so the Oracle Net connection is A -> C -> B. But this is a Standard Edition environment, so I can't use a Connection Manager. I've been trying to find some way of forwarding the traffic, but I can't. Any ideas? I suspect not - in which case a definitive "you can't do this" would help.

I could set up machine C to accept ssh and VNC from machine A, and then the user(s) can run their client software on C. But that is going to make things pretty awkward for them.

Thank you for any insight.

--update: corrected typo, I had a B for a C.

[Updated on: Sat, 07 January 2017 06:03]

Report message to a moderator

Re: Oracle Net proxy [message #659148 is a reply to message #659139] Sat, 07 January 2017 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes it is possible, here a small perl script you can use as a starter to make it.
This script listen on port 1234 and forward the request to port and server given in its parameters and returns the replies to the original requester.
As I said, it is a starter as it handles only one client and the target server and port (database listener) is fixed when it is launched and it ends when the client disconnects.
So on client A, the tnsnames.ora will contain C and 1234 for host and port.
The script is launched on C with: "perl ora_proxy.pl <listener port> B".

ora_proxy.pl

use strict;
use warnings;

use IO::Handle;
use IO::Select;
use IO::Socket;
use Sys::Hostname;

# Listen on port 1234
# Forward requests to host <2nd parameter> (default current host), port <1st parameter>
my $p = shift;
my $s = shift || hostname();
print "Forward to server: $s, port: $p\n";

# Set proxy parameters
my %proxy;
$proxy{server_host} = $s;
$proxy{server_port} = $p;
$proxy{proxy_port}  = 1234;
$proxy{proxy} = IO::Socket::INET->new (
   LocalPort => $proxy{proxy_port},
   Type      => SOCK_STREAM,
   Reuse     => 1,
   Listen    => 10);
binmode $proxy{proxy};

# Now ready to listen and forward the session
print "Ready!\n";
# Wait for a connection from SQL*Plus
doit();
print "Goodbye!\n";
exit;

sub doit {
  my $rc;

  my $client = $proxy{proxy}->accept() || die "Unable to reach client:\n$!";
  binmode $client;

  my $server = IO::Socket::INET->new (
           PeerAddr => $proxy{server_host},
           PeerPort => $proxy{server_port});
  if ( $server ) { binmode $server; }
  else { $rc = $!; }

  $client->blocking(0);
  $server->blocking(0) if $server;

  my $select = new IO::Select;
  $select->add($server);
  $select->add($client);

  autoflush $server if $server;
  autoflush $client;

  while ( my @ready = $select->can_read() ) {
    foreach my $fd (@ready) {
      my $buf="";
      if ( $fd == $client ) {
        sysread ($client, $buf, 1024) || return;
        if ( $server ) { print $server $buf; }
        else { print "Unable to reach server:\n$rc\n"; exit 0; }
      }
      if ( $fd == $server ) {
        sysread ($server, $buf, 1024) || return;
        print $client $buf;
      }
    }
  }
}
[Edit: fix B and C to match same in question]
[Edit 2: slight modification in a printed message]
[Edit 3: post new code which no more relies on any custom package]

[Updated on: Thu, 06 April 2017 03:26]

Report message to a moderator

Re: Oracle Net proxy [message #659158 is a reply to message #659148] Sun, 08 January 2017 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I made several tests on different configurations, always 11.2.0.4 Oracle version, and here's the result:
client      -> proxy       -> database
XP 32-bit   -> XP 32-bit   -> XP 32-bit      ok
XP 32-bit   -> XP 32-bit   -> Win7 64-bit    KO
XP 32-bit   -> Win7 64-bit -> XP 32-bit      ok
XP 32-bit   -> Win7 64-bit -> Win7 64-bit    ok
Win7 64-bit -> XP 32-bit   -> XP 32-bit      ok
Win7 64-bit -> XP 32-bit   -> Win7 64-bit    KO
Win7 64-bit -> Win7 64-bit -> XP 32-bit      ok
Win7 64-bit -> Win7 64-bit -> Win7 64-bit    ok
So when the proxy is on XP 32-bit and the database on Win7 64-bit it does not work, I don't know, and didn't investigate, why (error on client is "ORA-12537: TNS:connection closed", error on proxy is time-out).

[Updated on: Sun, 08 January 2017 03:19]

Report message to a moderator

Re: Oracle Net proxy [message #659159 is a reply to message #659158] Sun, 08 January 2017 03:25 Go to previous messageGo to next message
John Watson
Messages: 7187
Registered: January 2010
Location: Global Village
Senior Member
Thank you for this, I'm trying to suss out whether it is a usable solution. My 100% ignorance of Perl doesn't help. The usage would likely be Windows -> Linux -> Linux. If we get something functional from it, I'll tell my boss that we owe you. The alternative is to use VPN and give the client direct access to the database. That introduces another set of problems, but is at least a standard solution.

I was really pleased when Uncle Oracle de-licensed SQL*Net encryption. Pity he didn't de-license Connection Manager at the same time.
Re: Oracle Net proxy [message #659160 is a reply to message #659159] Sun, 08 January 2017 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe because it thought that with TCP.VALIDNODE_CHECKING, TCP.EXCLUDED_NODES and TCP.INVITED_NODES in sqlnet.ora it fulfills the basic usages as it allows to accept/forbid accesses to the database (but does not forbid accesses to the database server) and wants to promote its (paying) Database Firewall product and discourage the usage of Connection Manager.

The Perl script should work in an environment with same architecture in proxy and database servers. The problem I had is when I "downgrade" from 64-bit proxy to 32-bit database, maybe something in the TCP or SQL*Net packets.
Using a Perl script is very easy, I think you have a Perl installed on your Linux as it comes with almost all Linux/Unix OS (try "whereis perl") otherwise you can find it at http://ftp.perl.org/pub/ and the installation can be done with the command:
# ./configure.gnu && make && make test && make install

I will write a connection manager-like program but not in the next weeks as I am currently really busy.

Re: Oracle Net proxy [message #659352 is a reply to message #659160] Fri, 13 January 2017 06:17 Go to previous messageGo to next message
John Watson
Messages: 7187
Registered: January 2010
Location: Global Village
Senior Member
Well, it works! With SQL*Plus 12.1.0.2 on Windows 10 -> the proxy on OEL 6.6 -> DB 12.1.0.2 on OEL 6.6.

Thank you. I don't know yet if we'll use it, but it is nice to have it as an option.
Re: Oracle Net proxy [message #659372 is a reply to message #659352] Fri, 13 January 2017 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As it, it works with one proxy per session which ends when the session completes.
So you must have only one proxy at a time (as all listen at the same port) and start a new proxy when a session is established to handle the next one.
As I said, it's just a starter.

Re: Oracle Net proxy [message #659373 is a reply to message #659372] Fri, 13 January 2017 11:07 Go to previous message
John Watson
Messages: 7187
Registered: January 2010
Location: Global Village
Senior Member
I was thinking of assigning a range of ports in /etc/services launching it through inetd. It's a damn good start.
Previous Topic: Generic Connectivity Not Working (Oracle -Ms Access)
Next Topic: ORA-12560: TNS:protocol adapter error
Goto Forum:
  


Current Time: Sun Dec 17 06:26:39 CST 2017

Total time taken to generate the page: 0.13450 seconds