Oracle培训笔记 8.7 审计

 

审计
目的：审查谁、做了什么

相关参数
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      E:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \MYDB\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

1、audit_sys_operations ： 审计sys用户操作
alter system set audit_sys_operations=true scope=spfile;
重启数据库
windows: 记录在事件查看器中

2、audit_trail：普通审计
select * from v$parameter_valid_values where name='audit_trail'
DB
DB_EXTENDED
OS

alter system set audit_trail=db scope=spfile;
重启数据库

分类
1）语句审计：审计执行了什么样的语句
audit table by 用户 by {session|access} whenever [not]successful;

audit table by u1 whenever successful;
audit session;
select * from dba_stmt_audit_opts;

以u1用户登录，创建删除表，查看审计记录
select * from dba_audit_trail

去掉审计项
noaudit table;
noaudit session;

删除审计记录
delete from aud$;
commit;
查数据库对象的ddl语句
select dbms_metadata.get_ddl('VIEW','DBA_AUDIT_TRAIL') from dual

alter system set audit_trail=db_extended scope=spfile;
重启数据库

【注】更改审计项后，已登录用户重新登录后生效

2）权限审计：审计使用了哪个权限
audit create any table;
audit create table;

select * from dba_priv_audit_opts;

grant create any table to u1;

select * from dba_audit_trail;

以u1用户登录，在u2创建表
sqlplus u1/u1
create table u2.t1(id number);
create table t1(id number);

select * from dba_audit_trail

去掉审计项
noaudit create any table;
noaudit create table;

删除审计记录
delete from aud$;
commit;

3）对象审计：审计在对象上执行了什么操作
audit select on u1.t1;
audit update on u1.t1;

select * from dba_obj_audit_opts;

以u1用户登录
sqlplus u1/u1
insert into t1 values(100);
select * from t1;
update t1 set id=1000;

select * from dba_audit_trail

去掉审计项
noaudit select on u1.t1;
noaudit update on u1.t1;

删除审计记录
delete from aud$;
commit;

3、fga-fine grained audit,精度审计
和参数audit_trail无关

sqlplus u1/u1
create table salary(id number,name varchar2(20),salary number);

增加审计策略--根据条件
sqlplus / as sysdba
begin
dbms_fga.add_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p1',
audit_condition => 'salary>5000',
enable => true,
statement_types =>'select,insert,delete,update'
);
end;
begin
dbms_fga.add_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p2',
audit_condition => 'salary>5000',
enable => true
);
end;

begin
dbms_fga.drop_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p2'
);
end;
select * from dba_audit_policies;

sqlplus u1/u1
insert into salary values(1,'a',3000);
insert into salary values(2,'b',6000);

select * from dba_fga_audit_trail;

禁用审计
begin
dbms_fga.disable_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p1'
);
end;

删除审计记录
delete from fga_log$;
commit;

 

增加审计策略--审计指定列
sqlplus / as sysdba

begin
dbms_fga.disable_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p2'
);
end;

begin
dbms_fga.drop_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p3'
);
end;

begin
dbms_fga.add_policy(object_schema =>'U1' ,
object_name =>'SALARY' ,
policy_name => 'p3',
audit_column => 'name',
audit_condition => 'salary>5000',
enable => true,
statement_types =>'select,insert,delete,update'
);
end;

select * from dba_audit_policies;

select * from dba_fga_audit_trail;

删除审计记录
delete from fga_log$;
commit;