Restricting database access per IP addresses

Question

I have filtered IP addresses to an access DB server like this:

Set the sqlnet.ora

tcp.validnode_checking = YES
tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186)

But in my ORACLE_HOME, there are more than 1 Database, let say DB WORKSHOP and DB COURSE

I want to ask about connecting to database for each DB, example:

DB WORKSHOP only can be access from 192.168.100.130
DB COURSE only can be access from 192.168.100.186

What can I do to make it?

Answer 1

I think the only pure Oracle way to do this would be to run 2 separate listeners on different ports out of 2 different ORACLE_HOMEs, rather than just using the one.

A much more sane way of approaching this would be to use other security measures... Separate Usernames and passwords are quite common for this task - I guess you must be using LDAP or something that prevents this. Another option would be a login trigger on each database that checks the remote IP and disconnects the session if it's not come from the correct machine.

select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;

... will give you the remote IP address.

A trigger such as:

CREATE OR REPLACE TRIGGER DENY_LOGIN
AFTER LOGON ON DATABASE
DECLARE
  foo varchar2(128);
BEGIN

  IF ( sys_context('userenv','ip_address') <> '192.168.100.130' )
  THEN
    raise_application_error( -20001, 'Connection not authorised' );
  END IF;

EXCEPTION
WHEN OTHERS THEN
  RAISE;

END;

... will do the job for DB WORKSHOP.

(Untested, but should be OK)

Probably best to add a clause that allows DBAs to login from localhost too, or just exclude them completely :)

Answer 2

If different listeners on different ports is not an option then I would suggest investigating Oracle Connection Manager. Connection Manager serves many functions such as concentrating multiple connections and allowing multiple protocol support. It also can handle Network Access Control which sounds right up your alley. You can create CMAN_RULES which define rules for filtering incoming connection requests. They can include:

• Source hostname(s) or IP address(es) for clients
• Destination hostname(s) or IP address(es) for servers
• Destination database server identifier

More info on Connection Manager is available here: Connection Manager Documentation

I have used it to concentrate connections - we had a client that needed to access our database over a VPN. The firewall was only configured to open 1 port for all their connections. We were able to handle all their traffic over this 1 port using CMAN.