oracle粗粒度审计

一.粗粒度审计功能。

1.审计级别：

语句级审计（stmt）：表示只审计某种类型的SQL语句，不指定结构或对象。

权限级审计（privs）：表示只审计执行相应动作的系统权限的使用情况。

实体级审计（obj）：表示只对指定模式上的实体指定语句的审计。

  根据用户语句的执行结果，审计语句分为成功语句的审计（Whenever Successful）、不成功语句的审计（Whenever NOT Successful）以及无论成功与否都进行审计（默认情况）。

  根据用户语句的执行次数，审计又分为对某一用户或全体用户的会话（By Session）审计、对某一用户或全体用户存取方式（By Access）的审计。

  当数据库的审计功能被启动后，在用户语句执行阶段，系统会自动产生审计信息。审计信息中包括审计的操作、用户执行的操作、操作日期、操作时间等信息。

2.初始化参数：

audit_trail=NONE|DB|DB,EXTENDED|XML|XML,EXTENDED|OS

AUDIT_TRAIL ValueDescription

DBDirects audit records to the database audit trail (the  SYS.AUD$ table), except for mandatory and SYS audit records, which are always  written to the operating system audit trail. (Table 9-1 describes the  location of the audit records for each type of auditing.) Use this setting  for a general database for manageability. DB is the default setting for the  AUDIT_TRAIL parameter.

If the database was started in read-only mode with AUDIT_TRAIL set to DB,  then Oracle Database internally sets AUDIT_TRAIL to OS. Check the alert log  for details.

See also "Managing the Database Audit Trail".

DB,EXTENDEDBehaves the same as AUDIT_TRAIL=DB, but also populates  the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when  available.

DB,EXTENDED enables you to capture the SQL statement used in the action that  was audited. You can capture both the SQL statement that caused the audit,  and any associated bind variables. However, be aware that you only can  capture data from the following column datatypes: CHAR, NCHAR, VARCHAR,  VARCHAR2, NVARCHAR2, NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE, LONG, ROWID,  DATE, TIMESTAMP, and TIMESTAMP WITH TIMEZONE. Also be aware that DB, EXTENDED  can capture sensitive data, such as credit card information. See also  "Auditing Sensitive Information".

If the database was started in read-only mode with AUDIT_TRAIL set to DB,  EXTENDED, then Oracle Database internally sets AUDIT_TRAIL to OS. Check the  alert log for details.

You can specify DB,EXTENDED in any of the following ways:

ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE; ALTER SYSTEM SET  AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE; ALTER SYSTEM SET  AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE; ALTER SYSTEM SET  AUDIT_TRAIL=EXTENDED,DB SCOPE=SPFILE; ALTER SYSTEM SET AUDIT_TRAIL=EXTENDED,  DB SCOPE=SPFILE;

However, do not enclose DB, EXTENDED in quotes, for example:

ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;

In previous releases, the setting was DB_EXTENDED. This setting has been  retained for backward compatibility but may not be available in future  releases.

OSDirects all audit records to an operating system file.

Oracle recommends that you use the OS setting, particularly if you are using  an ultra-secure database configuration. See "Advantages of the Operating  System Audit Trail" for more information. See also Example 9-3,  "Text File Operating System Audit Trail".

If you set AUDIT_TRAIL to OS, then set the following additional  initialization parameters:

AUDIT_FILE_DEST, which specifies the location of the operating system audit  record file. On UNIX systems, the default location is  $ORACLE_BASE/admin/$ORACLE_SID/adump. For better performance on UNIX systems,  set the AUDIT_FILE_DEST parameter to a directory on a disk that is locally attached  to the host running the Oracle Database instance. On Windows, the OS setting  writes the audit trail to the Application area of the Windows Event Viewer.

AUDIT_SYS_OPERATIONS, if you want to audit the top-level SQL statements  directly issued by users who have connected with the SYSDBA or SYSOPER  privilege. To enable this auditing, set AUDIT_SYS_OPERATIONS to TRUE.

If you set AUDIT_SYS_OPERATIONS to TRUE and AUDIT_TRAIL to XML or  XML,EXTENDED, then Oracle Database writes SYS audit records operating system  files in XML format.

AUDIT_SYSLOG_LEVEL, which writes SYS and standard OS audit records to the  system audit log using the SYSLOG utility. This option only applies to UNIX  environments. See "Configuring Syslog Auditing" for more  information.

See also "Managing the Operating System Audit Trail".

XMLWrites to the operating system audit record file in XML  format. Records all elements of the AuditRecord node given by the XML schema  in http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2.xsd except  Sql_Text and Sql_Bind to operating system XML audit files. (This .xsd file  represents the schema definition of the XML audit file. An XML schema is a  document written in the XML Schema language.)

See also "Advantages of the Operating System Audit Trail" and  Example 9-4, "XML File Operating System Audit Trail".

If you set the XML value, then also set the AUDIT_FILE_DEST parameter. For  all platforms, including Windows, the default location for XML audit trail  records is $ORACLE_BASE/admin/$ORACLE_SID/adump.

The XML AUDIT_TRAIL value does not affect syslog audit file. In other words,  if you have set the AUDIT_TRAIL parameter to XML, then the syslog audit  records will still be in text format, not XML file format.

You can control the output for SYS and mandatory audit records as follows:

To write SYS and mandatory audit files to operating system files in XML  format: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_SYS_OPERATIONS to  TRUE, but do not set the AUDIT_SYSLOG_LEVEL parameter.

To write SYS and mandatory audit records to syslog audit files and standard  audit records to XML audit files: Set AUDIT_TRAIL to XML or XML,EXTENDED, set  AUDIT_SYS_OPERATIONS to TRUE, and set the AUDIT_SYSLOG_LEVEL parameter.

XML, EXTENDEDBehaves the same as AUDIT_TRAIL=XML, but also includes  SQL text and SQL bind information in the operating system XML audit files.

You can specify XML,EXTENDED in either of the following ways:

ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE; ALTER SYSTEM SET  AUDIT_TRAIL='XML','EXTENDED' SCOPE=SPFILE;

NONEDisables standard auditing.

audit_sys_operations=false|true是否启动对sysdba,sysoper身份的用户的审计。

3.语法：

audit

stmt opts|privs opts|obj opts by users by session|access whenever [not]

successful;

4.审计类型：

语句级审计

  语句级审计表示只审计某种类型的SQL语句。可以审计某个用户，也可以审计所有用户的SQL语句。语句级审计的语法如下：

AUDIT

SQL语句选项 [by 用户名] [by session|access] [whenever [NOT] successful;

语句选项被审计的语句

CLUSTERCreate Cluster、Audit Cluster、Drop Cluster、Truncate Cluster

DATABASE LINKCreate Database Link、Drop Database Link

DIRECTORYCreate Directory、Drop Directory

INDEXCreate Index、Alter Index、Drop Index

PROCEDURECreate Function、Create Library、Create Package、Create Package Body、Create Procedure、Drop Function、Drop Library、Drop Package

PROFILECreate Profile、Alter Profile、Drop Profile

PUBLIC SYNONYMCreate Public Synonym、Drop Public Synonym

ROLECreate Role、Alter Role、Drop Role、Set Role

ROLLBACK SEGMENTCreate Rollback Segment、Alter Rollback Segment、Drop Rollback Segment

SEQUENCECreate Sequence、Drop Sequence

SESSIONConnect、Disconnect

SYNONYMCreate Synonym、Drop Synonym

SYSTEM AUDITAudit、Noaudit

SYSTEM GRANTGrant、Revoke

TABLECreate Table、Drop Table、Truncate Table

TABLESPACECreate Tablespace、Alter Tablespace、Drop Tablespace

TRIGGERCreate Trigger、Alter Trigger

USERSCreate User、Alter User、Drop User

TYPECreate Type、Create Type Body、Alter Type、Drop Type、Drop Type Body

VIEWCreate View、Drop View

ALTER SEQUENCEAlter Sequence

ALTER TABLEAlter Table

DELETE TABLEDelete from tables、views

EXECUTEExecute Function、Library、Package

GRANT SEQUENCEGrant privilege On sequence、Revoke privilege On sequence

GRANT TABLEGrant privilege on table、Revoke privilege on table

UPDATE TABLELock Table

在使用时，不需要写出全部的SQL语句，只要写出语句的选项即可。

“by 用户名”表示只审计指定用户的SQL语句，不审计其他用户。没有指出用户名时，则审计全体用户。

audit table by scott; 表示scott用户在执行Create

Table、Drop Table、Truncate

Table操作时将被审计。

audit table; 表示所有用户执行该类型的语句都被审计。

“by session”表示按会话方式审计，在每个会话中，相同的语句只审计一次。这是系统默认的方式。

“by access”表示按存取方式审计，每一次语句都将审计。

“whenever successful”表示只审计成功语句。

“whenever not successful”表示只审计不成功语句。

audit table by scott by access;（审计scott用户每一次对表的CREATE、DROP、Truncate操作）。

audit table by scott by session;（审计scott用户对表的CREATE、DROP、Truncate操作，相同的操作只记录一次）。

audit session by tax02 by session whenever not

successful;（审计tax02用户尝试连接数据库，但不成功的信息，相同的操作只记录一次）。

audit session by tax01,tax02;（审计tax01，tax02用户尝试连接数据库，成功的信息，相同的操作只记录一次）。

audit session whenever not successful;(审计尝试连接数据库，但不成功的信息)。

如果要了解对于哪些用户都进行了语句级审计及审计的选项，可以查询数据字典DBA_STMT_AUDIT_OPTS，该数据字典要以sys用户连接数据库查询。

当不再对用户进行审计时，可以使用noaudit命令，把用户进行的审计取消。

noaudit SQL语句或选项[by 用户名] [by session|access] [Whenever [NOT] Successful];

权限级审计

    权限级审计表示只审计某一个系统权限的使用情况。可以审计某个用户所使用的系统权限，也可以审计所有用户使用的系统权限。权限级审计的语法如下：

Audit 权限名称[by 用户名] [by session|Access] [Whenever [NOT] Successful];

例如：

audit delete any table whenever not successful;（审计所有用户不成功的DELETE ANY TABLE权限使用情况）。

audit create table whenever not successful;（审计所有用户不成功的CREATE TABLE权限使用情况）。

audit alter any table,alter any procedure by scott

by access whenever not successful;

audit create user by tax02 whenever not successful;

    如果要了解对哪些用户进行了权限级审计及审计选项，可以查询数据字典DBA_PRIV_AUDIT_OPTS，该数据字典必须以sys用户连接数据库进行查询。

当不再对用户的系统权限进行审计时，可以使用noaudit命令取消对用户所进行的审计。取消用户权限审计的命令如下：

noaudit 权限名称[by 用户名] [by session|access] [whenever [NOT] Successful];

例如：

noaudit alter any table,alter any procedure by

scott by access whenever not successful;

noaudit create user by tax02 whenever not

successful;

noaudit create table whenever not successful;

审计停止后，用户所进行的操作将不再记录。

实体级审计

    实体审计用于监视所有用户对某一指定用户的表的存取状况。数据库管理员关心的重点是哪些用户操作某一个指定用户的表。实体级审计的语法如下：

audit 实体选项on schema.实体名称[by session | access] [Whenever [NOT] Successful];

实体级审计中的实体选项及对实体操作的语句

TABLEVIEWSEQUENCEPROCEDURESNAPSHOTS

ALTER√√

DELETE√√

EXECUTE√

INDEX√

INSERT√√

REFERENCES√

SELECT√√√√

UPDATE√√

例如：

audit delete on scott.emp by access whenever

successful;（审计所有用户对scott.emp表所有成功的DELETE操作，每次操作都会记录）

audit delete on scott.dept by access whenever not

successful;（审计所有用户对scott.emp表所有不成功的DELETE操作，每次操作都会记录）

audit select on sys.tab;（审计所有用户对sys.tab表的SELECT操作，相同的操作只会记录一次）

audit update on scott.dept;

audit update,delete on hr.employees by access

whenever successful;

audit update,delete on hr.employees by user;（对user用户对hr.employees的update,delete进行审计）

    如果要了解对哪些用户的实体进行了实体级审计及审计选项，可以查询数据字典DBA_OBJ_AUDIT_OPTS，该数据字典必须以sys用户连接数据库进行查询。例如对于用户scott所进行的实体级审计信息，可以使用以下命令：

select

object_name,object_type,alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe,cre,rea,wri

from dba_obj_audit_opts where owner='SCOTT';

“-”表示没有设置该选项的审计。

“S”表示使用by

session选项进行审计。

“A”表示使用by

access选项进行审计。

“/”表示使用过whenever

successful、whenever

not successful选项值。

使用noaudit命令取消对用户实体所进行的审计。

例如：

noaudit delete on scott.emp by access whenever

successful;

noaudit delete on scott.dept by access whenever not

successful;

5.查看审计结果：

dba_audit_trail;

    DBA_AUDIT_TRAIL displays all

standard audit trail entries.

dba_audit_session;

   DBA_AUDIT_SESSION displays all

audit trail records concerning CONNECT and DISCONNECT.

dba_audit_object;

   DBA_AUDIT_OBJECT displays audit

trail records for all objects in the database.

6.查看创建了哪些审计：

dba_obj_audit_opts;（实体级审计）

dba_priv_audit_opts;（权限级审计）

dba_stmt_audit_opts;（语句级审计）

7.取消审计(将原有的audit语句的audit换成noaudit执行即可)：

noaudit all;

noaudit all privileges;

noaudit update,delete on table_name [by username];

8.删除审计记录：

delete from sys.aud$ where timestamp# 

9.常用视图：

下面介绍几个最常用的初始化参数和视图：

1).SYS.FGA_LOG$：如果audit_trail参数包含DB，那么审计记录会被记录在FGA_LOG$表中。

2).AUDIT_FILE_DEST初始化参数：设置审计操作系统文件的存放位置。

3).V$XML_AUDIT_TRAIL：如果audit_trail参数包含XML，那么审计记录会记录在AUDIT_FILE_DEST初始化参数指定的目的地下的XML文件中，Oracle会读取这些XML文件，生成V$XML_AUDIT_TRAIL动态性能视图，方便DBA查看审计详细信息。

4).DBA_AUDIT_POLICIES：详细记录了审计配置的策略信息。

5).DBA_FGA_AUDIT_TRAIL：查看到审计的SQL语句和绑定变量。

6).DBA_COMMON_AUDIT_TRAIL：包含V$XML_AUDIT_TRAIL动态性能视图的内容，是标准和细粒度审计记录。