ORACLE 审计

oracle本地审计51Testing软件测试网sono_F{Hv

51Testing软件测试网)V)Uy5N]1`F
先说明一下审记记录的存储方式,51Testing软件测试网-b\ r2pc6i}
分为两种：
:^ID+E)G@t0一种是存储在操作系统文件中,外部审计文件存放位置为/oracle安装目录/admin/arcsight/adump/(此arcsight指数据库SID),
3@Z7Ei/m3t0一种是存储在system表空间中的SYS.AUD$表中(可通过视图dba_audit_trail查看).51Testing软件测试网 l6Qm.[3j~ __a

F+D9~ZLk*bs0默认情况下审计是没有开启的,但也有缺省审计,不论当前是否已经开启的审计功能,数据库都会把一些数据库相关的操作写入外部审计文件中(注意：不是写入SYS.AUD$表)

*g
x+c#Tww0x0一、对audit进行设置51Testing软件测试网s5?"Q*E8]`l
-------------------------------------------------------------------------------------------------------------------51Testing软件测试网g1cNT:xC-Yf

(\:Y4T
H9e`~f01.需要对sys用户进行审计,需要设置AUDIT_SYS_OPERATIONS = TRUE,该参数默认值为false,为不进行审计.当对其设计时,所有的信息将记录入操作系统中而不记录入SYS.AUD$51Testing软件测试网uK}?}E;X'^B
sqlplus " / as sysdba"51Testing软件测试网:ayZ8EAFu] I
sql>ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;
t@\JB|0可能需要重启数据库51Testing软件测试网+I.OQ%gb}.N

注:当记录入操作系统中时,其参数控制为AUDIT_FILE_DEST51Testing软件测试网0Y:z,q
a:u

2.需要对audit_trail参数进行设置.该参数有三个值可以设置,默认为false,为不进行审计.当设置为OS时,对数据库进行设计的信息记录到操作系统中,当为DB时记录入sys.aud$中.

查看相关审计参数:
3G+a(YW \1~v0|1U0show parameter aud;51Testing软件测试网4XrB4?$L \5Kw

8Z5U5T)_ E,e$E5E0结果:51Testing软件测试网Hl3J:c]#h
 NAME                                 TYPE        VALUE
h n"n `v!N I4y0------------------------------------ ----------- ------------------------------
$qa%Dxlw0audit_file_dest                      string      /oracle/admin/arcsight/adump
q XstJ!_M6aeQ0audit_sys_operations                 boolean     FALSE51Testing软件测试网#UhC]UOM
audit_syslog_level                   string51Testing软件测试网}[7ID`'T:q1q
audit_trail                          string      NONE51Testing软件测试网'St(r5~2P+\V

cq(N)z6Gg0可以看出audit_sys_operations初始状态是false,audit_trail为none,51Testing软件测试网-O;M6PAP'x`

6x \&sc2}+s2s0开启system表空间中的SYS.AUD$表审计
,W QHZ}$D,qY0cd /oracle/admin/arcsight/pfile/51Testing软件测试网n{!}bbY"v
vim init.ora.832007154919 在最后一行加入AUDIT_TRAIL=DB,EXTENDED51Testing软件测试网 C&^#S"e \Qi _
ln -s /oracle/admin/arcsight/pfile/init.ora.832007154919  /oracle/product/10.2.0/dbs/initarcsight.ora51Testing软件测试网zG.H's3[g2j"Ah4\
重启数据库51Testing软件测试网0m pS]uh-xo gU
sqlplus "/ as sysdba"
o(aA sF/C1[.`,[0sql>shutdown immediate51Testing软件测试网7JktF'Ls
sql>startup51Testing软件测试网3u8ke*I!f e @

sql>show parameter aud;51Testing软件测试网ad u]9WH
可以看到51Testing软件测试网)c4@"E-`x2Y)Fsqe
audit_trail                          string      DB, EXTENDED

这时查看dba_audit_trail,里面为空,是因为还没有添加被审计的对象51Testing软件测试网ZoRX sJA/eS
可以通过DBA_STMT_AUDIT_OPTS,查看当前数据库审计选项.

二、审计
8\^3f-_Jp'eP0---------------------------------------------------------------------------------------------------------------51Testing软件测试网F9X%aQ7tLv~Wtr

下面,可以开始试验了,
E j!|e1ko/[-PD[0添加审计用户test
$cvEY
~fkz`0sql>audit all by test51Testing软件测试网T@:_:{|E a;kE.gG

i}E*~$ts)\(?0查看 DBA_STMT_AUDIT_OPTS51Testing软件测试网Bz'{9L8N
USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE
0~C.ea ?i~s,|h0------------------------------ ------------------------------ ---------------------------------------- ---------- ----------51Testing软件测试网TdVrCau
TEST                                                          ALTER SYSTEM                             BY ACCESS  BY ACCESS
xri-Gxf0TEST                                                          SYSTEM AUDIT                             BY ACCESS  BY ACCESS
*clI9o-P0O3e&fb!Qn0TEST                                                          CREATE SESSION                           BY ACCESS  BY ACCESS51Testing软件测试网EA.l\7tZ p-t L\vL
TEST                                                          TABLE                                    BY ACCESS  BY ACCESS
#F ]3ff!Z0TEST                                                          CLUSTER                                  BY ACCESS  BY ACCESS51Testing软件测试网I(`$Bu$WJ2eN8K
TEST                                                          TABLESPACE                               BY ACCESS  BY ACCESS
O-~%u`/n0TEST                                                          USER                                     BY ACCESS  BY ACCESS
|f+f9Z#G\0TEST                                                          ROLLBACK SEGMENT                         BY ACCESS  BY ACCESS
x3h3RC,iq0TEST                                                          TYPE                                     BY ACCESS  BY ACCESS51Testing软件测试网4vtj C;meU
TEST                                                          INDEX                                    BY ACCESS  BY ACCESS
"{y6K~];K2W0TEST                                                          SYNONYM                                  BY ACCESS  BY ACCESS
I$^.fSZ\Q0TEST                                                          PUBLIC SYNONYM                           BY ACCESS  BY ACCESS51Testing软件测试网*I6Oi jpAJ1v._rA
TEST                                                          VIEW                                     BY ACCESS  BY ACCESS51Testing软件测试网6q VG!o/w }
TEST                                                          SEQUENCE                                 BY ACCESS  BY ACCESS51Testing软件测试网 x4ICTD
@n?
TEST                                                          DATABASE LINK                            BY ACCESS  BY ACCESS51Testing软件测试网F0L
wv!FS
TEST                                                          PUBLIC DATABASE LINK                     BY ACCESS  BY ACCESS51Testing软件测试网&sPgJ?r'RT/E3J
TEST                                                          ROLE                                     BY ACCESS  BY ACCESS
-I vL1rb;^*s@.O0TEST                                                          DIMENSION                                BY ACCESS  BY ACCESS
o-r9N.{4`7eX2D+nO0TEST                                                          PROCEDURE                                BY ACCESS  BY ACCESS51Testing软件测试网&V2LVuQ8S DS}~t
TEST                                                          TRIGGER                                  BY ACCESS  BY ACCESS

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE
/y9T^XTr/^6{0------------------------------ ------------------------------ ---------------------------------------- ---------- ----------51Testing软件测试网,?P
iK!g-_s8Z
TEST                                                          PROFILE                                  BY ACCESS  BY ACCESS51Testing软件测试网 ~-]}~3Z
TEST                                                          DIRECTORY                                BY ACCESS  BY ACCESS51Testing软件测试网4[9FV F
L
qMG^ sp
TEST                                                          MATERIALIZED VIEW                        BY ACCESS  BY ACCESS51Testing软件测试网p,OA:W T&Q^6p,N
TEST                                                          NOT EXISTS                               BY ACCESS  BY ACCESS51Testing软件测试网6x9H%Bt-t)Z9B
TEST                                                          SYSTEM GRANT                             BY ACCESS  BY ACCESS
{Y;J'cwYF9qe!U0TEST                                                          CONTEXT                                  BY ACCESS  BY ACCESS

以上是all 所包含的审计内容,我们关注一下AUDIT_OPTION中的table ,这里只审计create table,truncate table,drop table操作,如果要审计select table等,需要另外添加审计选项
8{5{P)C$W"O sc0sql>audit select table by test by access

1. by session与by access51Testing软件测试网;Hho"[jB5E
当设置为by session时,对每个session下每条审计记录只出现一次,by access对每次操作都进行记录,by access将会带来大量的记录.

v?e^Ych6J02. by user51Testing软件测试网g,T ?Lq"R9sz
如果在命令后面添加by user则只对user的操作进行审计,如果省去by 用户,则对系统中所有的用户进行审计(不包含sys用户).51Testing软件测试网q#FHP)a%i

3. WHENEVER [NOT] SUCCESSFUL
f(]%p QA#n
xH0如果在命令后添加whenever successful 则只审计操作成功的情况，反之加上not则只审计操作失败的情况

-o FQ7{;|S0例：51Testing软件测试网,iI~DD'd]/Z_5b

O*XS?\;s3X:P0AUDIT DELETE ANY TABLE;审计删除表的操作51Testing软件测试网-ZRxF&sD\6f.XeF

AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL;(限制：只审计删除失败的情况)

|:\M2oj)|h"r4H&dq0AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL;(限制：只审计删除成功的情况)51Testing软件测试网^~miRt*eV

AUDIT DELETE,UPDATE,INSERT ON user.table by test;审计test用户对表user.table的delete,update,insert操作

D|5iL/u@(e9y t
u0以test 用户进行操作51Testing软件测试网4]9m1Eu8o1N9K,p
执行一条select 操作51Testing软件测试网
E:n&b3b:YU*jl/a
在dba_audit_trail中可以看到这条sql已经被审计

添加更多的审计
DH@5ZOm.v0AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,ALTER TABLE,UPDATE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER  SUCCESSFUL;(所有表所有用户)51Testing软件测试网-vZ{1EQ9t_7FU*[VZ

三、撤消审计51Testing软件测试网gs0s6?~s(U0@
----------------------------------------------------------------------------------------------------------
{B;V/T3e(uS0如想撤消已经设置的审计选项，需要针对之前做过的每一个审计进行撤消51Testing软件测试网#IP-Sy\
noaudit all by test;
"E
V]"{fod ~+d4x3J+h.b*|[0noaudit select table by test;
R kW!\!O4~
uY!f0再查看 DBA_STMT_AUDIT_OPTS ，里面已经为空了，可以重新设置审计选项了。
f(d I.f.Dv+b5h*Z051Testing软件测试网&d-k)J)w#oCFQ