Forum Stats

  • 3,741,452 Users
  • 2,248,430 Discussions
  • 7,861,817 Comments

Discussions

How to create logon white list in Oracle?

user964944
user964944 Member Posts: 9
edited Jun 13, 2019 4:43PM in Database Security - General

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?

Tagged:
user964944

Best Answer

  • mariam.kupa
    mariam.kupa Member Posts: 240 Bronze Badge
    edited Jun 13, 2019 4:31PM Accepted Answer

    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_datanologging;

    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_triggerAFTER LOGON ON MYSCHEMANAME.schemaDECLARE   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;/
    user964944user964944

Answers

  • mariam.kupa
    mariam.kupa Member Posts: 240 Bronze Badge
    edited Jun 13, 2019 4:31PM Accepted Answer

    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_datanologging;

    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_triggerAFTER LOGON ON MYSCHEMANAME.schemaDECLARE   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;/
    user964944user964944
Sign In or Register to comment.