oracle数据库登录、DDL触发器的应用_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1719 | 回复: 0   主题: oracle数据库登录、DDL触发器的应用        下一篇 
justfriend
注册用户
等级:新兵
经验:61
发帖:62
精华:0
注册:2011-11-21
状态:离线
发送短消息息给justfriend 加好友    发送短消息息给justfriend 发消息
发表于: IP:您无权察看 2015-7-28 18:19:06 | [全部帖] [楼主帖] 楼主

登录触发器

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 NOLOGGING;
create index SYSTEM.IDX_LOGIN_LOG1 on SYSTEM.LOGIN_LOG (LOGIN_ON_TIME desc,SESSION_ID) tablespace TS_AUDIT_MON NOLOGGING;
create index SYSTEM.IDX_LOGIN_LOG2 on SYSTEM.LOGIN_LOG (USER_IN_DB,SESSION_ID) tablespace TS_AUDIT_MON NOLOGGING;
create index SYSTEM.IDX_LOGIN_LOG3 on SYSTEM.LOGIN_LOG (SESSION_ID) tablespace TS_AUDIT_MON NOLOGGING;


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 NOLOGGING;
create index system.idx_ddl_log1 on system.ddl_log (ddl_date desc,session_id) tablespace TS_AUDIT_MON NOLOGGING;
create index system.idx_ddl_log2 on system.ddl_log (user_name,session_id) tablespace TS_AUDIT_MON NOLOGGING;


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字段唯一关联,便于事后审查

手工进行统计分析,避免错误执行计划带来不必要的性能问题。

exec dbms_stats.gather_table_stats(ownname=>'system',tabname=>'LOGIN_LOG',degree=>8,estimate_percent=>100,cascade=>true);
exec dbms_stats.gather_table_stats(ownname=>'system',tabname=>'ddl_log',degree=>8,estimate_percent=>100,cascade=>true);


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论