audit審計(jì)方法是什么

本篇內(nèi)容介紹了“audit審計(jì)方法是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

audit_trail 的value值為NONE表示不開(kāi)啟；

和審計(jì)相關(guān)的兩個(gè)主要參數(shù)：
1.audit_sys_operations：默認(rèn)為false，當(dāng)設(shè)置為true時(shí)，審計(jì)管理用戶(hù)(sysdba/sysoper角色登陸)的操作都會(huì)被記錄，audit trail不會(huì)寫(xiě)在aud$表中，這個(gè)很好理解，如果數(shù)據(jù)庫(kù)還未啟動(dòng)aud$不可用，那么像conn /as sysdba這樣的連接信息，只能記錄在其它地方。如果是windows平臺(tái)，audti trail會(huì)記錄在windows的事件管理中，如果是linux/unix平臺(tái)則會(huì)記錄在audit_file_dest參數(shù)指定的文件中。

2.audit_trail：None：是10g默認(rèn)值，不做審計(jì)；11g默認(rèn)值DB，將審計(jì)結(jié)果記錄到aud$表中；

audit_trail 的value值為FALSE表示不開(kāi)啟；

audit_trail 的value值為DB表示開(kāi)啟；

audit_trail 的value值為T(mén)URE表示開(kāi)啟；

audit_trail 的value值為OS表示審計(jì)記錄寫(xiě)入一個(gè)操作系統(tǒng)文

3.oracle 審計(jì)日志清理
--進(jìn)入審計(jì)日志目錄：
cd $ORACLE_BASE/admin/$ORACLE_SID/adump

--刪除3個(gè)月前的審計(jì)文件：
find ./ -type f -name "*.aud" -mtime +91|xargs rm -f

--一次清空所有審計(jì)文件
find ./ -type f -name "*.aud"|xargs rm-f

find ./ -mtime +7 -name "*.aud" -type f –delete

注意：
oracle在$ORACLE_BASE/admin/$ORACLE_SID/adump 目錄中記陸后綴為.aud的審計(jì)文件。
所以，開(kāi)了DB功能，會(huì)同時(shí)將審計(jì)日志記在AUD$表中和操作系統(tǒng)aud文件中。設(shè)置為NONE，仍然會(huì)而且毫無(wú)其他辦法的將記錄在操作系統(tǒng)aud文件中。
*數(shù)據(jù)庫(kù)的表為：sys.aud$
*操作系統(tǒng)目錄為：$ORACLE_BASE/admin/實(shí)例名/adump/

4.審計(jì)：
4.1 強(qiáng)制性審計(jì)
啟停數(shù)據(jù)庫(kù)等動(dòng)作，都記錄在了 alert 日志中，這些就是強(qiáng)制審計(jì)，是 oracle 自動(dòng)開(kāi)啟的。

4.2 標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)審計(jì)
show parameter audit_trail

4.3 基于值審計(jì)
這個(gè)是通過(guò)我們自己編寫(xiě)的觸發(fā)器來(lái)完成的。

4.4 細(xì)粒度審計(jì) (FGA)
可以針對(duì)某一列進(jìn)行更細(xì)致的審計(jì)

4.5 DBA 審計(jì)
安全管理員對(duì) DBA 的審計(jì)

標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)審計(jì)
- 審計(jì)語(yǔ)法：
audit sql_statement_clause by {session | access} whenever [not] successful;
 by session，在一個(gè)會(huì)話(huà)中，同類(lèi)型的操作只審計(jì)一條
 by access，每個(gè)符合審計(jì)的操作全部審計(jì)
- 審計(jì)相關(guān)參數(shù)（ audit_trail）：
audit_trail = { none | os | db [, extended] | xml [, extended] }
 none： 10g 默認(rèn)值，不做審計(jì)；
 os：將 audit trail 記錄在操作系統(tǒng)文件中，文件名由 audit_file_dest 參數(shù)指定；
 db： 11g 默認(rèn)值，將審計(jì)結(jié)果記錄到 aud$表中；
 db,extended：將審計(jì)結(jié)果記錄到 aud$表中，同時(shí)包括綁定變量及 CLOB 字段；
 xml：記錄 OS 文件的是 XML 格式的審計(jì)記錄；
 xml,extended：記錄OS 文件的是 XML 格式的審計(jì)記錄，同時(shí)包括綁定變量及 CLOB 字段。

oracle10g：默認(rèn)審計(jì)參數(shù)為 NONE,即未開(kāi)啟
Oracle11g：默認(rèn)審計(jì)參數(shù)為 DB

實(shí)驗(yàn)一：審計(jì)開(kāi)啟os
[oracle@wang ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 13 08:11:24 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/DBdb/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
SQL>
SQL> alter system set audit_trail='OS' scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             549456976 bytes
Database Buffers          281018368 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/DBdb/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      OS
SQL>  

--查看os層的審計(jì)文件
[oracle@wang adump]$ cd /u01/app/oracle/admin/DBdb/adump

--進(jìn)行相關(guān)操作

SQL> conn scott/tiger; 
Connected.
SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2018-01-26 00:28:58

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
JOBS                           TABLE

SQL> create table a as select * from user_objects;

Table created.

SQL> insert into a select * from a;

10 rows created.

SQL> commit;

Commit complete.

SQL> update a set object_id=1;

20 rows updated.

SQL> commit;

Commit complete.

SQL> delete a where rownum <10;

9 rows deleted.

SQL> commit;

Commit complete.

SQL> truncate table a;

Table truncated.

SQL> drop table a purge;

Table dropped.

--查看審計(jì)文件：
[oracle@wang adump]$ pwd
/u01/app/oracle/admin/DBdb/adump
[oracle@wang adump]$     
[oracle@wang adump]$
[oracle@wang adump]$ ll
total 24
-rw-r----- 1 oracle oinstall 772 Jan 26 00:22 DBdb_ora_27579_20180126002238441832143795.aud
-rw-r----- 1 oracle oinstall 755 Jan 26 00:26 DBdb_ora_27630_20180126002622032142143795.aud
-rw-r----- 1 oracle oinstall 762 Jan 26 00:26 DBdb_ora_27630_20180126002623437420143795.aud
-rw-r----- 1 oracle oinstall 768 Jan 26 00:26 DBdb_ora_27669_20180126002623481070143795.aud
-rw-r----- 1 oracle oinstall 772 Jan 26 00:26 DBdb_ora_27674_20180126002627838313143795.aud
-rw-r----- 1 oracle oinstall 877 Jan 26 00:28 DBdb_ora_27722_20180126002816963203143795.aud
[oracle@wang adump]$

--查看審計(jì)日志
[oracle@wang adump]$ more DBdb_ora_27722_20180126002816963203143795.aud
Audit file /u01/app/oracle/admin/DBdb/adump/DBdb_ora_27722_20180126002816963203143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      wang
Release:        3.10.0-327.el7.x86_64
Version:        #1 SMP Thu Oct 29 17:29:29 EDT 2015
Machine:        x86_64
Instance name: DBdb
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 27722, image: oracle@wang (TNS V1-V3)

Fri Jan 26 00:28:16 2018 +08:00
LENGTH: "266"
SESSIONID:[7] "7450116" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[5] "SCOTT" USERHOST:[4] "wang" TERMINAL:[5] "pts/1" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:
[26] "Authenticated by: DATABASE" OS$USERID:[6] "oracle" DBID:[10] "3282897732" PRIV$USED:[1] "5"

[oracle@wang adump]$

實(shí)驗(yàn)證明os層不記錄數(shù)據(jù)庫(kù)相關(guān)操作，只有一些登入登出數(shù)據(jù)庫(kù)操作實(shí)驗(yàn)二：
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/DBdb/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      OS
SQL>
SQL> alter system set audit_trail='DB' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             549456976 bytes
Database Buffers          281018368 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/DBdb/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
SQL>
SQL>  

--確認(rèn)審計(jì)相關(guān)的表是否已經(jīng)安裝
select * from sys.aud$;         -- 沒(méi)有記錄返回  
select * from dba_audit_trail;  -- 沒(méi)有記錄返回

如果做上述查詢(xún)的時(shí)候發(fā)現(xiàn)表不存在，說(shuō)明審計(jì)相關(guān)的表還沒(méi)有安裝，需要安裝。
@$ORACLE_HOME/rdbms/admin/cataudit.sql  
審計(jì)表安裝在SYSTEM表空間。所以要確保SYSTEM表空間又足夠的空間存放審計(jì)信息。

--查詢(xún)審計(jì)表aud$情況：
SQL> col owner for a10
SQL> col table_name for a15
SQL> col TABLESPACE_NAME for a15
SQL> alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';

Session altered.

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED from dba_tables where table_name='AUD$';

OWNER      TABLE_NAME      TABLESPACE_NAME STATUS   LAST_ANALYZED
---------- --------------- --------------- -------- -------------------
SYS        AUD$            SYSTEM          VALID    2018-01-24 22:01:31

SQL> col segment_name for a15
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME,BYTES/1024 size_k from dba_segments where SEGMENT_NAME='AUD$';

OWNER      SEGMENT_NAME    PARTITION_NAME                 TABLESPACE_NAME     SIZE_K
---------- --------------- ------------------------------ --------------- ----------
SYS        AUD$                                           SYSTEM                 128


SQL> conn scott/tiger
Connected.

SQL> create table temp as select * from user_objects;

Table created.

SQL> insert into temp select * from temp;

10 rows created.

SQL> commit;

Commit complete.

SQL> update temp set object_name='WANG' where object_id=10;

0 rows updated.

SQL> update temp set object_name='WANG' where object_id=87107;

2 rows updated.

SQL> commit;

Commit complete.

SQL> delete temp where rownum <10;

9 rows deleted.

SQL> commit;

Commit complete.

SQL> truncate table temp;

Table truncated.

SQL> drop table temp purge;

Table dropped.


--查詢(xún)審計(jì)表aud$：


SQL> select os_username,
       username,
       obj_name,
       action_name,
       audit_option,
       logoff_time,
       sessionid,
       os_process,
       instance_number,
       sql_text,
       sql_bind
  from dba_audit_trail
 where sql_text like '%TEMP%';

no rows selected

實(shí)驗(yàn)三：指定對(duì)t表進(jìn)行更新審計(jì)

登錄 scott 用戶(hù)，創(chuàng)建 t 表并開(kāi)啟 update 審計(jì)，使用 by access 子句，每次 update 都審計(jì)
SQL> conn scott/tiger;
Connected.
SQL> create table t(x int);

Table created.

SQL> insert into t values(9);

1 row created.

SQL> commit;

Commit complete. --在scott用戶(hù)下對(duì)t表進(jìn)行access級(jí)別的審計(jì)
SQL> audit update on t by access;  

Audit succeeded.

表示對(duì)t表的每次更新操作進(jìn)行審計(jì)（by access，每個(gè)符合審計(jì)的操作全部審計(jì)；by session，在一個(gè)會(huì)話(huà)中，同類(lèi)型的操作只審計(jì)一條）--使用綁定變量的 sql 進(jìn)行 update 測(cè)試

SQL>var v_num number;

SQL>exec :v_num:=1000;   （:v_num綁定變量，:v_num:=給綁定變量授予一個(gè)值）

PL/SQL procedure successfully completed.

SQL>update t set x=:v_num;

1 row updated.

SQL>commit;

commit complete.

--關(guān)閉審計(jì)
SQL> noaudit update on t;

Noaudit succeeded.

--查詢(xún)審計(jì)結(jié)果

set lines 200
col OS_USERNAME for a10
col USERNAME for a10
col OBJ_NAME for a10
col SQL_BIND for a10
col SQL_TEXT for a10
col OWNER for a10
col ACTION_NAME for a10
alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
select os_username,
       username,
       timestamp,
       owner,
       obj_name,
       action_name,
       sessionid,
       instance_number,
       os_process,
       transactionid,
       sql_bind,
       sql_text
  from dba_audit_trail
 where sql_text like '%T%';

結(jié)果說(shuō)明開(kāi)啟DB級(jí)的審計(jì)，必須指定對(duì)某個(gè)表或某個(gè)session（by access 或 by session）,才會(huì)對(duì)相應(yīng)操作進(jìn)行審計(jì)并記錄在dba_audit_trail表中

“audit審計(jì)方法是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！