Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 6 '12 at 15:16

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

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 :)

share|improve this answer
    
+1 thanks for the useful answer Phil - and welcome to dba.se :) –  Jack Douglas Jan 6 '12 at 15:32
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.