How to Restrict User from Connecting to Database Through Specific IP Address

Some of the DBA asked me to restrict the connection to DB from specific IPs. Its simple and you can use the logon trigger for this purpose.

CREATE OR REPLACE TRIGGER logontrig
   AFTER LOGON ON DATABASE
BEGIN
   IF ora_client_ip_address = '172.16.108.48'
   THEN
      raise_application_error
                         (-20001,
                          'You are not authorized to login into this machine'
                         );
   END IF;
END;

Note: This will only work for non-dba users that do not have the privilege ADMINISTER DATABASE TRIGGER, otherwise the trigger will only cause a message to be written to the alert.log file.


Other Examples:
An event trigger that checks if the connection occurs between Monday and Friday ,
and within working hours: 8AM to 6PM.

CREATE OR REPLACE TRIGGER logon_trg
   AFTER LOGON ON DATABASE
BEGIN
   IF    (TO_CHAR (SYSDATE, 'D') NOT BETWEEN '2' AND '6')
      OR (TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')
   THEN
      raise_application_error
                             (-20001,
                              'You are not allowed to log into database now.'
                             );
   END IF;
END;

To restrict the logon periods for a users so that they can only access the database betrween the periods to 17:00 - 24:00 daily.

CREATE OR REPLACE TRIGGER scottlogintrigger
   AFTER LOGON ON scott.SCHEMA
DECLARE
   temp     VARCHAR2 (50);
   v_time   VARCHAR2 (50);
BEGIN
   temp := 'select to_char(sysdate,''HH24:MI'') from dual';

   EXECUTE IMMEDIATE temp
                INTO v_time;

   IF (TO_DATE (v_time, 'HH24:MI') < TO_DATE ('17:00', 'HH24:MI'))
   THEN
      raise_application_error
              (-20001,
                  'SCOTT access is denied until 17:00. The current time is '
               || v_time,
               TRUE
              );
   END IF;

   IF (TO_DATE (v_time, 'HH24:MI') > TO_DATE ('23:59', 'HH24:MI'))
   THEN
      raise_application_error
         (-20001,
             'SCOTT access is denied because the time is past 23:59. The current time is '
          || v_time,
          TRUE
         );
   END IF;
END;