Martins Blog

Trying to explain complex things in simple terms

Using Connection Manager to protect a database

Posted by Martin Bach on July 11, 2011

I have known about Oracle’s connection manager (CMAN) for quite a while but never managed to use it in anger. In short there was no need to do so. Now however I have been asked to help in finding a solution to an interesting problem.

A long story short is that access to databases has to be governed via a mechanism that worked well with firewalls and allowed to regulate access to databases based on rule sets. CMAN can do that, and a few other things that are out of scope of this article.

The architecture I use in this post includes a number of hosts. Simulating end users I created a VM named client, and it’s on the 192.168.99.0/24 network. The “router” is named CMAN in a fit of creativity, and it connects the 192.168.99.0/24 network to the database network on 192.168.100.0/24 network. The database server to which access is regulated via CMAN is named CMANDB. Crucially, there is no routing enabled on the CMAN host. In other words, there is no direct connection possible from the client to the database server. Additional protection of the database host could have been achieved by using firewall rules, but that was out of scope of this post.

Overview of Connection Manager 11.2

For those of you who aren’t familiar with CMAN, here’s a short summary (based on the official Oracle documentation).

Configuration of Oracle Connection Manager (CMAN) allows the clients to connect through a firewall [I haven’t verified this yet, ed]. CMAN is an executable that allows clients to connect despite a firewall being in place between the client and server. CMAN is similar to the Listener in that it reads a configuration file [called CMAN.ora, ed], which contains an address that Oracle Connection Manager listens for incoming connections. CMAN starts similar to the Listener and will enter a LISTEN state.

This solution [to the firewall issue with TCP redirects, ed] will make the REDIRECT happen inside the firewall and the client will not see it; CMAN comports as a proxy service between the client and the real database listener.

Interestingly, Connection Manager is fully integrated into the FAN/FCF framework and equally suitable for UCP connection pools.

Installing Connection Manager

Connection Manager is part of the Oracle client, and you can install it by choosing the “custom” option. From the list of selectable options, pick “Oracle Net Listener” and “Oracle Connection Manager” plus any others you might need.

From there on it’s exactly the same as any other client installation. You should probably consider applying the latest PSU to the client installation.

Testing

A quick test with 2 separate networks reveals how the concept actually works. As I said in the introductoin the following hosts are used:

  • cman: 192.168.99.224
  • cmandb: 192.168.100.225
  • client: 192.168.99.31

The networks in use are:

  • Public network: 192.168.99.0/24
  • Private network: 192.168.100.0/24

Crucially, CMANDB is on a different network than the other hosts with cman acting as the arbiter on 192.68.99.224.

As indicated, connection manager has been installed on host “cman”, with IP 192.168.99.224 and listens on port 1521. The corresponding cman.ora file has been configured as follows in $CLIENT_HOME/network/admin:

cman1 =
  (configuration=
    (address=
      (protocol=tcp)(host=192.168.99.224)(port=1521)
    )
    (rule_list=
      (rule=(src=192.168.99.224)(dst=127.0.0.1)(srv=cmon)(act=accept))
      (rule=(src=192.168.99.0/24)(dst=192.168.100.225)(srv=*)(act=accept))
    )
  )

The file has been left in this minimalistic state deliberately, please consult the documentation for options available. The CMAN listener – not to be confused with the net8 listener – listens on port 1521.

There are two minimalistic rules:

  • Rule number one is necessary to allow management access to Connection Manager. Without it, CMAN will not start.
  • Rule number two actually governs access to the database hosts on CMANDB

There might be another one needed if IPv6 is in use on the host. Should CMAN not start and complain about TNS-04014 and TNS-12529 check MOS 1059938.1 for a solution.

The gateway host has 2 network interfaces, one for each network:

[root@cman ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:16:3E:F2:34:56
          inet addr:192.168.99.224  Bcast:192.168.99.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1209 errors:0 dropped:0 overruns:0 frame:0
          TX packets:854 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:105895 (103.4 KiB)  TX bytes:147462 (144.0 KiB)
          Interrupt:17

eth1      Link encap:Ethernet  HWaddr 00:16:3E:52:4A:56
          inet addr:192.168.100.224  Bcast:192.168.100.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:334 errors:0 dropped:0 overruns:0 frame:0
          TX packets:151 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:36425 (35.5 KiB)  TX bytes:22141 (21.6 KiB)
          Interrupt:16

Configuration on host “CMANDB”

Note that host 192.168.100.225 is on the private network! The database CMANDB has its local and remote listener configured using the below entries in tnsnames.ora:

[oracle@cmandb admin]$ cat tnsnames.ora
LOCAL_CMANDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.100.225))
    )
  )

REMOTE_CMANDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.100.224))
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.100.225))
    )
  )

CMAN_LSNR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = 192.168.99.224))
    )
  )

Translated into human language, this means:

  • local listener is set to local_cmandb
  • remote listener is set to remote_cmandb
  • CMAN_LSNR is used for a test to verify that a connection to the CMAN listener is possible from this host

It should be pointed out at this stage that this applies to single instance databases. If I find the time I’ll write a post about CMAN and RAC later.

The host had only one network interface, allowing connections to the CMAN host:

[root@cmandb ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:16:3E:12:14:51
          inet addr:192.168.100.225  Bcast:192.168.100.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:627422 errors:0 dropped:0 overruns:0 frame:0
          TX packets:456584 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2241758430 (2.0 GiB)  TX bytes:32751153 (31.2 MiB)

After the database listener parameters have been changed to LOCAL_CMANDB and REMOTE_CMANDB, the alert log on the CMAN host recorded a number of service registrations. This is important as it allows Connection Manager to hand the connection request off to the database:

[oracle@cman trace]$ grep cmandb *
08-JUL-2011 10:19:55 * service_register * cmandb * 0
08-JUL-2011 10:25:28 * service_update * cmandb * 0
08-JUL-2011 10:35:28 * service_update * cmandb * 0
[...]
08-JUL-2011 11:15:10 * service_update * cmandb * 0
08-JUL-2011 11:15:28 * service_update * cmandb * 0
[oracle@cman trace]$

Additionally, the CMAN processes now know about the database service CMANDB:

CMCTL:cman1> show services
Services Summary...
Proxy service "cmgw" has 1 instance(s).
  Instance "cman", status READY, has 2 handler(s) for this service...
    Handler(s):
      "cmgw001" established:0 refused:0 current:0 max:256 state:ready
        <machine: 127.0.0.1, pid: 2298>
        (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=23589))
      "cmgw000" established:0 refused:0 current:0 max:256 state:ready
        <machine: 127.0.0.1, pid: 2294>
        (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=31911))
Service "cmandb" has 1 instance(s).
  Instance "cmandb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
      REMOTE SERVER
        (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=192.168.100.225)))
Service "cmandbXDB" has 1 instance(s).
  Instance "cmandb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
      DISPATCHER <machine: cmandb.localdomain, pid: 7167>
      (ADDRESS=(PROTOCOL=tcp)(HOST=cmandb.localdomain)(PORT=50347))
Service "cmon" has 1 instance(s).
  Instance "cman", status READY, has 1 handler(s) for this service...
    Handler(s):
      "cmon" established:1 refused:0 current:1 max:4 state:ready
      <machine: 127.0.0.1, pid: 2282>
     (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59541))
The command completed successfully.
CMCTL:cman1>

The previous output has been obtained from cmctl, the CMAN control utility.

Connectivity Test

With the setup completed it was time to perform a test from a third host on the (public) network. Its IP address is 192.168.99.31. The below TNSnames entries were created:

[oracle@client admin]$ cat tnsnames.ora
CMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PORT=1521)(HOST=192.168.99.224)(PROTOCOL=TCP))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cmandb)
      (SERVER = DEDICATED)
    )
  )

DIRECT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PORT=1521)(HOST=192.168.100.225)(PROTOCOL=TCP))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cmandb)
      (SERVER = DEDICATED)
    )
  )

The CMAN entry uses the Connection Manager gateway host to connect to database CMANDB, whereas the DIRECT entry tries to bypass the latter. A tnsping should show whether or not this is possible. Before any of the testing can take place it is necessary to start CMAN using “cmctl startup -c cman1”.

[oracle@client admin]$ tnsping direct
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JUL-2011 11:19:47

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PORT=1521)(HOST=192.168.100.225)(PROTOCOL=TCP))) (CONNECT_DATA = (SERVICE_NAME = cmandb) (SERVER = DEDICATED)))

TNS-12543: TNS:destination host unreachable

[oracle@client admin]$ tnsping cman

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JUL-2011 10:53:27

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PORT=1521)(HOST=192.168.99.224)(PROTOCOL=TCP))) (CONNECT_DATA = (SERVICE_NAME = cmandb) (SERVER = DEDICATED)))

OK (0 msec)

[oracle@client admin]$

Now that proves that a direct connection is impossible, and also that the connection manager’s listener is working. A tnsping doesn’t imply that a connection is possible though, this requires an end to end test with SQL*Plus:

[oracle@client admin]$ sqlplus system/xxx@cman

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 8 10:55:39 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
cmandb
cmandb.localdomain

The successful connection is also recorded in the CMAN log file:

Fri Jul 08 10:55:39 2011

08-JUL-2011 10:55:39 * (CONNECT_DATA=(SERVICE_NAME=cmandb)(SERVER=DEDICATED)(CID=(PROGRAM=sqlplus@client)(HOST=client)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.99.31)(PORT=16794)) * establish * cmandb * 0

(LOG_RECORD=(TIMESTAMP=08-JUL-2011 10:55:39)(EVENT=Ready)(CONN NO=0))

My idea to limit access to the database via rules seems to have been working.

3 Responses to “Using Connection Manager to protect a database”

  1. Martin – when you say “but because of the way Oracle connections work this proved impossible” what do you mean? You mean because a listener accepts for any service/SID connection request from any source network? Isn’t there some way of restricting that in config (or am I getting confused)? Alternatively if you have a db host running, say, both DR and UAT instances, you could have two different listeners (e.g. live and test) and then simply restrict access by the firewall.

    Just a thought,

    Simon

  2. We were connecting to the remote databases through CMAN (we were using it as proxy) in my previous company. To increase the security, there were also VPN tunnels between our company network and the remote networks.

    As I remember, unpatched 10g version on windows, had some memory leak problems, and we were restarting its service periodically under heavy loads, but 11g version was working stable.

    CMAN also supports SSL connections (although we haven’t used/tested that feature).

    Regards

    Gokhan

  3. […] Martin Bach solves an interesting problem, i.e. Using Connection Manager to protect a database. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 3,306 other followers

%d bloggers like this: