1

I want to lock down access to specific schemas. Restrict the access to specific users when the connection is made from the specific IP addresses. Eg Only Access to USER1 and USER2

2
  • Oracle Version?
    – syam
    Commented Nov 13, 2017 at 19:06
  • I m using Oracle 11g Commented Nov 13, 2017 at 19:07

1 Answer 1

2

An AFTER LOGON trigger can be used for this purpose.

CREATE OR REPLACE TRIGGER system.check_ip_addresses 
AFTER LOGON 
ON DATABASE
BEGIN 
IF USER IN ('USER1', 'USER2') THEN
IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT IN 
('192.168.0.10','192.168.0.11') THEN
  RAISE_APPLICATION_ERROR(-20000, 'Can not log in from this IP address (' || 
l_ip_address || ')');
END IF;
END IF;
END; 
 /

Remember the following points.

  1. The trigger must use ON DATABASE, as making it schema specific would prevent the RAISE_APPLICATION_ERROR from preventing the logon.
  2. The trigger will fire for all connection attempts, so make sure you restrict which users it affects carefully to reduce the overhead.
3
  • Double for point 2; if you don't limit this to specific users, you could lock yourself out of the server. (Don't put the built in SYSTEM and SYS accounts in this trigger, in other words...)
    – CaM
    Commented Nov 13, 2017 at 19:13
  • Cam, That's a valuable point. Have to take extra care while doing this.
    – syam
    Commented Nov 13, 2017 at 19:18
  • 1
    That does not answer the question. It does not restrict access to specific schemas for these users. It simply does not allow these users to connect, That is a complete different thing.
    – miracle173
    Commented Nov 14, 2017 at 6:33

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.