oracle记录IP登录日志

-- 建日志表

CREATE TABLE user_login_log (

    log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    username    VARCHAR2(30),

    ip_address  VARCHAR2(40),

    login_time  DATE DEFAULT SYSDATE,

    terminal    VARCHAR2(30),

    os_user     VARCHAR2(30),

    module      VARCHAR2(48)

);

CREATE OR REPLACE TRIGGER trg_log_user_login

AFTER LOGON ON DATABASE

DECLARE

    v_ip VARCHAR2(40);

BEGIN

    -- 获取客户端IP

    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO v_ip FROM DUAL;



    -- 插入日志

    INSERT INTO user_login_log (

        username, ip_address, login_time, terminal, os_user, module

    ) VALUES (

        SYS_CONTEXT('USERENV', 'SESSION_USER'),

        v_ip,

        SYSDATE,

        SYS_CONTEXT('USERENV', 'TERMINAL'),

        SYS_CONTEXT('USERENV', 'OS_USER'),

        SYS_CONTEXT('USERENV', 'MODULE')

    );

EXCEPTION

    WHEN OTHERS THEN

        -- 触发器不能报错，否则用户登不上库

        NULL;

END;

/

-- 查看所有登录IP

SELECT username, ip_address, login_time

FROM user_login_log

ORDER BY login_time DESC;



-- 按用户统计登录过哪些IP

SELECT username, ip_address, COUNT(*) AS login_count

FROM user_login_log

GROUP BY username, ip_address

ORDER BY username;