Oracle 审计 audit

--审计 AUDIT
1）审查可疑活动
如：所有表的删除
2）监视和收集关于指定数据库活动的数据
如：哪些表被经常修改

--打开和禁用审计
audit/noaudit

--审计的类型
语句审计 如：audit create table
权限审计
对象审计 如：audit select on scott.dept

--audit_trail参数
none：禁用数据库审计。此参数为默认值。
os：把审计记录写到一个操作系统文件（操作系统审计跟踪）中。
db：把审计记录写入数据库审计跟踪（存储在SYS.AUD$表中），dba_audit_trail。
db_bextended：把所有审计记录发送到数据库审计跟踪（SYS.AUD$），此外，填充SQLBIND和SQLTEXT CLOB列。
xml：指定数据库审计，进入OS文件的是XML格式的审计记录。
xml_extended：与XML设置相同，另外还记录所有审计跟踪列，包括SQLTEXT和SQLBIND。

audit_file_dest 指定审计文件放置目录。
 
alter system set audit_trail=OS scope=spfile           (需要重启数据库)
alter system set audit_trail=db_extended scope=spfile  (需要重启数据库)可以查看详细语句的话

sys.aud$表和操作系统文件存储审计记录
select USERID,USERHOST,SQLTEXT from sys.aud$ where userid='HR';

--审计命令
audit session whenever successful
audit session whenever not sucessful

--例子
audit create table by scott;
(noaudit create table by scott; 关闭审计）

create table audit_test (c1 int);
SYS@dbtest> audit insert,update on scott.audit_test by access whenever successful;
Audit succeeded.

SYS@dbtest

> select object_name,object_type,alt,del,ins,upd,sel
  2   from dba_obj_audit_opts;
OBJECT_NAME                    OBJECT_TYPE             ALT   DEL   INS   UPD   SEL
------------------------------ ----------------------- ----- ----- ----- ----- -----
AUDIT_TEST                     TABLE                   -/-   -/-   A/-   A/-   -/-

--相关视图
dba_audit_trail
user_audit_trail
dba_audit_object

--例子
SYS@ test11g> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/test11g/
                                                 adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

SYS@ test11g> alter system set audit_trail=db_extended scope=spfile;
System altered.

SYS@ test11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ test11g> startup
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1338336 bytes
Variable Size             444597280 bytes
Database Buffers          176160768 bytes
Redo Buffers                5636096 bytes
Database mounted.
Database opened.
SYS@ test11g> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/test11g/
                                                 adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB_EXTENDED

SYS@ test11g> audit create session by ikki;
Audit succeeded.

SYS@ test11g> audit resource by ikki;
Audit succeeded.

[oracle@serv11 app]$ sqlplus ikki/ikki

IKKI@ test11g> create table t1(c1 int, c2 int, c3 int);
Table created.

SYS@ test11g> audit insert, update on ikki.t1 by access whenever successful;
Audit succeeded.

IKKI@ test11g> insert into t1 values(1,2,3);
1 row created.

IKKI@ test11g> commit;
Commit complete.

IKKI@ test11g> select * from t1;
        C1         C2         C3
---------- ---------- ----------
         1          2          3

IKKI@ test11g> update t1 set c3=2
  2  where c1=1;
1 row updated.

IKKI@ test11g> commit;
Commit complete.

SYS@ test11g> set linesize 100
SYS@ test11g> col username for a8
SYS@ test11g> col action_name for a12
SYS@ test11g> col priv_used for a12
SYS@ test11g> col extended_timestamp for a18
SYS@ test11g> col sql_text for a30

SYS@ test11g> select username, action_name, priv_used, extended_timestamp, sql_text
  2  from dba_audit_object   
  3  where username='IKKI';

USERNAME ACTION_NAME  PRIV_USED    EXTENDED_TIMESTAMP SQL_TEXT
-------- ------------ ------------ ------------------ ------------------------------
IKKI     UPDATE                    27-NOV-13 11.03.13 update t1 set c3=2
                                   .905728 AM +08:00  where c1=1

IKKI     INSERT                    27-NOV-13 11.02.13 insert into t1 values(1,2,3)
                                   .252535 AM +08:00

IKKI     CREATE TABLE CREATE TABLE 27-NOV-13 11.00.28 create table t1(c1 int, c2 int
                                   .661674 AM +08:00  , c3 int)

SYS@ test11g> noaudit create session by ikki;
Noaudit succeeded.

SYS@ test11g> noaudit resource by ikki;
Noaudit succeeded.

SYS@ test11g> noaudit insert, update on ikki.t1;
Noaudit succeeded.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27633655/viewspace-1081552/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27633655/viewspace-1081552/