ORACLE审计

一.开启审计

alter system set audit_sys_operations=TRUE scope=spfile;  --审计管理用户
alter system set audit_trail=db,extended scope=spfile;  //将sql语句写入审计表中

重启数据库查看

shutdown immediate;
startup;
show parameter audit;

二.迁移并重建审计表(因审计表默认存放于system表空间，为避免发生审计表过大将system表空间撑满，故移出审计表)　　

create tablespace AUDITSPACE datafile '/dbfile/oracle/oradata/system/AUDITSPACE.dbf' size 30G;

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDITSPACE');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDITSPACE');
END;
/

alter table audit$ move tablespace AUDITSPACE;

alter index i_audit rebuild online tablespace AUDITSPACE;

alter table audit_actions move tablespace AUDITSPACE;

alter index i_audit_actions rebuild online tablespace AUDITSPACE;

重建审计表按天自增分区
create table AUD$
(
sessionid NUMBER not null,
entryid NUMBER not null,
statement NUMBER not null,
timestamp# DATE default sysdate,
userid VARCHAR2(30),
userhost VARCHAR2(128),
terminal VARCHAR2(255),
action# NUMBER not null,
returncode NUMBER not null,
obj$creator VARCHAR2(30),
obj$name VARCHAR2(128),
auth$privileges VARCHAR2(16),
auth$grantee VARCHAR2(30),
new$owner VARCHAR2(30),
new$name VARCHAR2(128),
ses$actions VARCHAR2(19),
ses$tid NUMBER,
logoff$lread NUMBER,
logoff$pread NUMBER,
logoff$lwrite NUMBER,
logoff$dead NUMBER,
logoff$time DATE,
comment$text VARCHAR2(4000),
clientid VARCHAR2(64),
spare1 VARCHAR2(255),
spare2 NUMBER,
obj$label RAW(255),
ses$label RAW(255),
priv$used NUMBER,
sessioncpu NUMBER,
ntimestamp# TIMESTAMP(6),
proxy$sid NUMBER,
user$guid VARCHAR2(32),
instance# NUMBER,
process# VARCHAR2(16),
xid RAW(8),
auditid VARCHAR2(64),
scn NUMBER,
dbid NUMBER,
sqlbind CLOB,
sqltext CLOB,
obj$edition VARCHAR2(30),
creatdate DATE default sysdate
)
partition by range (CREATDATE) INTERVAL (NUMTODSINTERVAL(1, 'day')) store in (AUDITSPACE)
(
partition SYS_AUD_P01 values less than (TO_DATE('2021-04-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace AUDITSPACE
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
));

 三.增加审计对象

　　审计用户username所有成功的操作

audit all by username by access whenever successful;

　　针对用户的审计(未执行成功的也审计)

audit select table by username by access; //查表审计
audit update table by username by access; //更新审计
audit delete table by username by access; //删除审计
audit insert table by username by access; //插入审计

　　针对某表的更新、删除审计(错误也审计)

AUDIT UPDATE,DELETE,INSERT ON T_TEST by access; 

　　保护审计

audit all on sys.aud$ by access;

　　其他审计：

select * from AUDIT_ACTIONS t;    --所有可审计的操作

audit alter table by USERNAME by access;

audit alter sequence by USERNAME by access;

　　查看审计

select * from dba_audit_trail;

select * from dba_stmt_audit_opts where user_name='USERNAME';

 四.自动删除审计表 

--自动删表分区存储过程
create or replace procedure drop_partition(dateBefore in Date, tableName in varchar2) as
       v_sql   varchar2(1024);
       temp    varchar2(1024);
       temp_D    date;
       v_temp   varchar2(1024);
       v_error varchar2(1024);
        CURSOR cur_partition IS
              select t.partition_name,t.high_value  from user_tab_partitions t where t.table_name=tableName  order by t.last_analyzed desc;

begin
    for for_partition in cur_partition loop
     begin
        v_temp := 'select to_char('||for_partition.high_value||',''yyyy-MM-dd'') from dual';
        execute immediate v_temp into temp ;
        select to_date(temp,'yyyy-mm-dd') into temp_D from dual;
        if (temp_D<dateBefore)    then
        v_sql := 'alter table '||tableName||' drop partition ' ||for_partition.partition_name;
        Dbms_Output.put_line(v_sql);
        execute immediate v_sql;
        commit;
        end if;
        end;
        end loop;
    close cur_partition;
    exception when others then
      v_error := sqlcode || ' - ' || sqlerrm;
      dbms_output.put_line(v_error);
      rollback;
end drop_partition;

　每天自动删除五天前分区

begin
  sys.dbms_job.submit(job => :job,
                      what => 'drop_partition(sysdate-5,''AUD$'');',
                      next_date => to_date('03-04-2021 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(sysdate) + 1 +1/(24)');
  commit;
end;
/