Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

D:\Documents and Settings\tian>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 6月 20 09:00:55 2013

SQL> conn /as sysdba
已连接。

SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\TEST\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE

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

系统已更改。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             230689644 bytes
Database Buffers          377487360 bytes
Redo Buffers                2940928 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      D:\ADMIN\TEST\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
DELETE ANY TABLE BY ACCESS BY ACCESS

SQL> select * from dba_audit_trail;

OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME NEW_OWNER NEW_NAME OBJ_PRIVILEGE SYS_PRIVILEGE ADMIN_OPTION GRANTEE AUDIT_OPTION SES_ACTIONS LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK COMMENT_TEXT SESSIONID ENTRYID STATEMENTID RETURNCODE PRIV_USED CLIENT_ID ECONTEXT_ID SESSION_CPU EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER OS_PROCESS TRANSACTIONID SCN SQL_BIND SQL_TEXT
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- ---------- ---------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------- ---------------------------------------- ------------ ------------------------------ ---------------------------------------- ------------------- ----------- ------------ ------------ ------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ---------- ---------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- --------------- -------------------------------- --------------- ---------------- ---------------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------

SQL> audit delete any table by access;

Audit succeeded

SQL> delete from nbs.t;

0 rows deleted

SQL> commit;

Commit complete

SQL> select * from sys.aud$;

SESSIONID ENTRYID STATEMENT TIMESTAMP# USERID USERHOST TERMINAL ACTION# RETURNCODE OBJ$CREATOR OBJ$NAME AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER NEW$NAME SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE LOGOFF$DEAD LOGOFF$TIME COMMENT$TEXT CLIENTID SPARE1 SPARE2 OBJ$LABEL SES$LABEL PRIV$USED SESSIONCPU NTIMESTAMP# PROXY$SID USER$GUID INSTANCE# PROCESS# XID AUDITID SCN DBID SQLBIND SQLTEXT
---------- ---------- ---------- ----------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------- ---------- ------------ ------------ ------------- ----------- ----------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------- ---------- ---------------- ---------------- ---------------------------------------------------------------- ---------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
13718 1 27 SCOTT WORKGROUP\TIANLEI TIANLEI 7 0 NBS T TIANLEI\tian 50 20-6月 -13 01.04.40.812000 上午 0 1268:3032 02002600C5060000 4009520

SQL> NOAUDIT DELETE ANY TABLE;

Noaudit succeeded

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------

以上是最简单的权限审计。
开启审计的前提是初始化参数audit_trail 不是NONE。
audit delete any table by access;是对所有使用delete any table权限的语句进行审计。
成功设置后可以从sys.aud$中查看审计到的符合条件的记录信息。
在dba_priv_audit_opts视图中看到我们都设置了哪些审计要求。
当不再需要审计时，执行NOAUDIT ……;将对应的审计选项关闭。

audit、noaudit语句的使用不需要重启数据库，但设置audit_trail需要修复参数文件并重启数据库。

权限审计的几种形式：
AUDIT DELETE ANY TABLE BY ACCESS;--审计所有使用了delete any table权限的语句
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;--审计所有使用了delete any table权限，未成功执行的语句
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;--审计所有用户增删查表及执行存储过程未成功的语句

禁用权限审计需要audit system系统权限。通常只有安全管理员才有此权限。

注意：
1.dba_priv_audit_opts视图中的记录与我们所开启的审计并非一对一的关系。比如执行audit create table by access；将产生两条记录：
CREATE ANY TABLE和CREATE TABLE
2.在我们将audit_trail设置为NONE时，我们仍可以正常执行audit/noaudit语句，只是不生成相关的记录。可以认为就是开启了审计，但不保存跟踪痕迹
3.audit和noaudit是严格对应的。比如我们用audit create table by scott by access开启对scott创建表的审计，如果执行noaudit create table；并不能使审计关闭。比如执行noaudit create table by scott;
4.在删除了所有可以删除的审计策略之后，可能发现有些策略无法用语句取消审计。比如：我的实验中发现不知道如何删除以下选项
CREATE EXTERNAL JOB                      BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE               BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY                     BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE                      BY ACCESS BY ACCESS
DROP ANY PROCEDURE                       BY ACCESS BY ACCESS
ALTER ANY PROCEDURE                      BY ACCESS BY ACCESS
GRANT ANY ROLE                           BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK              BY ACCESS BY ACCESS
DROP ANY TABLE                           BY ACCESS BY ACCESS
ALTER ANY TABLE                          BY ACCESS BY ACCESS
这些在dba_priv_audit_opts视图中存在，最后通过删除sys.audit$中的对应关系将其删除了。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
以下将audit_trail改为xml，extended看看效果：

SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/admin/testdb/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB, EXTENDED

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

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup open;
ORACLE instance started.

Total System Global Area 405929984 bytes
Fixed Size                  1336848 bytes
Variable Size             318769648 bytes
Database Buffers           79691776 bytes
Redo Buffers                6131712 bytes
Database mounted.
Database opened.
SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/admin/testdb/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      XML, EXTENDED
SQL> audit create table by scott by access;

Audit succeeded.

SQL> conn scott/scott
Connected.

SQL> create table terry.emp as select * from emp;

Table created

SQL> select sql_text from v$xml_audit_trail;

SQL_TEXT
--------------------------------------------------------------------------------
CONNECT
SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, S
ALTER DATABASE MOUNT
CONNECT
ALTER DATABASE OPEN
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integ
audit create table by scott by access
select current_scn from v$database
CONNECT
COMMIT
COMMIT
BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
select lower(user) || '@' || substr( global_name, 1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name )

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
create table terry.dept as select * from dept
create table terry.dept as select * from dept

16 rows selected

scott@TESTDB> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost adump]$ pwd
/u01/admin/testdb/adump

[oracle@localhost adump]$ ll
total 44
-rw-r--r-- 1 oracle oinstall 188 May 13 15:20 adx_testdb.txt
-rw-r----- 1 oracle oinstall 2337 May 13 14:58 testdb_ora_21809_1.aud
-rw-r----- 1 oracle oinstall 1149 May 13 15:10 testdb_ora_21983_1.aud
-rw-r----- 1 oracle oinstall 2997 May 13 15:13 testdb_ora_21984_1.aud
-rw-r----- 1 oracle oinstall 1357 May 13 15:17 testdb_ora_22009_1.aud
-rw-r----- 1 oracle oinstall 720 May 13 15:17 testdb_ora_22018_1.aud
-rw-r----- 1 oracle oinstall 727 May 13 15:17 testdb_ora_22018_2.aud
-rw-r--r-- 1 oracle oinstall 2072 May 13 15:17 testdb_ora_22102_1.xml
-rw-r--r-- 1 oracle oinstall 3154 May 13 15:18 testdb_ora_22123_1.xml
-rw-r--r-- 1 oracle oinstall 3500 May 13 15:19 testdb_ora_22198_1.xml
-rw-r--r-- 1 oracle oinstall 1643 May 13 15:21 testdb_ora_22200_1.xml

可以看到，虽然我们只开启了一个语句审计的策略，但默认每次启动数据库操作都会被记录到审计日志中，而且由于在登录时使用了login.sql脚本，该脚本中的部分操作也被记录下来了。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

细粒度审计一例：
创建对terry.dept的update操作审计。
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY(
3 object_schema => 'TERRY',
4 object_name => 'DEPT',
5 policy_name => 'chk_terry_dept',
6 enable => TRUE,
7 statement_types => 'UPDATE',
8 audit_trail => DBMS_FGA.DB);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> select * from sys.fga_log$;

no rows selected

SQL> update terry.dept set deptno=50 where deptno=40;

1 row updated.

select obj$schema,obj$name,policyname from sys.fga_log$
2 ;

OBJ$SCHEMA OBJ$NAME POLICYNAME
------------------------------ ---------------- ----------------------------
TERRY DEPT CHK_TERRY_DEPT

可以看到，即使操作还未提交，也会记录一条审计记录。

找到问题后一定要记得删除审计策略：

SQL> exec DBMS_FGA.DISABLE_POLICY('TERRY','DEPT','chk_terry_dept');

PL/SQL procedure successfully completed.

ITPub博客

实验-审计

最新文章