Restricting database access per IP addresses

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?

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

StackExchange Database Administrators Q#10264, answer score: 4