oracle审计客户端的登陆ip,利用oracle审计，实现只允许指定的IP、应用、用户连接...

需要使用的数据表

数据库用户登录校验-用户

CREATE TABLE SYS.DB_LOGO_RULE_USER (

USER_ID NUMBER(18,0),

USER_NAME VARCHAR2(30 BYTE),

OPERATETIME DATE DEFAULT SYSDATE NOT NULL ENABLE,

CHECK_LEVEL NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE

);

COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.USER_ID IS '用户id';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.USER_NAME IS '用户名称';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.OPERATETIME IS '操作时间';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.CHECK_LEVEL IS '校验级别，0：不校验；1：ip校验；2：终端校验';

COMMENT ON TABLE SYS.DB_LOGO_RULE_USER IS '数据库用户登录校验-用户，有问题请联系Kindey.S，微信kindey123;

CREATE UNIQUE INDEX SYS.DB_LOGO_RULE_USER_ID ON SYS.DB_LOGO_RULE_USER (USER_ID);

CREATE UNIQUE INDEX SYS.DB_LOGO_RULE_USER_NAME ON SYS.DB_LOGO_RULE_USER (USER_NAME);

数据库用户登录校验-ip地址

CREATE TABLE SYS.DB_LOGO_RULE_IP (

USER_NAME VARCHAR2(30 BYTE),

IP_ADDRESS VARCHAR2(30 BYTE),

OPERATETIME DATE DEFAULT SYSDATE NOT NULL ENABLE,

REMARK VARCHAR2(100 CHAR)

);

COMMENT ON COLUMN SYS.DB_LOGO_RULE_IP.USER_NAME IS '用户名称';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_IP.IP_ADDRESS IS 'ip地址';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_IP.OPERATETIME IS '操作时间';

COMMENT ON TABLE SYS.DB_LOGO_RULE_IP IS '数据库用户登录校验-ip地址，有问题请联系Kindey.S，微信kindey123;

数据库用户登录校验-终端

CREATE TABLE SYS.DB_LOGO_RULE_MDL (

USER_NAME VARCHAR2(30 BYTE),

IP_ADDRESS VARCHAR2(30 BYTE),

MODULE VARCHAR2(30 BYTE),

OPERATETIME DATE DEFAULT SYSDATE NOT NULL ENABLE

);

COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.IP_ADDRESS IS 'ip地址';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.USER_NAME IS '数据库用户';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.MODULE IS '终端标识';

COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.OPERATETIME IS '操作时间';

COMMENT ON TABLE SYS.DB_LOGO_RULE_USER IS '数据库用户登录校验-终端，有问题请联系Kindey.S，微信kindey123;

schema触发器

create or replace trigger CHECK_LOGO_HXWORK

after logon on HXWORK.schema

/*

authro:Kindey.S

date:2018-07-23

version:1.0.0.0

remark:check ip for schema HXWORK, not allowed id cann't connnect this schema.

date:2019-01-02

version:1.0.1.1

remark:修复在本地用sqlplus 用户名/密码 方式连接数据库失败的问题

*/

declare

CHECK_LEVEL number(1,0);

COUNT_IP number(10,0);

COUNT_MDL number(10,0);

begin

select CHECK_LEVEL into CHECK_LEVEL from DB_LOGO_RULE_USER where CHECK_LEVEL != 0 and USER_NAME = user;

if CHECK_LEVEL is null then CHECK_LEVEL := 0; end if;

if CHECK_LEVEL = 1 then

select count(1) into COUNT_IP from DB_LOGO_RULE_IP

where USER_NAME = user and IP_ADDRESS = nvl(SYS_CONTEXT('USERENV', 'IP_ADDRESS'),'127.0.0.1');

if COUNT_IP = 0 then

RAISE_APPLICATION_ERROR(-20000

, 'User '||user ||'checked by IP address,'

||'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ')'

);

end if;

elsif CHECK_LEVEL = 2 then

select count(1) into COUNT_MDL

from DB_LOGO_RULE_MDL

where USER_NAME = user

and IP_ADDRESS = SYS_CONTEXT('USERENV', 'IP_ADDRESS')

and (MODULE = SYS_CONTEXT('USERENV', 'MODULE') or MODULE is null);

if COUNT_MDL = 0 then

RAISE_APPLICATION_ERROR(-20000

, 'User '||user ||'checked by module,'

||'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS')

||' and this module (' || SYS_CONTEXT('USERENV', 'MODULE') || ')'

);

end if;

end if;

end;

/