Oracle9i新特性之数据库监控系列详解

共有 2 条评论

1. augustnov 于 2006-12-05 00:37:27发表:

   5、监控所有DDL的触发器
   
   当然，在此之前我们需要建立一张表，用来记录所有的DDL操作的信息。
   
   
   　　
   　　create table DDL$TRACE
   　　(
   　　LOGIN_USER　　VARCHAR2(30),
   　　AUDSID　　　　NUMBER,
   　　IPADDRESS　　 VARCHAR2(20),
   　　SCHEMA_USER　 VARCHAR2(30),
   　　SCHEMA_OBJECT VARCHAR2(30),
   　　DDL_TIME　　　DATE,
   　　DDL_SQL　　　 VARCHAR2(4000)
   　　)
   
   
   　　
   下面就是触发器的主体，用来记录审计所有的DDL操作。
   
   
   　　
   　　CREATE OR REPLACE TRIGGER tr_trace_ddl
   　　AFTER ddl
   　　ON database
   　　DECLARE
   　　sql_text ora_name_list_t;
   　　state_sql ddl$trace.ddl_sql%TYPE;
   　　BEGIN
   　　FOR i IN 1..ora_sql_txt(sql_text) LOOP
   　　state_sql := state_sql||sql_text(i);
   　　END LOOP;
   　　
   　　INSERT INTO ddl$trace(login_user,audsid,ipaddress,
   　　schema_user,schema_object,ddl_time,ddl_sql)
   　　VALUES(ora_login_user,userenv('SESSIONID'),sys_context('userenv','ip_address'),
   　　ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);
   　　EXCEPTION
   　　WHEN OTHERS THEN
   　　sp_write_log('捕获DDL语句异常错误：'||SQLERRM);
   　　END tr_trace_ddl;
   
   
   　　
   说明：以上语句是监控整个数据库的DDL语句，如果只想监控一个用户的话，需要修改。
   
   
   　　
   　　ON database
   　　为
   　　ON uruser.schema
   
   
   　　
   6、捕获有需要的DML语句
   
   对于某些特殊的表，可能需要记载DML语句，我们也需要创建一张表来记载这个信息：
   
   
   　　create table CAPT$SQL
   　　(
   　　CAPT_TIME　DATE,
   　　USERNAME　 VARCHAR2(30),
   　　AUDSID　　　　NUMBER,
   　　CLIENT_IP　VARCHAR2(20),
   　　SQL_TEXT　 VARCHAR2(4000),
   　　TABLE_NAME VARCHAR2(30),
   　　OWNER　　　VARCHAR2(30)
   　　)
   
   
   　　
   以下就是捕获特定表的DML语句的触发器:
   
   
   　　
   　　CREATE OR REPLACE TRIGGER tr_capt_sql
   　　BEFORE DELETE OR INSERT OR UPDATE
   　　ON mtamanager.emailbox
   　　DECLARE
   　　stmt VARCHAR2(4000);
   　　sql_text ora_name_list_t;
   　　BEGIN
   　　FOR i IN 1..ora_sql_txt(sql_text) LOOP
   　　stmt := stmt || sql_text(i);
   　　END LOOP;
   　　
   　　INSERT INTO
   　　capt$sql(CAPT_TIME,USERNAME,AUDSID,CLIENT_IP,SQL_TEXT,
   　　TABLE_NAME,OWNER)
   　　VALUES(sysdate,ora_login_user,userenv('SESSIONID'),
   　　sys_context('userenv','ip_address'),stmt,'emailbox','mtamanager');
   　　EXCEPTION
   　　WHEN OTHERS THEN
   　　pkgsys_manage.sp_write_log

2. augustnov 于 2006-12-05 00:37:04发表:

   3、监控数据库关闭/启动的触发器
   
   
   　　create or replace trigger TR_DB_SHUTDOWN
   　　before shutdown ON DATABASE
   　　DECLARE
   　　msMsg VARCHAR2(500);
   　　BEGIN
   　　msMsg :='user '||ora_login_user||' in '
   ||ora_client_ip_address||' ready shutdown database '
   ||ora_database_name|| ' now';
   　　sp_send_mail('urmail@mail.com',msMsg);
   　　EXCEPTION
   　　WHEN OTHERS THEN
   　　sp_send_mail(' urmail@mail.com ',ora_database_name||' shutdown error');
   　　END;
   
   
   　　
   说明：当数据库关闭之前，发送Mail到指定邮箱.不要在关闭/启动数据库的触发器中调用utl_file包写文件，可能会导致BUG，引起数据库不能启动，如果实在有必要，则关闭数据库与启动数据库不要使用同一个会话。
   
   4、监控登录用户的触发器
   
   先需要建立一张表，用于存放登陆信息。
   
   
   　　create table LOG$INFORMATION
   　　(
   　　ID　　　　NUMBER(10),
   　　USERNAME　VARCHAR2(30),
   　　LOGINTIME DATE,
   　　TERMINAL　VARCHAR2(50),
   　　IPADRESS　VARCHAR2(20),
   　　OSUSER　　VARCHAR2(30),
   　　MACHINE　 VARCHAR2(64),
   　　PROGRAM　 VARCHAR2(64),
   　　SID　　　 NUMBER,
   　　SERIAL#　 NUMBER,
   　　AUSID　　 NUMBER
   　　)
   
   
   　　
   然后需要创建一个序列，才产生连续的序列号，根据序列的信息，可以更好的得到登录的信息。
   
   
   　　
   　　create sequence SQ_LOGIN
   　　minvalue 1
   　　maxvalue 999999999
   　　start with 1
   　　increment by 1
   　　cache 20;
   
   
   　　
   最后创建触发器，记载登录信息。
   
   
   　　
   　　CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD
   　　AFTER logon ON DATABASE
   　　DECLARE
   　　mtSession v$session%ROWTYPE;
   　　CURSOR cSession(iiQuerySid IN NUMBER) IS
   　　SELECT * FROM v$session
   　　WHERE audsid = iiQuerySid;
   　　BEGIN
   　　OPEN cSession(userenv('SESSIONID'));
   　　FETCH cSession INTO mtSession;
   　　IF cSession%FOUND THEN
   　　INSERT INTO log$information(id,username,logintime,terminal,ipadress,osuser,machine,
   　　program,sid,serial#,ausid)
   　　VALUES(sq_login.nextval,USER,SYSDATE,mtSession.Terminal,
   　　SYS_CONTEXT ('USERENV','IP_ADDRESS'),mtSession.Osuser,
   　　mtSession.Machine,mtSession.Program,mtSession.
   Sid,mtSession.Serial#,userenv('SESSIONID'));
   　　ELSE
   　　sp_write_log('session信息错误：'||SQLERRM);
   　　raise_application_error(-20099,'登录异常错误',FALSE);
   　　END IF;
   　　CLOSE cSession;
   　　EXCEPTION
   　　WHEN OTHERS THEN
   　　sp_write_log('登记登录信息错误：'||SQLERRM);
   　　RAISE;
   　　END;
   
   
   　　
   说明：这个触发器监控所有登录用户，并把其信息存入到以上表中。根据表中记载的信息，可以获得所有登录信息，用于审计用户的登陆是否许可。