Hướng dẫn trigger bắt thông tin log in

 Mình chia sẻ trigger bắt thông tin log in: Thông tin log lưu trong bảng log_login Exclude lưu trong bảng exclude_login Chỉ action với database primary, standby thì bỏ qua

--- ThieuVD --- Tạo bảng lưu log
CREATE TABLE SYSTEM.log_login
(
user_name VARCHAR2 (300),
OS_USER VARCHAR2 (300),
HOST VARCHAR2 (300),
ip_address VARCHAR2 (300),
SERVICE_NAME VARCHAR2 (300),
DBLINK_INFO VARCHAR2 (300),
CURRENT_SCHEMA VARCHAR2 (300),
MODULE VARCHAR2 (300),
SCHEDULER_JOB VARCHAR2 (300),
SERVER_HOST VARCHAR2 (300),
CLIENT_INFO VARCHAR2 (300),
AUTHENTICATION_METHOD VARCHAR2 (300),
AUTHENTICATED_IDENTITY VARCHAR2 (300),
TERMINAL VARCHAR2 (300),
create_time DATE
)
TABLESPACE users
PARTITION BY RANGE (create_time)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )(
PARTITION
SYS_P201801
VALUES LESS THAN
(TO_DATE (' 2018-02-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
TABLESPACE users,
PARTITION
SYS_P201802
VALUES LESS THAN
(TO_DATE (' 2018-03-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
TABLESPACE users);
--- ThieuVD --- Tạo bảng lưu exclude_login
CREATE TABLE SYSTEM.exclude_login
(
user_name VARCHAR2 (300),
ip_address VARCHAR2 (300),
SERVICE_NAME VARCHAR2 (300),
status VARCHAR2 (300)
)
TABLESPACE users;
--- ThieuVD --- Tạo trigger bắt log_login
CREATE OR REPLACE TRIGGER SYS.AUDIT_LOGON
AFTER LOGON
ON DATABASE
DECLARE
CURSOR c_database_role IS
SELECT 1
FROM v$database
WHERE database_role IN ('PRIMARY');
CURSOR c_exclude_login IS
SELECT 1
FROM SYSTEM.exclude_login
WHERE UPPER (user_name) =
UPPER (SYS_CONTEXT ('USERENV', 'SESSION_USER'))
AND UPPER (ip_address) =
UPPER (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'))
AND UPPER (SERVICE_NAME) =
UPPER (SYS_CONTEXT ('USERENV', 'SERVICE_NAME'))
AND status = 1;
v_database_role NUMBER;
v_exclude_login NUMBER;
BEGIN
OPEN c_database_role;
FETCH c_database_role INTO v_database_role;
OPEN c_exclude_login;
FETCH c_exclude_login INTO v_exclude_login;
IF c_database_role%FOUND
THEN
IF c_exclude_login%NOTFOUND
THEN
INSERT INTO SYSTEM.log_login (user_name,
OS_USER,
HOST,
IP_ADDRESS,
SERVICE_NAME,
DBLINK_INFO,
CURRENT_SCHEMA,
MODULE,
SCHEDULER_JOB,
SERVER_HOST,
CLIENT_INFO,
AUTHENTICATION_METHOD,
AUTHENTICATED_IDENTITY,
TERMINAL,
create_time)
VALUES (SYS_CONTEXT ('USERENV', 'SESSION_USER'),
SYS_CONTEXT ('USERENV', 'OS_USER'),
SYS_CONTEXT ('USERENV', 'HOST'),
SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT ('USERENV', 'SERVICE_NAME'),
SYS_CONTEXT ('USERENV', 'DBLINK_INFO'),
SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA'),
SYS_CONTEXT ('USERENV', 'MODULE'),
SYS_CONTEXT ('USERENV', 'SCHEDULER_JOB'),
SYS_CONTEXT ('USERENV', 'SERVER_HOST'),
SYS_CONTEXT ('USERENV', 'CLIENT_INFO'),
SYS_CONTEXT ('USERENV', 'AUTHENTICATION_METHOD'),
SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY'),
SYS_CONTEXT ('USERENV', 'TERMINAL'),
SYSDATE);
COMMIT;
END IF;
END IF;
CLOSE c_database_role;
CLOSE c_exclude_login;
END;
/

Check dữ liệu

select * from SYSTEM.log_login;