1 Reply Latest reply on Jun 13, 2019 8:31 PM by mariam.kupa

    How to create logon white list in Oracle?

    user964944

      I want to have a white list, who can connect to the database via some user and also log any logon action somewhere, how can I achieve that?

        • 1. Re: How to create logon white list in Oracle?
          mariam.kupa

          You can use logon trigger on desired schema, here is the whole script:

           

          1. Create table log:

           

          create table sys.lb_logons_log

          (username varchar2(100), host varchar2(200), program varchar2(200), logon_date date)

          tablespace log_data

          nologging;

           

          2. Create white list table:

           

          create table sys.lb_IPTABLES

          (

            ip          VARCHAR2(60),

            username    VARCHAR2(100),

            host        VARCHAR2(100),

            description VARCHAR2(150),

            insert_date DATE default sysdate

          )

          tablespace SYSAUX

            pctfree 10

            pctused 40

            initrans 1

            maxtrans 255

            storage

            (

              initial 64K

              next 1M

              minextents 1

              maxextents unlimited

            )

            nologging;

           

          3. Insert IP addresses that should be able to connect to the database via mentioned schema

           

          4. Create logon trigger on mentioned schema:

           

          CREATE OR REPLACE TRIGGER sys.lb_logon_trigger

          AFTER LOGON ON MYSCHEMANAME.schema

          DECLARE

             v_flag number;

          BEGIN

             insert into sys.lb_logons_log(username, host, program, logon_date)

             values(user, SYS_CONTEXT ('USERENV', 'HOST'), SYS_CONTEXT ('USERENV', 'MODULE'), sysdate);

             commit;

           

           

             select 1 into v_flag

             from dual

             where not exists(select 1 from sys.LB_IPTABLES where ip=SYS_CONTEXT('USERENV','IP_ADDRESS'));

           

           

          exception

              when NO_DATA_FOUND then

                  RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login to this database');

              when others then

                  null;

          END;

          /

          1 person found this helpful