Net8 Connection Manager
The requirement requires the Oracle connection and firewalls. A remote Oracle client making a connection to an Oracle database can fail if there is a firewall installed between the client and the server if port redirection is taking place. The firewall will block the connection to the new port when the Oracle client connects to the database ORA-12203 or ORA-12535 or ORA-12564. The client connection failure is due to port redirection from the Database Server‘s operating system. Port redirection requires the client to connect to the database using a different port than originally configured in the configuration files. Solution: Oracle Connection Manager (CMAN)
Oracle Connection Manager is available from Oracle Net8 and requires configuration of CMAN to allow the clients to connect through firewall. CMAN is executable that allows clients to connect despite a firewall being in place between the client and the server. CMAN is similar to the listener that it reads a CMAN.ORA configuration file, which contains an address that Oracle Connection Manager listens for in comming connections, usually default port of 1610 or 1630. The oracle client must be Net8 or above.
Sample Proxy Configuration
The following configuration is based on a working CMAN acting as a proxy to access a database on a firewalled segmented piece of network.
192.168.x.x -> 192.168.10.131 -| DMZ |-> 192.168.14.4 AnyHost -> Gamma (sql proxy) -|firewall|-> Beta (Database)
Files affected in the configuration are TNSNAMES.ORA (client), CMAN.ORA (connection manager) and LISTENER.ORA (remote site)
TNSNAMES.ORA
Resides on the client machines needing access to the firewalled database.
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora EXTPROC_CONNECTION_DATA.DBZOO.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) ## MUON - Via connection manager test.dbzoo.com = (DESCRIPTION= (SOURCE_ROUTE=yes) (ADDRESS= (PROTOCOL=tcp) (HOST=gamma) (PORT=1610)) (ADDRESS= (PROTOCOL=tcp) (HOST=beta) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=muon.dbzoo.com)))
CMAN.ORA
The CMAN.ORA file. There are 3 sections in the CMAN.ORA. file that need to configure, CMAN, CMAN_ADMIN & CMAN_PROFILE.
cman = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=gamma)(PORT=1610)(QUEUESIZE=32)) ) CMAN_ADMIN = (ADDRESS=(PROTOCOL=tcp)(HOST=gamma)(PORT=1830))
cman's configurable params
- MAXIMUM_RELAYS defaults to 128
- LOG_LEVEL defaults to 0
- TRACING defaults to no
- TRACE_DIRECTORY defaults to …/network/trace
- RELAY_STATISTICS defaults to no
- SHOW_TNS_INFO defaults to no
- USE_ASYNC_CALL defaults to yes
- AUTHENTICATION_LEVEL defaults to 0
- Note: If AUTHENTICATION_LEVEL=1 then use SQLNET.AUTHENTICATION_SERVICES= (NTS) in SQLNET.ORA
- MAXIMUM_CONNECT_DATA defaults to 1024
- ANSWER_TIMEOUT defaults to 0
- MAX_FREELIST_BUFFERS defaults to 2048
CMAN_PROFILE = (PARAMETER_LIST= (MAXIMUM_RELAYS=32) (LOG_LEVEL=1) (TRACING=no) (SHOW_TNS_INFO=YES) (RELAY_STATISTICS=NO) (AUTHENTICATION_LEVEL=0) )
Startup the Connection Manager services. The command to activate is CMCTL.
Type CMCTL START CMAN|ADM. And you should be able to check the services up and running using windows services.
LISTENER.ORA
The LISTENER.ORA in the SQL Proxy server must include a listening port for CMAN that is 1610.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = GAMMA)(PORT = 1521)) ) # FOR CMAN (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = GAMMA)(PORT = 1610)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oracle\ora92) (PROGRAM = extproc) ) )
Controlling the Connection manager
From CMD on the box with the connection manager software installed.
cmctl start - Will startup the admin and cm daemons cmctl start cm - Connection manager deamon only. cmctl start admin - Admin daemon only
Getting Oracle Instant Client to Work
Summary: Installing and configuring oracle instant client
Download the oracle instant client software
- unzip the instant client software into a directory say C:/oracle
- Setup the PATH environment variable to include this location.
- Setup sqlnet.ora and tnsnames.ora file for your servers
c:/oracle/network/admin/sqlnet.ora c:/oracle/network/admin/tnsnames.ora
- Setup TNS_ADMIN environment variable to c:/oracle/network/admin
How to setup environment variable ?
- Right mouse click on “My Computer” - it will be either on your desktop or alternatively it can be located in the left hand side of Windows Explorer
- Select Properties
- Select the Advanced Tab
- Pressed the “Environment Variables” button
- Press the “New” button for the user variables section
- Enter VARIABLE and the VALUE
You've now got an instance oracle client.
Table column list
How to get a CSV list of columns for a table?
/** Supplied with a table name return a comma separated list of its columns in ascending order. */ FUNCTION getColumnList (fromuser VARCHAR2, ptablename VARCHAR2) RETURN VARCHAR2 IS columnArray dbms_utility.uncl_array; csvLen BINARY_INTEGER; csvList VARCHAR2(4000); BEGIN SELECT column_name BULK COLLECT INTO columnArray FROM all_tab_cols WHERE table_name = ptablename AND owner = fromuser AND hidden_column = 'NO' ORDER BY column_name; dbms_utility.table_to_comma(columnArray, csvLen, csvList); RETURN csvList; END;
Blocking PROFTPD banned users permanently
PROFTPD is a great FTP server. However I found that once I had it running every man and his dog tried to brute force their way in.
The mod_ban modules helped stop brute force attacks. Check if the module is already compiled in.
# proftpd --list | fgrep mod_ban mod_ban.c #
If not you need to download the source and recompile it. Here is the configuration that I used to compile on my Centos 5 distribution.
./configure --build=i686-redhat-linux-gnu --host=i686-redhat-linux-gnu --target=i386-redh at-linux-gnu --program-prefix= --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbind ir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/u sr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir= /usr/share/man --infodir=/usr/share/info --libexecdir=/usr/libexec/proftpd --localstatedi r=/var/run --enable-ctrls --enable-dso --with-modules=mod_readme:mod_auth_pam:mod_tls:mod _ban:mod_shaper:mod_ifsession:mod_wrap
Edit the /etc/proftpd.conf and add some simple BAN rules to keep the script kiddies at bay.
<IfModule mod_ban.c> BanEngine on BanLog /var/log/proftpd/ban.log BanTable /var/log/proftpd/ban.tab BanControlsACLs all allow user root deny user Administrator BanMessage "Host %a has been banned" # If a client reached the max login attempt twice in 12 seconds ban them # Thats 6 failures in 12sec - thats a login attempt every 2sec ! BanOnEvent MaxLoginAttempts 2/00:00:12 99:99:99 # Configure a rule to automatically ban scripts looking for anonymous # servers to which they can upload. We don't do anonymous. BanOnEvent AnonRejectPasswords 1/00:05:00 99:99:99 # Ban clients which connect too frequently. This rule bans clients # which connect more than 5 times within one minute. Include a special # message just for them. #BanOnEvent ClientConnectRate 5/00:01:00 04:00:00 "Stop connecting frequently" </IfModule>
However some times after the ban has expired the same script kiddie comes back for another attempt. So this time we make sure they are permanently blocked from my network.
Place this code into /usr/local/bin/mod_ban_deny.py and chmod 755
#!/usr/bin/python # # Parse mod_ban LOGS and block permenatly those banned import re def parseIP(file): iplist = [] for line in open(file).readlines(): if len(line) == 0: continue x = re.search("\d+\.\d+\.\d+\.\d+", line) if x: ip=x.group(0) if not ip in iplist: iplist.append(ip) return iplist banlist = parseIP("/var/log/proftpd/ban.log") denylist = parseIP("/etc/hosts.deny") f = open("/etc/hosts.deny","a") for ip in banlist: if not ip in denylist: f.write("ALL: %s\n" % ip) f.close()
Setup a crontab to scan the ban log file and convert these entries into hosts.deny blocks.
@daily /usr/local/bin/mod_ban_deny.py
That should help you sleep at night. Its interesting to note how quickly the /etc/hosts.deny file fills up with people attempting to break into your FTP server. Now you are armed with a secret weapon their attempts are futile.
Over time your /etc/hosts.deny file will accumulate blocked IP addresses as extracted from the ban.log file.
ALL: 218.15.143.174 ALL: 124.114.130.149 ALL: 158.49.50.139 ALL: 218.62.29.118 ALL: 202.4.119.35
If you run the PROFTPD server in standalone mode then you will need some additional configuration options in the /etc/proftpd.conf configuration file.
<IfModule mod_wrap.c> TCPAccessFiles /etc/hosts.allow /etc/hosts.deny </IfModule>
See also: http://www.aczoom.com/cms/blockhosts