两篇oracle审计的文章（转）

1.      启动方法

a)        修改参数audit_trail=true | db | os

        true：开启审计功能

        db：将审计结果放入表

        os：利用操作系统审计功能

        none：不进行审计

SQL> show parameter audit

 

NAME                                 TYPE        VALUE

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

audit_sys_operations                 boolean     FALSE

audit_trail                          string      NONE

transaction_auditing                 boolean     TRUE

SQL> alter system set audit_trail=true;

alter system set audit_trail=true

                 *

ERROR 位于第 1 行:

ORA-02095: 无法修改指定的初始化参数

 

 

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

 

系统已更改。

 

SQL> startup force

ORACLE 例程已经启动。

 

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

数据库装载完毕。

数据库已经打开。

SQL>

b)        生成相应数据字典脚本：\RDBMS\ADMIN\cataudit.sql

删除这些字典脚本为：\RDBMS\ADMIN\catnoadt.sql

通过比较9i和10g的cataudit.sql脚本，可以发现10g中审计方式最大到211号，而9i中最大到183号。但10g中空闲184-196号及203号审计方式，所以比9i共多28-14=14（种）：

insert into audit_actions values (197, 'PURGE USER_RECYCLEBIN');

insert into audit_actions values (198, 'PURGE DBA_RECYCLEBIN');

insert into audit_actions values (199, 'PURGE TABLESAPCE');

insert into audit_actions values (200, 'PURGE TABLE');

insert into audit_actions values (201, 'PURGE INDEX');

insert into audit_actions values (202, 'UNDROP OBJECT');

insert into audit_actions values (204, 'FLASHBACK DATABASE');

insert into audit_actions values (205, 'FLASHBACK TABLE');

insert into audit_actions values (206, 'CREATE RESTORE POINT');

insert into audit_actions values (207, 'DROP RESTORE POINT');

 

insert into audit_actions values (208, 'PROXY AUTHENTICATION ONLY') ;

insert into audit_actions values (209, 'DECLARE REWRITE EQUIVALENCE') ;

insert into audit_actions values (210, 'ALTER REWRITE EQUIVALENCE') ;

insert into audit_actions values (211, 'DROP REWRITE EQUIVALENCE') ;

2.      分类

a)        语句级审计

审计某种类型的SQL语句。

b)        权限级审计

审计某个权限的使用情况。

c)        实体级审计

监视所有用户对某一指定用户的表的存取情况。不分审计对象，dba关心的重点是哪些用户操作某一个指定用户的表。

3.      语句级审计

a)        语法：

Audit 语句或选项> [by用户名] [by session | access] [whenever [not] successful]

n       by user_name 审计指定用户。若不指定，则审计所有用户

n       by session按会话方式审计，每个会话中的相同语句只审计一次（系统默认）

n       by access按存取方式审计，每个语句都将审计

n       whenever successful只审计成功语句

n       whenever not successful只审计不成功语句

n       SQL语句选项

语句选项	被审计的语句
Cluster	Create/audit/drop/truncate cluster
Database link	Create/drop database link
Directory	Create/drop directory
Index	Create/alter/drop index
Procedure	Create function/library/package/package body/procedure Drop function/library/package
Profile	Create/alter/drop profile
Public synonym	Create/drop public synonym
Role	Create/alter/drop/set role
Rollback segment	Create/alter/drop rollback segment
Sequence	Create/drop sequence
Session	Connect disconnect
Synonym	Create/drop synonym
System audit	Audit Noaudit
System grant	Grant Revoke
Table	Create/drop/truncate table
Tablespace	Create/alter/drop tablespace
Trigger	Create/alter trigger
Users	Create/alter/drop user
Type	Create/alter/drop type Create/drop type body
View	Create/drop view
Alter sequence	Alter sequence
Alter table	Alter table
Delete table	Delete from table/view
Execute	Execute function/library/package
Guant sequence	Grant/revoke privilege on sequence
Grant table	Grant/revoke privilege on table
Update table	Lock table

b)        查询设置：

Select * from dba_stmt_audit_opts;

c)        取消审计：

Noaudit 语句或选项> [by 用户名] [whenever [not] successufl]

d)        举例：

请输入用户名:  / as sysdba

 

连接到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 – Production

SQL> audit table, alter table, update table by test by access whenever successful;

 

审计已成功。

 

SQL> set wrap off

SQL> set linesize 1000

SQL> select * from dba_stmt_audit_opts;

 

USER_NAME   PROXY_NAME   AUDIT_OPTION    SUCCESS    FAILURE

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

TEST                           TABLE            BY ACCESS  NOT SET

TEST                                     ALTER TABLE     BY ACCESS  NOT SET

TEST                           UPDATE TABLE    BY ACCESS  NOT SET

 

 

SQL> conn test/test

已连接。

SQL> create table test_a1 (a varchar2(10));

 

表已创建。

 

SQL> create table test_a2 (a varchar2(10));

 

表已创建。

 

SQL> alter table test_a2 add b varchar2(10);

 

表已更改。

 

SQL> insert into test_a1 select 1 from user_objects;

 

已创建34行。

 

SQL> insert into test_a2 select 1,2 from user_objects;

 

已创建34行。

 

SQL> commit;

 

提交完成。

 

SQL> SELECT TIMESTAMP#, userid, terminal, action#, returncode, obj$creator, obj$name, spare1, priv$used FROM sys.aud$;

TIMESTAMP#  USERID        TERMINAL             ACTION# RETURNCODE OBJ$CREATOR     OBJ$NAME    SPARE1                 PRIV$USED
----------- ------------- ----------------- ---------- ---------- --------------- --------------------------------------------
2007-09-08  TEST          YUECHAOTIAN                1          0 TEST            TEST_A2     YUECHAOTIAN\tianyc            40

SQL> select *from sys.dba_audit_object;

 

……

 

SQL> select *from sys.dba_audit_trail;

 

……

 

SQL> select *from sys.dba_audit_session;

 

未选定行

 

SQL> noaudit table, alter table, update table by test by access whenever successful;

noaudit table, alter table, update table by test by access whenever successful

                                                    *

ERROR 位于第 1 行:

ORA-01718: NOAUDIT 不允许 BY ACCESS | SESSION 子句

 

 

SQL> noaudit table, alter table, update table by test whenever successful;

 

审计未成功。

 

SQL> select * from dba_stmt_audit_opts;

 

未选定行

 

 

 

 

 

 

Oracle审计
1.
AUDIT_SYS_OPERATIONS = TRUE审计管理用户(以sysdba/sysoper角色登陆)
windows平台会保存到Event Viewer日志文件中,诸如
CONNECT / AS SYSDBA;
ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='myname';
的操作都会记录到windows事件中
AUDIT_TRAIL=OS时AUDIT_FILE_DEST定义审计的destination

2.
相关的视图
-- 审计记录
select * from sys.aud$
select * from dba_audit_trail
select * from dba_common_audit_trail
-- action的定义
select * from audit_actions

3.
多层环境下的审计
appserve-应用服务器
jackson-client?
AUDIT SELECT TABLE BY appserve ON BEHALF OF jackson;

4.
审计选项
Statement-诸如CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE等语句
Privilege-AUDIT CREATE ANY TRIGGER会审计使用CREATE ANY TRIGGER权限执行的语句
Object-审计特定对象上的特定语句,比如emp表上的ALTER TABLE语句

5.
BY SESSION/BY ACCESS-每个session或者每次访问
WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL-成功/不成功

6.
审计连接或断开连接:
AUDIT SESSION;
-- 指定用户
AUDIT SESSION BY jeff, lori;
审计权限(使用该权限才能执行的操作):
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;
AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS WHENEVER NOT SUCCESSFUL;
对象审计:
AUDIT DELETE ON jeff.emp;
AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL;

7.
取消审计
NOAUDIT session;
NOAUDIT session BY jeff, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,EXECUTE PROCEDURE;
-- 取消所有statement审计
NOAUDIT ALL;
-- 取消所有权限审计
NOAUDIT ALL PRIVILEGES;
-- 取消所有对象审计
NOAUDIT ALL ON DEFAULT;

8.
清除审计信息
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='EMP';

9.
审计视图
STMT_AUDIT_OPTION_MAP-审计选项类型代码
AUDIT_ACTIONS-action代码
ALL_DEF_AUDIT_OPTS-对象创建时默认的对象审计选项
DBA_STMT_AUDIT_OPTS-当前数据库系统审计选项
DBA_PRIV_AUDIT_OPTS-权限审计选项
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS-对象审计选项
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL-审计记录
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT-审计对象列表
DBA_AUDIT_SESSION
USER_AUDIT_SESSION-session审计
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT-语句审计
DBA_AUDIT_EXISTS-使用BY AUDIT NOT EXISTS选项的审计
DBA_AUDIT_POLICIES-审计POLICIES
DBA_COMMON_AUDIT_TRAIL-标准审计+精细审计