9i新特性之数据库监控系列
前言
对数据库的监控是一个重要的工作，好的监控工作可以防止或者是检查到机器异常，人为错误。从ORACLE8i开始，数据库推出了一系列的数据库事件触发器，可以对数据库系统进行跟踪审计，防止错误发生或检查异常原因。
因为仅仅化了一天的时间来写这些东西，所有比较仓促，肯定会有些遗漏或不对的地方，欢迎补充。
其中一个典型的新特性就是利用DBMS_FGA来审计SELECT语句。这个东西化了我太多时间，别的东西，其实早就成型了的。
以下两个过程呢，其实是辅助过程，一个是完成写日志，一个是完成发邮件，在以后的程序中，可能会经常用到。
-----------------------------------------------------------------------------------------------------
1、写日志过程
/**************************************************************************
name:sp_Write_log
parameter:textContext in varchar2 日志内容
create date:2003-06-01
creater:chen jiping
desc: ·写日志,把内容记到服务器指定目录下
·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
****************************************************************************/
create or replace PROCEDURE sp_Write_log(textContext VARCHAR2)
IS
file_handle UTL_FILE.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--打开文件
--Write_file_name := rtrim(to_char(SYSDATE,'YYYY-MM-DD'))||'.log';
Write_file_name := 'db108_alert.log';
file_handle := UTL_FILE.FOPEN('/u01/product/admin/ora81/logs',Write_file_name,'a');
Write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||textContext;
--写文件
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.PUT_LINE(file_handle,Write_content);
END IF;
--关闭文件
UTL_FILE.Fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.Fclose(file_handle);
END IF;
END sp_Write_log;
2、发送Email的过程
/**************************************************************************
name:sp_Send_mail
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
create date:2003-06-01
creater:chen jiping
desc: ·发送邮件到指定邮箱
·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
****************************************************************************/
create or replace procedure sp_send_mail(
Rcpter IN VARCHAR2,
Mail_Content IN VARCHAR2)
IS
conn utl_smtp.connection;
PROCEDURE send_header(NAME IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME || ': ' || header || utl_tcp.CRLF);
END;
BEGIN
conn := utl_smtp.open_connection('smtp.ur.net.cn');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" <'||Rcpter||'>');
send_header('Subject', 'Hello');
utl_smtp.write_data(conn, utl_tcp.CRLF || Mail_Content);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The quit call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || SQLERRM);
END sp_send_mail;
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('[email]urmail@mail.com[/email]',msMsg);
EXCEPTION
WHEN OTHERS THEN
sp_send_mail(' [email]urmail@mail.com[/email] ',ora_database_name||' shutdown error');
END;
说明：当数据库关闭之前，发送Mail到指定邮箱
不要在关闭/启动数据库的触发器中调用utl_file包写文件，可能会导致BUG，引起数据库不能启动，如果实在有必要，则关闭数据库与启动数据库不要使用同一个会话。
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('[email]urmail@mail.com[/email]',msMsg);
EXCEPTION
WHEN OTHERS THEN
sp_send_mail(' [email]urmail@mail.com[/email] ',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;
说明：这个触发器监控所有登录用户，并把其信息存入到以上表中。
根据表中记载的信息，可以获得所有登录信息，用于审计用户的登陆是否许可。
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('捕获DML语句异常错误：'||SQLERRM);
END;
7、监控表的Select语句
表的查询用触发器是检测不到的，除非开启数据库审计，这样又显得过于麻烦，从ORACLE9i开始，提供了一个DBMS_FGA包，可以对单个的表进行审计并查询审计资料。但是这个包的审计过程要求数据库运行在CBO优化模式下，如果不是，可能会有意想不到的结果，这就要求对审计的表进行分析。
先简单介绍一下该包中的第一个过程
PROCEDURE add_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
audit_condition IN VARCHAR2 := '1=1',
audit_column IN VARCHAR2 := NULL,
handler_schema IN VARCHAR2 := NULL,
handler_module IN VARCHAR2 := NULL,
enable IN BOOLEAN := TRUE);
object_schema：要审计的用户的名称，默认是本用户
object_name：要审计的对象的名称
policy_name：这次审计的策略名称，每次审计都有一个名称，来与别的审计策略区分
注：以上三部分唯一确定一个审计策略
audit_condition：审计条件（谓语动词），默认全部，如规定SID=10，那么当返回的结果集中包含SID=10的行或者SID在where条件中被使用的时候，该查询语句将被审计。但是还有一种情况是如果显式或隐式指定了audit_column，where条件中也没有SID=10，但是结果集参考到了SID=10，这个语句也被审计。
audit_column：表示审计那些列，默认全部，如果指定列，那么只有select（显式指定）或者是where（隐式指定） 指定该列的时候才被审计，如果同时指定了audit_column与audit_condition，那么必须满足audit_column，才能被审计
如果同时执行了两个条件，需要两个条件同时满足
handler_schema：我们还可以规定一个管理者来管理这个策略并在策略执行的时候，执行一个指定的过程
handler_module：指定这个策略执行的时候，执行的存储过程的名称
enable：确定审计策略是否马上生效。
比如，我们现在增加一个策略
BEGIN
dbms_fga.add_policy( object_schema => 'MANAGER',
object_name => 'my_table,
policy_name => 'chk_table',
--audit_condition => 'sid like ''1%''',
--audit_column => 'sid',
--handler_schema => 'manager',
--handler_module => 'sp_chk_mytable',
enable => TRUE );
END;
以上的意思是，开始审计MANAGER用户的表my_table，策略的名称叫chk_table。
如果开启audit_condition，则只对谓语条件隐式或显式包含SID LIKE '1%' 的语句审计
如果开启audit_column，则只对查询列或条件列中有SID的语句审计
如果开启了handler_schema与handler_module，则需要一个handler_schema指定的用户manager下的存储过程叫sp_chk_mytable，这个过程可以类似为：
CREATE PROCEDURE sp_chk_mytable (
p_object_schema VARCHAR2,
p_object_name VARCHAR2,
p_policy_name VARCHAR2) AS
BEGIN
INSERT INTO audit$proc (audtime,loguser,audsid, clientip
object_schema, object_name, policy_name )
VALUES (sysdate,ora_login_user,userenv('SESSIONID'),
sys_context('userenv','ip_address'),p_object_schema,p_object_name, p_policy_name );
EXCEPTION
WHEN OTHERS THEN
pkgsys_manage.sp_write_log('审计语句异常错误：'||SQLERRM);
END sp_chk_mytable;
当然，这里还需要建立一个audit$proc的表，这个就不多说了，存储过程可以根据自己的要求来改写，获取更多的用户信息或直接与登录信息表关联查询。
注意：如果指定了过程而没有过程的时候，审计不会失败，但是被审计的数据将不能被普通查询获取
好，我们就运行一个简单的例子，这个例子不包含运行本地过程。
SQL> select * from test;
A B
--------------------------------------- ---------------------------------------
1 2
3 4
2 6
4 5
6 7
SQL> analyze table test compute statistics;
Table analyzed
SQL> BEGIN
2 dbms_fga.add_policy( object_schema => 'MANAGER',
3 object_name => 'test',
4 policy_name => 'chk_test',
5 audit_condition => 'a = 1',
6 audit_column => 'b',
7 enable => TRUE );
8 END;
9 /
PL/SQL procedure successfully completed
SQL>
我们查询如下视图（或者是基表sys.fga$）
SQL> select t.object_schema,t.object_name,t.policy_name,t.enabled from dba_audit_policies t;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
------------------------------ ------------------------------ ------------------------------ -------
MANAGER TEST CHK_TEST YES
就可以看到我们的定制的策略信息。
现在我们看看该审计策略怎么生效
先需要了解sys.fga_log$（基表）与Dba_Fga_Audit_Trail（视图）
fga_log$存放审计信息的表，而Dba_Fga_Audit_Trail是对应的视图
SQL> select count(*) from Dba_Fga_Audit_Trail ;
COUNT(*)
----------
0
可以看到，是没有记录的
我们现在来执行查询
SQL> select b from test where a=1;
B
---------------------------------------
2
该语句肯定被审计，这个是最典型的情况了，显式包含审计列b，谓语条件正好是a=1。
SQL> select b from test where b=2;
B
---------------------------------------
2
这个语句也被审计，为什么呢？这个语句包含审计列，而且隐式包含了a=1（因为a=1与b=2是同一行）
SQL> select a from test where a=2;
A
---------------------------------------
2
这个语句就不被审计了，典型的什么都不满足
SQL> select b from test where b=4;
B
---------------------------------------
4
这个语句没有被审计，因为虽然满足了审计列的条件，但是没有显式或隐式包含a=1
SQL> select rownum from test where a=1 and b=2;
ROWNUM
---------------------------------------
1
这个语句也被审计，因为隐式包含B，谓语条件也满足a=1
所以以上被审计的语句应当是3个，检查一下了
SQL> select OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,SQL_TEXT from Dba_Fga_Audit_Trail;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
-------------------------------- ------------------ ----------------------------------------------------
MANAGER TEST CHK_TEST select rownum from test where a=1 and b=2
MANAGER TEST CHK_TEST select b from test where a=1
MANAGER TEST CHK_TEST select b from test where b=2
对于已经定制的审计策略，我们可以利用dbms_fga.drop_policy来删除。
如
BEGIN
dbms_fga.drop_policy( object_schema => 'MANAGER',
object_name => 'test',
policy_name => 'chk_test');
END;
就是删除我们刚才定制的审计策略，当然我们还可以利用enable_policy与disable_policy来开启与禁止相应的审计策略
最后，对于审计的记录会越来越多，我们必须手工维护，删除没有参考价值的记录，我们可以运行如下查询来删除审计记录（需要delete any table的权限或在sys下执行）：
