Oracle 审计和测试操作

.1  相关参数

1.1.1  AUDIT_SYS_OPERATIONS参数

默认为false，当设置为true时，所有以sysdba,sysoper身份登录的用户的操作都会被记录至操作系统中,sysdba相关操作和普通用户不同，它是记录在操作系统中不会放在aud$表中，注意这个参数修改后需要重启才生效

1.1.2  AUDIT_TRAIL 参数

AUDIT_TRAIL  用于开启和禁用审计,默认值为DB，当值为db的情况不会记录相关操作的具体SQL,只有为DB,EXTENDED时才会记录具体执行什么SQL(aud$表中sql_text才不为空)，注意这个参数需要重启后才生效，这个参数有以下值可以设置，分别含义如下

Parameter Value	Meaning
DB	Enables database auditing and directs all audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail
XML	All elements of the AuditRecord node except Sql_Text and Sql_Bind will be printed to the operating system XML audit file.
DB,EXTENDED	Performs all actions of AUDIT_TRAIL=DB, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified. DB,EXTENDED captures the SQL triggered by an audit. 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, DATE, LONG, ROWID, and TIMESTAMP.
XML,EXTENDED	Does all actions of AUDIT_TRAIL=XML and also populates the SQL bind and SQL text CLOB-type columns of theSYS.AUD$ table, wherever possible. (These columns are populated only when this parameter is specified.)
OS	Enables database auditing and directs all audit records to an operating system file
NONE	Disables standard auditing (This value is the default.)

1.2  审计类型

分为审计按SQL语句审计，按权限审计，按对象审计和按网络进行审计

审计类型	作用
Statement	记录所有指定类型的审计语句，如 CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE和 DELETE TABLE等相关的语句.
Privilege	审计所有相关的系统权限,如AUDIT CREATE ANY TRIGGER, CREATE ANY TRIGGER等系统权限.
Object	根据指定的对象进行审计，如只审计emp表的delete操作.
Network	审计网络错误.

 

1.3  审计的触发条件

1.3.1  根据语句执行是否成功进行审计

不管是语句，权限和对象都可以根据语句执行是否成功在审计语句后面加判断词，语法如下：

在审计语句后面加WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL

如:

AUDIT CREATE TABLE WHENEVER NOT SUCCESSFUL;

AUDIT TRUNCATE TABLE WHENEVER NOT SUCCESSFUL;

1.3.2  根据指定用户进行审计

在做语句和权限进行审计的时候可以在审计语句后面加by再加指定用户名，多个用户用逗号隔开，例如下面语句为开启对scott,blake两个用户的所有select table和update table权限

AUDIT SELECT TABLE, UPDATE TABLE  BY scott, blake;

 

1.4  审计相关的视图

可以通过dba_views查看所有相关的视图

select * from dba_views where view_name like '%AUDIT%'

最常用的视图如下：

---审计系统视图

select  *  from  STMT_AUDIT_OPTION_MAP    -- 审计选项类型代码

select  *  from  AUDIT_ACTIONS    -- action代码

select  *  from  ALL_DEF_AUDIT_OPTS    -- 对象创建时默认的对象审计选项

---三种审计已启用审计选项

select  *  from  DBA_STMT_AUDIT_OPTS    -- 当前数据库系统审计选项

select  *  from  DBA_PRIV_AUDIT_OPTS    -- 当前权限审计选项

select  *  from  DBA_OBJ_AUDIT_OPTS     ---查看当前审计了哪些对象

---审计结果,也可以直接查询sys.aud$表

select  *  from  DBA_AUDIT_TRAIL  ---查看审计记录,调用的sys.aud$表

select  *  from  DBA_AUDIT_OBJECT   ---查看对象审计记录

select  *  from  DBA_AUDIT_SESSION  -- session审计记录

select  *  from  DBA_AUDIT_STATEMENT   -- 查看语句审计记录 

select  *  from  DBA_AUDIT_EXISTS    -- 使用BY AUDIT NOT EXISTS选项的审计

select  *  from  DBA_AUDIT_POLICIES    -- 审计POLICIES

select  *  from  DBA_COMMON_AUDIT_TRAIL  -- 标准审计+精细审计记录

1.5  设置默认表空间不为system

alter table aud$ move tablespace audit_tbs;
alter table aud$ move  LOB ("SQLBIND") store as ( tablespace audit_tbs);
alter table aud$ move  LOB ("SQLTEXT") store as ( tablespace audit_tbs);

 

 

1.6  测试过程

1.6.1  修改audit_trail参数并重启数据库

SQL> alter system set audit_trail=DB,EXTENDED  scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 1640484864 bytes

Fixed Size                  1345296 bytes

Variable Size             973080816 bytes

Database Buffers          654311424 bytes

Redo Buffers               11747328 bytes

Database mounted.

Database opened.

SQL>

1.6.2  测试按语句审计

（1）在sys用户下面开启语句审计

SQL> AUDIT delete any TABLE, UPDATE any TABLE,insert any table, ALTER any table BY eymit;

Audit succeeded.

SQL>

SQL> AUDIT table BY eymit;

Audit succeeded

 

（2）在eymit用户下创建相关的表和执行部分操作

create table eymit.T_EYMIT

(

  ID   NUMBER,

  NAME VARCHAR2(20)

);

SQL> insert into t_eymit values(1,'aaaa');

1 row inserted

SQL> insert into t_eymit values(2,'bbbbbb');

1 row inserted

SQL> insert into t_eymit values(3,'cccccc');

1 row inserted

SQL> commit;

Commit complete

SQL> update t_eymit set name='bbbbb2' where id=2;

1 row updated

SQL> commit;

Commit complete

SQL>

SQL> alter table T_EYMIT

  2    add constraint pk_eymit primary key (ID);

Table altered

（3）查看添加的审计

SQL> select * from DBA_STMT_AUDIT_OPTS where USER_NAME='EYMIT';

 

USER_NAME             PROXY_NAME             AUDIT_OPTION         SUCCESS    FAILURE

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

EYMIT                                   DELETE ANY TABLE        BY ACCESS   BY ACCESS

EYMIT                                   UPDATE ANY TABLE        BY ACCESS   BY ACCESS

EYMIT                                   INSERT ANY TABLE        BY ACCESS   BY ACCESS

EYMIT                                   ALTER ANY TABLE         BY ACCESS   BY ACCESS

 

SQL> select * from DBA_PRIV_AUDIT_OPTS  where USER_NAME='EYMIT';

 

USER_NAME           PROXY_NAME          PRIVILEGE               SUCCESS    FAILURE

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

EYMIT                                 DELETE ANY TABLE          BY ACCESS   BY ACCESS

EYMIT                                 UPDATE ANY TABLE          BY ACCESS   BY ACCESS

EYMIT                                 INSERT ANY TABLE          BY ACCESS   BY ACCESS

EYMIT                                 ALTER ANY TABLE           BY ACCESS   BY ACCESS

 

 

SQL> select owner,object_name,object_type from DBA_OBJ_AUDIT_OPTS  where wner='EYMIT';

 

OWNER                          OBJECT_NAME                    OBJECT_TYPE

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

SQL>

(4)查看有没有记录具体的SQL

测试下来发现，上面语句并没有记录相关的操作包括DDL，DML都不会记录具体执行什么SQL，但是会记录操作类型

SQL> SELECT T.USERID,T.SQLBIND,T.SQLTEXT FROM AUD$ T WHERE userid='EYMIT';

 

USERID         SQLBIND           SQLTEXT

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

EYMIT                                                                                                          

EYMIT                                                                                                           

EYMIT                                                                                                          

EYMIT                                                                                                           

(5)再次测试，发现还是没有记录具体的SQL

SQL> create table t_eymit2 as select * from t_eymit;

Table created

SQL>  alter table T_EYMIT2  add constraint pk_eymit2 primary key (ID);

Table altered

SQL> alter table T_EYMIT2 add name2 varchar2(20);

Table altered

在sys下面查看是否生成审计记录

SQL> select sql_text from DBA_AUDIT_TRAIL where username='EYMIT' ;

 

SQL_TEXT

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

 

SQL>

(6)重设audit_sys_operations,并进行测试

按文档说明audit_trail=DB,EXTENDED应该会记录具体的SQL，查看测试过程都没有什么问题，为什么不生成相关的详细SQL呢，会不会是权限问题呢，设置一下audit_sys_operations=true，并重启了数据库，发现sql_text记录了具体的SQL，为了验证是不是audit_sys_operations的问题，重新设置audit_sys_operations=false再重启数据库，sql_text还是会记录操作的SQL，说明不是audit_sys_operations的问题

 

SQL>  alter system set  audit_sys_operations=true  scope=spfile;

 

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area 1640484864 bytes

Fixed Size                  1345296 bytes

Variable Size             973080816 bytes

Database Buffers          654311424 bytes

Redo Buffers               11747328 bytes

Database mounted.

Database opened.

SQL>

在eymit用户下面执行创建表的操作

SQL> create table t_eymit3 as select * from t_eymit;

Table created

再使用sys用户查看审计信息，发现已经有记录了

SQL>  select sql_text from DBA_AUDIT_TRAIL where username='EYMIT' ;

SQL_TEXT

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

create table t_eymit3 as select * from t_eymit

SQL>

1.6.3  测试指定对象

使用sys用户执行下面操作

SQL> audit delete,insert,update on eymit.t_eymit by session;

Audit succeeded

SQL> delete FROM AUD$;

16 rows delete

SQL> commit;

在eymit用户下面作部分操作

SQL> update t_eymit set name='ffffff1' where id=5;

1 row updated

SQL> update t_eymit set name='ffffff2' where id=5;

1 row updated

SQL> commit;

在sys下面查看是否记录了相关的SQL

SQL> select sql_text from DBA_AUDIT_TRAIL where username='EYMIT' ;

 

SQL_TEXT

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

update t_eymit set name='ffffff1' where id=5

update t_eymit set name='ffffff2' where id=5

SQL>

下面操作只记录新建对象的DDL操作，对新表的操作审计并没有记录,注意Oracle没有提供对schema中所有对象的审计功能，只能一个一个对象审计，对于后面创建的对象，Oracle则提供on default子句来实现自动审计，比如执行audit drop on default by access后，对于随后创建的对象的drop操作都会审计。但这个default会对之后创建的所有数据库对象有效，似乎没办法指定只对某个用户创建的对象有效

在sys下面清除的审计

SQL> delete FROM AUD$;

SQL> commit;

在eymit用户下新表上执行DML操作

SQL> create table eymit2 as select * from t_eymit;

Table created

SQL> delete eymit2 where id=5;

1 row deleted

SQL> commit;

Commit complete

SQL> insert into eymit2 values(5,'cccccc5');

1 row inserted

SQL> commit;

Commit complete

 

使用sys查看审计

SQL> select sql_text from DBA_AUDIT_TRAIL where username='EYMIT' ;

 

SQL_TEXT

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

create table eymit2 as select * from t_eymit

 

SQL>