oracle 审计总结
oracle 审计总结:
和审计相关的两个主要参数
Audit_sys_operations:
默认为false,当设置为true时,所有sys用户(包括以sysdba,sysoper身份登录的用户)的操作都会被记录,audit trail不会写在aud$表中,这个很好理解,如果数据库还未启动aud$不可用,那么像conn /as sysdba这样的连接信息,只能记录在其它地方。如果是windows平台,audti trail会记录在windows的事件管理中,如果是linux/unix平台则会记录在audit_file_dest参数指定的文件中。
Audit_trail:
None:是默认值,不做审计;
DB:将audit trail 记录在数据库的审计相关表中,如aud$,审计的结果只有连接信息;
DB,Extended:这样审计结果里面除了连接信息还包含了当时执行的具体语句;
OS:将audit trail 记录在操作系统文件中,文件名由audit_file_dest参数指定;
XML:10g里新增的。
注:这两个参数是static参数,需要重新启动数据库才能生效。
1、激活审计
SQL> conn /as sysdba
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
SQL> alter system set audit_sys_operations=TRUE scope=spfile; --审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db,extended scope=spfile;
SQL> startup force;
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/ORCL/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED
2、开始审计
SQL> conn /as sysdba
SQL> audit all on t_test;
SQL> conn u_test
SQL> select * from t_test;
SQL> insert into u_test.t_test (c2,c5) values ('test1','2');
SQL> commit;
SQL> delete from u_test.t_test;
SQL> commit;
SQL> conn /as sysdba
SQL> col DEST_NAME format a30
col OS_USERNAME format a15
col USERNAME format a15
col USERHOST format a15
col TERMINAL format a15
col OBJ_NAME format a30
col SQL_TEXT format a60
SQL> select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_text from dba_audit_trail;
对象审计:
AUDIT DELETE ON jeff.emp;
AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL;
取消审计:
NOAUDIT session;
NOAUDIT session BY jeff, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,EXECUTE PROCEDURE;
NOAUDIT ALL; -- 取消所有statement审计
NOAUDIT ALL PRIVILEGES; -- 取消所有权限审计
NOAUDIT ALL ON DEFAULT; -- 取消所有对象审计
3、清除审计信息
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='EMP';
将审计结果表从system表空间里移动到别的表空间上
实际上sys.aud$表上包含了两个lob字段,并不是简单的move table就可以。
下面是具体的过程:
sql>connect / as sysdba;
sql>alter table aud$ move tablespace
sql>alter index I_aud1 rebuild online tablespace
SQL> alter table audit$ move tablespace
SQL> alter index i_audit rebuild online tablespace
SQL> alter table audit_actions move tablespace
SQL> alter index i_audit_actions rebuild online tablespace
alter table sys.aud$ move tablespace users;
alter table sys.aud$ move lob(sqlbind) store as( tablespace USERS);
alter table sys.aud$ move lob(SQLTEXT) store as( tablespace USERS);
alter index sys.I_AUD1 rebuild tablespace users;
4 删除审计记录
create or replace procedure p_sjdele as
v_sql varchar2(100);
begin
v_sql:='truncate table sys.aud$';
insert into ssj.t_ssj t
select *
from sys.aud$ s
where s.userhost <> 'WINDOWS-GCMZW2Z';
execute immediate v_sql;
end;
declare
job number;
begin
sys.dbms_job.submit(job,
'p_sjdele;',
sysdate,
'TRUNC(LAST_DAY(SYSDATE ) + 1)');
end;