审计

最新推荐文章于 2023-10-31 16:14:48 发布

weixin_34160277

最新推荐文章于 2023-10-31 16:14:48 发布

阅读量267

点赞数

文章标签：数据库操作系统

什么是审计：

审计（audit）用于监视用户所执行的数据库操作，并且Oracle会将审计跟踪结果存放到OS文件(默认位置为：$ORACLE_BASE/admin /$ORACLE_SID/adump/)，或数据库（存储在 system 表空间中的 SYS.AUD$表中，可通过视图 dba_audit_trail查看）中。审计可以提提供有用的信息，用户揭示权限的滥用和误用。当需要一定的粒度时，DBA 可以使用细粒度的审计来监控对表中某些行或者列的访问，而不仅仅

是是否访问表。

在 Oracle 11 g 中，审计功能(AUDIT_TRAIL) 是默认开启的。审计数据默认存放在 system 表空间下的 AUD$ 审计字典基表上。开启审计数据库会增加消耗，降低业务性能，因此，如果bushI很必要，在安装好数据库后，可适当选择关闭数据库审计功能。Oracle 公司还推荐使用基于OS 文件的审计日志记录方式（OS audit trail files ）.当 AUDIT_TRAIL 设置为OS时，审计记录文件将在 AUDIT_FILE_DEST 参数所指定的目录中生成。

SYS@orcl> show parameter audit_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
SYS@orcl> ho ls /u01/app/oracle/admin/orcl/adump
orcl_ora_10103_1.aud  orcl_ora_12102_2.aud  orcl_ora_12729_2.aud  orcl_ora_13005_1.aud  orcl_ora_13298_1.aud  orcl_ora_4600_1.aud  orcl_ora_8862_1.aud  orcl_ora_9535_1.aud
orcl_ora_10103_2.aud  orcl_ora_12195_1.aud  orcl_ora_12791_1.aud  orcl_ora_13005_2.aud  orcl_ora_13319_1.aud  orcl_ora_4649_1.aud  orcl_ora_8869_1.aud  orcl_ora_9536_1.aud
orcl_ora_10107_1.aud  orcl_ora_12245_1.aud  orcl_ora_12813_1.aud  orcl_ora_13067_1.aud  orcl_ora_13354_1.aud  orcl_ora_4888_1.aud  orcl_ora_8875_1.aud  orcl_ora_9536_2.aud
orcl_ora_10107_2.aud  orcl_ora_12245_2.aud  orcl_ora_12822_1.aud  orcl_ora_13091_1.aud  orcl_ora_13360_1.aud  orcl_ora_4909_1.aud  orcl_ora_8876_1.aud  orcl_ora_9601_1.aud
orcl_ora_10171_1.aud  orcl_ora_12309_1.aud  orcl_ora_12823_1.aud  orcl_ora_13110_1.aud  orcl_ora_13362_1.aud  orcl_ora_4981_1.aud  orcl_ora_8904_1.aud  orcl_ora_9623_1.aud
orcl_ora_10193_1.aud  orcl_ora_12367_1.aud  orcl_ora_12823_2.aud  orcl_ora_13111_1.aud  orcl_ora_13363_1.aud  orcl_ora_4981_2.aud  orcl_ora_8912_1.aud  orcl_ora_9716_1.aud
orcl_ora_10313_1.aud  orcl_ora_12383_1.aud  orcl_ora_12884_1.aud  orcl_ora_13124_1.aud  orcl_ora_13364_1.aud  orcl_ora_5046_1.aud  orcl_ora_8912_2.aud  orcl_ora_9797_1.aud
orcl_ora_10807_1.aud  orcl_ora_12383_2.aud  orcl_ora_12922_1.aud  orcl_ora_13125_1.aud  orcl_ora_13370_1.aud  orcl_ora_5069_1.aud  orcl_ora_8976_1.aud  orcl_ora_9806_1.aud
orcl_ora_10807_2.aud  orcl_ora_12421_1.aud  orcl_ora_12923_1.aud  orcl_ora_13126_1.aud  orcl_ora_13376_1.aud  orcl_ora_6382_1.aud  orcl_ora_9046_1.aud  orcl_ora_9856_1.aud
orcl_ora_10884_1.aud  orcl_ora_12421_2.aud  orcl_ora_12969_1.aud  orcl_ora_13129_1.aud  orcl_ora_13377_1.aud  orcl_ora_6395_1.aud  orcl_ora_9048_1.aud  orcl_ora_9856_2.aud
orcl_ora_10907_1.aud  orcl_ora_12447_1.aud  orcl_ora_12971_1.aud  orcl_ora_13130_1.aud  orcl_ora_13381_1.aud  orcl_ora_6397_1.aud  orcl_ora_9048_2.aud  orcl_ora_9921_1.aud
orcl_ora_11464_1.aud  orcl_ora_12470_1.aud  orcl_ora_12987_1.aud  orcl_ora_13135_1.aud  orcl_ora_13403_1.aud  orcl_ora_6402_1.aud  orcl_ora_9112_1.aud  orcl_ora_9943_1.aud
orcl_ora_11464_2.aud  orcl_ora_12486_1.aud  orcl_ora_12990_1.aud  orcl_ora_13135_2.aud  orcl_ora_13404_1.aud  orcl_ora_7284_1.aud  orcl_ora_9134_1.aud  orcl_ora_9983_1.aud
orcl_ora_11544_1.aud  orcl_ora_12531_1.aud  orcl_ora_12992_1.aud  orcl_ora_13197_1.aud  orcl_ora_13422_1.aud  orcl_ora_8365_1.aud  orcl_ora_9147_1.aud
orcl_ora_11957_1.aud  orcl_ora_12531_2.aud  orcl_ora_12995_1.aud  orcl_ora_13221_1.aud  orcl_ora_13429_1.aud  orcl_ora_8365_2.aud  orcl_ora_9149_1.aud
orcl_ora_11957_2.aud  orcl_ora_12595_1.aud  orcl_ora_12997_1.aud  orcl_ora_13232_1.aud  orcl_ora_16684_1.aud  orcl_ora_8429_1.aud  orcl_ora_9149_2.aud
orcl_ora_12021_1.aud  orcl_ora_12617_1.aud  orcl_ora_13002_1.aud  orcl_ora_13233_1.aud  orcl_ora_4594_1.aud   orcl_ora_8451_1.aud  orcl_ora_9213_1.aud
orcl_ora_12102_1.aud  orcl_ora_12729_1.aud  orcl_ora_13003_1.aud  orcl_ora_13233_2.aud  orcl_ora_4594_2.aud   orcl_ora_8831_1.aud  orcl_ora_9235_1.aud

SYS@orcl>

View Code

SYS@orcl> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl/adu
                                                 mp
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
SYS@orcl>

View Code

审计的作用

对 Oracle DBA 而言，审计就是监视和记录用户对数据库所作的操作，审计记录可以告
诉你用户对数据库做了什么操作，使用了哪些系统权限，在高峰时期有多少用户在使用数据
库，每个会话平均持续多长时间以及其它有关事实。
审计能帮助 DBA 完成的操作类型包括： • 监视用户活动，记录企图闯入数据库的失败尝试。 • 确定最繁忙的表，以便对它作额外的调整。 • 调查对关键表的可疑的更改。 • 获得数据库的使用信息，规划资源消耗。通过对数据库审计，可以帮助 DBA 更好地保证数据库的安全以及数据库的高效运行。
审计记录既可以存放到 SYS 模式的 AUD$表中，也可以存放到操作系统文件中。

审计的激活

我们知道，为了调查数据库可疑活动或收集活动数据，可以使用审计。但是对数据库的
审计是需要激活的，激活审计是通过设置初始化参数 AUDIT_TRAIL 来完成的。初始化参数
AUDIT_TRAIL 具有三种取值： • DB 或 TRUE：表示激活审计并将审计记录存放到 SYS.AUD$表中。 • OS：激活审计并将审计记录存放到操作系统文件中。 • FALSE 或 NONE：表示禁止审计。
该初始化参数没有默认值。
需要注意的是，特权用户操作(如启动、关闭数据库等)总是被审计，不需要激活的，并
且其审计信息会存放到警告文件中，而不是存放在审计记录中

审计日志文件存放位置

查看审计日志文件路径信息

[oracle@localhost install]$ cd /u01/app/oracle/admin/
[oracle@localhost admin]$ ls
orcl
[oracle@localhost admin]$ cd orcl/
[oracle@localhost orcl]$ ls
adump  dpdump  pfile
[oracle@localhost orcl]$ cd adump/
[oracle@localhost adump]$ cd ../dpdump/
[oracle@localhost dpdump]$ cd ../pfile/
[oracle@localhost pfile]$ cd ../adump/
[oracle@localhost adump]$ pwd
/u01/app/oracle/admin/orcl/adump
[oracle@localhost adump]$

查看审计日志文件内容

审计类型

----------审计

语句审计

1：语法：

    sql_statement_clause: 包含很多条不同的信息，例如 希望审计的sql语句类型及审计什么人。

    by session：表示每次会话审计一次

    by access：在每次动作发生时都对其进行审。即：表示每次访问都要审计一次

    whenever  successful：表示这条sql是执行成功被审计

    whenever  not successful: 表示这条sql是执行失败的时候被审计

2：示例：audit create table by scott by access;

[root@localhost ~]# su - oracle
[oracle@localhost ~]$ rlwrap sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 26 23:04:40 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl> alter system set audit_trail=db_extended scope=spfile;

System altered.

SYS@orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1221992448 bytes
Fixed Size                  1344596 bytes
Variable Size             788532140 bytes
Database Buffers          419430400 bytes
Redo Buffers               12685312 bytes
Database mounted.
Database opened.
SYS@orcl> show parameter adudit_trail
SYS@orcl> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED
SYS@orcl> alter user scott account unlock identified by scott;

User altered.

SYS@orcl> audit create table by scott by access;

Audit succeeded.

SYS@orcl> select user_name,audit_option,success,failure from dba_stmt_audit_opts where user_name='SCOTT';

USER_NAME                      AUDIT_OPTION
------------------------------ ----------------------------------------
SUCCESS    FAILURE
---------- ----------
SCOTT                          CREATE ANY TABLE
BY ACCESS  BY ACCESS

SCOTT                          CREATE TABLE
BY ACCESS  BY ACCESS


SYS@orcl> CONN SCOTT/SCOTT
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
@> conn scott/scott
Connected.
SCOTT@orcl> create table tt_audit (id int);

Table created.

SCOTT@orcl> conn / as sysdba;
Connected.
SYS@orcl> select user_name,audit_option,success,failure from dba_stmt_audit_opts where user_name='SCOTT';

USER_NAME                      AUDIT_OPTION
------------------------------ ----------------------------------------
SUCCESS    FAILURE
---------- ----------
SCOTT                          CREATE ANY TABLE
BY ACCESS  BY ACCESS

SCOTT                          CREATE TABLE
BY ACCESS  BY ACCESS


SYS@orcl> select username ,to_char(timestamp,'yyyy/mm/dd hh24:mis:ss') timestamp ,obj_name,action_name,sql_text from dba_audit_trail where username ='SCOTT';
select username ,to_char(timestamp,'yyyy/mm/dd hh24:mis:ss') timestamp ,obj_name,action_name,sql_text from dba_audit_trail where username ='SCOTT'
                                   *
ERROR at line 1:
ORA-01821: date format not recognized


SYS@orcl> select username ,to_char(timestamp,'YYYY/MM/DD') timestamp ,obj_name,action_name,sql_text from dba_audit_trail where username ='SCOTT';

权限审计

1：什么是权限审计：

     审计系统权限具有与语句审计相同的基本语法，但审计系统权限是在 sql_statement_clause 中，而不是在语句中，指定系统权限。
 例如，将 alter tablespace 权限授予所有的DBA，生成审计记录。启用对这种权限的审计命令：

audit alter tablespace by access whenever successful;

每次成功使用 alter  tablespace 权限时，都会将一行内容添加到 SYS.AUD$.

    使用 sysdba 和 sysoper 权限或者以SYS 用户连接到数据库的系统管理员可以利用特殊的审计。为了启用这种额外的审计级别，可以设置初始参数 audit_sys_operations 为true.这种审计记录发送到与操作系统审计系统的位置。当使用其中一种权限时执行的所有的SQL语句，以及作为用户sys执行的任何SQL语句，都会发送到操作系统审计的位置。

alter  system set audit_sys_operations=true scope = spfile;

2：语法：

3：示例： audit create table by scott by access;

模式对象审计

audit select,insert,delete on scott.dept by access;
noaudit select,insert,delete on scott.dept;

细粒度审计

DBMS_FGA.ADD_POLICY (
   object_schema      =>  'scott',
   object_name        =>  'emp',
   policy_name        =>  'mypolicy1',
   audit_condition    =>  'sal < 100',
   audit_column       =>  'comm,sal',
   handler_schema     =>   NULL,
   handler_module     =>   NULL,
   enable             =>   TRUE,
   statement_types    =>  'INSERT, UPDATE',
   audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED,
   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);

开启审计

SYS@orcl> alter system set audit_trail=db_extended scope=spfile;

System altered.

SYS@orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1221992448 bytes
Fixed Size                  1344596 bytes
Variable Size             788532140 bytes
Database Buffers          419430400 bytes
Redo Buffers               12685312 bytes
Database mounted.
Database opened.
SYS@orcl>

View Code