1

We run an Exadata XM-8 with multiple DBs for a variety of clients in our company. I want to limit access to one of the DBs to one particular server. I know I can run a logon trigger and limit the IP address that way but I have always been a little shy about logon triggers. I have read I can add a second port to the scan listener and restrict access via the following commands.

srvctl modify scan_listener -p “TCP:1521/TCP:1522″

Add an additional node listener using port 1523, for example: srvctl add listener -l LISTENER1523 -p 1523.

Start the new node listener: srvctl start listener -l LISTENER1523.

Double-check using: srvctl status listener -l LISTENER1523.

Double-check using: lsnrctl status LISTENER1523.

alter system set local_listener='(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST="scan address")(PORT=1523))))' scope=BOTH SID='OCRL1';
alter system set remote_listener='scan:1522' scope=BOTH SID='OCRL1';
alter system register;

Double-check using lsnrctl status LISTENER1523 that the instance has registered.

Then in the firewall on the Exadata I set the firewall to limit access to port 1522 to the server I want.

Which do ya'll think is better and why or what have ya'll done? .

1 Answer 1

0

In general, trying to restrict database access by IP address is a pointless exercise, as the address can be spoofed by the client to bypass any whitelist you put in place. Creating separate listener ports for different SIDs is also not recommended, as it just opens more ports and quickly becomes an administrative nightmare.

If you have Enterprise Edition (which Exadata would suggest), then consider using Oracle Connection Manager to limit access by IP address and service_name. It isn't foolproof, but is simpler to setup than what you have suggested. I wrote this a while ago for Oracle 11g, but the implementation really hasn't changed with newer versions.

Also consider using strong authentication with SSL, using wallets with certificates to identify a particular client and then limiting the wallet and client cert to the specific app server you want to allow. This would add one new port to your listener to support TCPS protocol connections, but could be leveraged for any/all databases on the server. When setup correctly, it would make it impossible to login from any client that didn't have the required certificate.

1
  • Thanks alot will look into OCM.
    – cptkirkh
    May 25, 2023 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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