ITPub博客

首页 > 数据库 > Oracle > oracle审计

oracle审计

原创 Oracle 作者:sky850623 时间:2014-03-02 11:42:31 0 删除 编辑

1.启用数据库审计
SQL> alter system set audit_trail=db scope=spfile;
2.审计结果保存在哪里
  1)保存在数据库中(sys.aud$这个基表)
    如果把audit_trail=xml,也会记录在audit_file_dest指定的目录中,xml文件格式保存,查看v$xml_audit_trail视图
  2)保存在操作系统日志文件中
     a.如果是windows系统,保存在事件查看器
     b.如果是linux系统,由参数audit_file_dest指定的目录

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB


3.查看audit_trail这个参数的有效值有哪些
SQL> select * from v$parameter_valid_values where name like '%audit%';

       NUM NAME                    ORDINAL VALUE           ISDEFAULT
---------- -------------------- ---------- --------------- ----------
       907 audit_trail                   1 DB              FALSE
       907 audit_trail                   2 OS              FALSE
       907 audit_trail                   3 NONE            FALSE
       907 audit_trail                   4 TRUE            FALSE
       907 audit_trail                   5 FALSE           FALSE
       907 audit_trail                   6 DB_EXTENDED     FALSE
       907 audit_trail                   7 XML             FALSE
       907 audit_trail                   8 EXTENDED        FALSE
4.审计类型
 4.1标准审计
  a.权限审计(系统权限)
   查看有哪些权限审计-->dba_priv_audit_opts
   SQL> select * from dba_priv_audit_opts;  --没有记录

    no rows selected
    审计scott用户后再查看
SQL> audit create session by scott;  --审计scott用户

Audit succeeded.

SQL> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME                     PRIVILEGE                                SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
SCOTT                                                         CREATE SESSION                           BY ACCESS  BY ACCESS
默认成功或失败都审计.
scott用户成功登录
C:\Documents and Settings\Administrator>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 2 10:44:38 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
查看审计结果
SQL> select username,owner,audit_option,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME                       OWNER                          AUDIT_OPTION                             TO_CHAR(TIMESTAMP,'
------------------------------ ------------------------------ ---------------------------------------- -------------------
SCOTT                                                                                                  2014-03-02 10:44:39
scott登录失败也会记录
C:\Documents and Settings\Administrator>sqlplus scott/tigeer

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 2 10:47:00 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
SQL> select username,owner,audit_option,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME                       OWNER                          AUDIT_OPTION                             TO_CHAR(TIMESTAMP,'
------------------------------ ------------------------------ ---------------------------------------- -------------------
SCOTT                          SCOTT                                                                   2014-01-19 18:13:47
SCOTT                                                                                                  2014-03-02 10:44:39
SCOTT                                                                                                  2014-03-02 10:47:00
SQL> noaudit create session by scott;   --取消审计

Noaudit succeeded.
  2)只审计不成功的情况
  SQL> audit create session by scott whenever not successful;

Audit succeeded.

SQL> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME                     PRIVILEGE                                SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
SCOTT                                                         CREATE SESSION                           NOT SET    BY ACCESS
  b.对象审计
SQL> audit select on scott.t_3;

Audit succeeded.
SQL> col owner for a5
SQL> col object_name for a10
SQL> col object_type for a10
SQL> select * from dba_obj_audit_opts;

OWNER OBJECT_NAM OBJECT_TYP ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL   UPD   REF EXE   CRE   REA   WRI   FBK
----- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- ----- ----- ----- ----- -----
SCOTT T_3        TABLE      -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   S/S   -/-   -/- -/-   -/-   -/-   -/-   -/-

S(session): 同一个会话查询多次只有一条审计记录,默认是by session;
SQL> noaudit select on scott.t_3;   --取消审计
Noaudit succeeded.
SQL> audit select on scott.t_3 by access;

Audit succeeded.

SQL> select * from dba_obj_audit_opts;

OWNER OBJECT_NAM OBJECT_TYP ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL   UPD   REF EXE   CRE   REA   WRI   FBK
----- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --- ----- ----- ----- ----- -----
SCOTT T_3        TABLE      -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   -/-   A/A   -/-   -/- -/-   -/-   -/-   -/-   -/-

A(access): 在同一个会话,执行同一个select语句多次会记录多条审计结果,执行两次select就会有两条审计记录.
c.语句审计
 SQL> select * from dba_stmt_audit_opts;
4.2强制审计
  不管数据库审计是否启用一些数据库相关的行为总会被写到操作系统日志中,以下操作会被记录:
    a.启动实例
    b.关闭实例
    c.管理员权限连接数据库
 4.3细粒度审计

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

下一篇: oracle scheduler任务
请登录后发表评论 登录
全部评论

注册时间:2013-05-30

  • 博文量
    154
  • 访问量
    472933