oracle数据库登录失败触发器,oracle数据库登录、DDL触发器的应用

登录触发器

oracle登录记录触发器:

1.创建日志记录表：

CREATE TABLE SYSTEM.LOGIN_LOG

(

SESSION_ID NUMBER,

LOGIN_ON_TIME DATE,

LOGIN_OFF_TIME DATE,

USER_IN_DB VARCHAR2(30),

PROCESS VARCHAR2(12),

TYPE VARCHAR2(10),

MACHINE VARCHAR2(64),

OSUSER VARCHAR2(30),

IP_ADDRESS VARCHAR2(20),

RUN_PROGRAM VARCHAR2(48)

) tablespace TS_AUDIT_MON;

create index SYSTEM.IDX_LOGIN_LOG1 on SYSTEM.LOGIN_LOG (LOGIN_ON_TIME desc,SESSION_ID) tablespace TS_AUDIT_MON;

create index SYSTEM.IDX_LOGIN_LOG2 on SYSTEM.LOGIN_LOG (USER_IN_DB,SESSION_ID) tablespace TS_AUDIT_MON;

2.创建登录触发器：

CREATE OR REPLACE TRIGGER SYS.LOGIN_ON_INFO

AFTER LOGON

ON DATABASE

BEGIN

INSERT INTO SYSTEM.LOGIN_LOG(session_id,login_on_time,login_off_time,user_in_db,process,type,machine,osuser,ip_address,run_program)

SELECT AUDSID,sysdate,null,sys.login_user,process,type,machine,osuser,SYS_CONTEXT('USERENV','IP_ADDRESS'),program

FROM v$session WHERE AUDSID=USERENV('SESSIONID');

END;

3.创建登出触发器：

CREATE OR REPLACE TRIGGER SYS.LOGIN_OFF_INFO

BEFORE LOGOFF

ON DATABASE

Begin

update system.login_log set login_off_time=sysdate where session_id=USERENV('SESSIONID');

exception when others then

null;

END;

4.日期筛选语句：

查询所有记录

set linesize 300

col USER_IN_DB for a10

col MACHINE for a10

col osuser for a10

col RUN_PROGRAM for a40

select SESSION_ID,TO_CHAR(LOGIN_ON_TIME,'YYYYMMDD HH24:MI:SS'),TO_CHAR(LOGIN_OFF_TIME,'YYYYMMDD HH24:MI:SS'),USER_IN_DB,PROCESS,TYPE,MACHINE,OSUSER,IP_ADDRESS,RUN_PROGRAM from SYSTEM.LOGIN_LOG;

DDL触发器

1. 创建ddl记录表

create table system.ddl_log

(

ddl_date date,

session_id number,

os_user VARCHAR2(30),

machine VARCHAR2(64),

user_name varchar2(30),

ip_addr VARCHAR2(20),

obj_name VARCHAR2(128),

ddl_type VARCHAR2(30),

object_type VARCHAR2(19),

owner VARCHAR2(30),

SQL_TEXT VARCHAR2(1000)

) tablespace TS_AUDIT_MON;

create index system.idx_ddl_log1 on system.ddl_log (ddl_date desc,session_id) tablespace TS_AUDIT_MON;

create index system.idx_ddl_log2 on system.ddl_log (user_name,session_id) tablespace TS_AUDIT_MON;

2.创建数据库级DDL触发器，记录所有ddl操作

CREATE OR REPLACE TRIGGER sys.ddl_audit AFTER CREATE OR ALTER OR DROP OR TRUNCATE OR GRANT OR REVOKE OR RENAME

on DATABASE declare

session_id_var number;

osuser_var VARCHAR2(30);

machine_var VARCHAR2(64);

ipaddr_var varchar2(20);

STEXT VARCHAR2(1000);

BEGIN

begin

select sys_context('USERENV', 'SESSIONID'),sys_context('USERENV', 'OS_USER'),sys_context('USERENV', 'HOST'),sys_context('USERENV', 'IP_ADDRESS') into session_id_var,osuser_var,machine_var,ipaddr_var FROM dual;

exception when others then

null;

end;

begin

select SQL_TEXT INTO STEXT FROM v$open_cursor WHERE UPPER(sql_text) LIKE 'ALTER%' or UPPER(sql_text) LIKE 'TRUNCATE%' or UPPER(sql_text) LIKE 'DROP%' or UPPER(sql_text) LIKE 'CREATE%' or UPPER(sql_text) LIKE 'GRANT%' or UPPER(sql_text) LIKE 'REVOKE%' or UPPER(sql_text) LIKE 'RENAME%';

exception when others then

STEXT:='-';

end;

insert into system.ddl_log(ddl_date,session_id,os_user,machine,user_name,ip_addr,obj_name,ddl_type,object_type,owner,SQL_TEXT) values

(sysdate,

session_id_var,

osuser_var,

machine_var,

user,

ipaddr_var,

NVL(ora_dict_obj_name,'-'),

NVL(ORA_SYSEVENT,'-'),

NVL(ora_dict_obj_type,'-'),

NVL(ora_dict_obj_owner,'-'),

STEXT

);

exception when others then

null;

end;

/

3.查询语句

查询所有记录

set linesize 300

col MACHINE for a10

col os_user for a10

col USER_NAME for a10

col ip_addr for a16

col obj_name for a15

col DDL_TYPE for a10

col object_type for a10

col owner for a10

col sql_text for a30

select TO_CHAR(ddl_date,'YYYYMMDD HH24:MI:SS'),session_id,os_user,machine,user_name,ip_addr,obj_name,ddl_type,object_type,owner,SQL_TEXT from system.ddl_log;

两边的SESSION_ID字段唯一关联，便于事后审查

随着记录数增多，还须做好历史表，定时进行数据转移