管理审计（二）--审计的对象

ORACLE支持三种审计类型：

      语句审计，对某种类型的SQL语句审计，不指定结构或对象。

      特权审计，对执行相应动作的系统特权的使用审计。

      对象审计，对一特殊模式对象上的指定语句的审计。

1、语句审计

语句审计用于审计特定语句相关的sql操作指定了语句审计后只对将来的会话起作用，对当前会话不生效。

(1）设置语句审计

03:23:33 SQL> audit table;

Audit succeeded.

将审计除sys用户外所有的用户的create  table、drop  table 等动作。

SQL> audit table by tom;——对用户tom进行语句审计

Audit succeeded.

SQL> audit table by tom whenever successful;——对用户tom进行成功的语句审计，失败的语句不审计

Audit succeeded.

(2）显示已经设置的设计选项

SQL> select user_name,audit_option from dba_stmt_audit_opts;

USER_NAME       AUDIT_OPTION

--------------- ------------------------------

TABLE

TOM             TABLE

(3)执行操作

11:08:54 SQL> conn scott/tiger

Connected.

11:09:02 SQL> drop table dept1 purge;

drop table dept1 purge

*

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

11:09:12 SQL> drop table dept1 cascade purge;

drop table dept1 cascade purge

*

ERROR at line 1:

ORA-00905: missing keyword

11:09:28 SQL> drop table dept1 cascade;

drop table dept1 cascade

*

ERROR at line 1:

ORA-00905: missing keyword

11:09:31 SQL> drop table dept1 cascade constraint purge;

Table dropped.

11:09:38 SQL> drop table emp1 purge;

Table dropped.

11:09:46 SQL> create table emp1 as select * from emp;

Table created.

11:11:50 SQL> conn tom/tom

Connected.

11:12:52 SQL> create table t01 (id int);

Table created.

11:13:07 SQL> drop table t01 purge;

Table dropped.

(4）显示审计跟踪结果

11:13:11 SQL> conn /as sysdba

Connected.

11:13:29 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

11:14:31 SQL> col username for a10

11:14:35 SQL> col obj_name for a10

11:14:42 SQL>select USERNAME,TIMESTAMP,OBJ_NAME,ACTION_NAME from dba_audit_trail

USERNAME   TIMESTAMP           OBJ_NAME   ACTION_NAME

---------- ------------------- ---------- ----------------------------

SCOTT      2011-08-11 11:09:12 DEPT1      DROP TABLE

SCOTT      2011-08-11 11:09:26 DEPT1      DROP TABLE

SCOTT      2011-08-11 11:09:31 DEPT1      DROP TABLE

SCOTT      2011-08-11 11:09:39 DEPT1      DROP TABLE

SCOTT      2011-08-11 11:09:47 EMP1       DROP TABLE

SCOTT      2011-08-11 11:09:59 EMP1       CREATE TABLE

TOM        2011-08-11 11:13:07 T01        CREATE TAB

——审计结果存放到aud$的基表里，通过dba_audit_trail 视图查看

11:14:42 SQL> select count(*) from aud$;

COUNT(*)

----------

8

(5）禁止语句审计

——删除审计结果

11:17:24 SQL> delete from aud$;

8 rows deleted.

——关闭审计

11:17:35 SQL> noaudit table ;

Noaudit succeeded.

2、权限审计

权限审计:用于审计与系统权限相关的SQL操作。

(1）指定权限审计

03:32:48 SQL> audit create sequence;

Audit succeeded.

(2）执行相关权限的操作

03:34:31 SQL> create sequence seq01;

Sequence created.

03:34:33 SQL> drop sequence seq01;

Sequence dropped.

(3）查询审计结果

03:41:39 SQL> select username,action_name,obj_name,

03:42:06   2    to_char(timestamp,'YYYY-MM-DD HH24:MI:SS')

03:42:17   3    FROM dba_audit_trail;

USERNAME                 ACTION_NAME                  OBJ_NAME              TO_CHAR(TIMESTAMP,'

----------------------- ---------------------------- ----------------       -------------------

SYSTEM                         CREATE TABLE                 T1              2011-03-03 03:26:18

SYSTEM                         DROP TABLE                   T1              2011-03-03 03:26:28

(4）显示已经设置的权限审计选项

03:43:36 SQL> select user_name,privilege from dba_priv_audit_opts;

USER_NAME                      PRIVILEGE

------------------------------ ----------------------------------------

CREATE SEQUENCE

(5）禁止权限审计

03:44:22 SQL> noaudit create sequence;

Noaudit succeeded.

3、对象审计

用于审计特定对象上的sql操作

(1）设置审计对象

03:44:24 SQL> audit all on scott.emp;

Audit succeeded.

(2）执行相关操作

03:48:09 SQL> select * from scott.emp

03:48:18   2    where empno=7788;

EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       2000                    10

03:48:25 SQL> update scott.emp set sal=5000 where empno=7788;

1 row updated.

03:48:45 SQL> delete from scott.emp where empno=7788;

1 row deleted.

(3）查看审计记录

03:51:12 SQL> select username,ses_actions,obj_name,

03:51:30   2    to_char(timestamp,'yyyy-mm-dd HH24:MI:SS')

03:51:32   3   FROM dba_audit_trail;

USERNAME                       SES_ACTIONS         OBJ_NAME                                            TO_CHAR(TIMESTAMP,'

------------------------------ ------------------- ------------------------------------------------- --------------------

SYSTEM                                             T1                                                  2011-03-03 03:26:18

SYSTEM                                             T1                                                  2011-03-03 03:26:28

SYSTEM                         ---------S------    EMP                                                 2011-03-03 03:48:09

SYSTEM                         ----------S-----    EMP                                                 2011-03-03 03:48:45

SYSTEM                         ---S------------    EMP                                                 2011-03-03 03:49:04

其中S表示successful ，表示在这个位置操作是成功的，F表示failure 失败，B表示both，两者都有。

(4）显示已经设置的审计项

03:52:09 SQL> select sel,ins,upd ,del from dba_obj_audit_opts

03:53:08   2    where owner='SCOTT' AND OBJECT_NAME='EMP';

SEL INS UPD DEL

--- --- --- ---

S/S S/S S/S S/S

22:48:21 SQL> AUDIT SELECT ON SCOTT.EMP;

Audit succeeded.

22:48:33 SQL>  select sel,ins,upd ,del from dba_obj_audit_opts

22:48:38   2  where owner='SCOTT' AND OBJECT_NAME='EMP';

SEL   INS   UPD   DEL

----- ----- ----- -----

S/S   -/-   -/-   -/-

(5)禁止审计对象

03:53:34 SQL> noaudit all on scott.emp;

Noaudit succeeded