AUDIT

Purpose

Use the AUDIT statement to:

·         Track the occurrence of SQL statements in subsequent user sessions. You can track the occurrence of a specific SQL statement or of all SQL statements authorized by a particular system privilege. Auditing operations on SQL statements apply only to subsequent sessions, not to current sessions.

·         Track operations on a specific schema object. Auditing operations on schema objects apply to current sessions as well as to subsequent sessions.

Audit语义流图

子项

2,oracle audit测试步骤

配置audit_trail为db

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  289406976 bytes

Fixed Size                  1248600 bytes

Variable Size              71303848 bytes

Database Buffers          213909504 bytes

Redo Buffers                2945024 bytes

Database mounted.

Database opened.

SQL>

测建一个audit_test用户，用于审计测试操作用

SQL> show user

USER is "SYS"

SQL> create user audit_test identified by system default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect to audit_test;  ---给测建用户授权

Grant succeeded.

SQL> grant create table,create procedure to audit_test; ---给测建用户授权

Grant succeeded.

SQL> audit all by audit_test by access;  --申计测建用户所有的操作

Audit succeeded.

SQL> audit select table,update table,insert table,delete table by audit_test by access;--申计与测建用户系统权限相关的操作

Audit succeeded.

---以下开始以测建用户进行各种操作，用于查看是否把各种操作申计记录

SQL> conn audit_test/system

Connected.

SQL> create table test_tab(id number);

Table created.

SQL> insert into test_tab values(1);

1 row created.

SQL> update test_tab set id=id;

1 row updated.

SQL> select * from test_tab;

        ID

----------

         1

SQL> delete from test_tab;

1 row deleted.

SQL> drop table test_tab;

Table dropped.

在dba_audit_trail数据字典查询以上操作产生的申计记录

SQL> conn /as sysdba

Connected.

SQL> col username for a10

SQL> col owner for a10

SQL> col obj_name for a10

SQL> col extended_timestamp for a35

SQL> select username,extended_timestamp,owner,action_name from dba_audit_trail where wner='AUDIT_TEST' order by timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER

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

ACTION_NAME

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

AUDIT_TEST 08-SEP-09 09.20.29.576355 AM +08:00 AUDIT_TEST

CREATE TABLE

AUDIT_TEST 08-SEP-09 09.20.43.693351 AM +08:00 AUDIT_TEST

INSERT

AUDIT_TEST 08-SEP-09 09.20.51.383562 AM +08:00 AUDIT_TEST

UPDATE

USERNAME   EXTENDED_TIMESTAMP                  OWNER

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

ACTION_NAME

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

AUDIT_TEST 08-SEP-09 09.20.56.698266 AM +08:00 AUDIT_TEST

SELECT

AUDIT_TEST 08-SEP-09 09.21.02.743562 AM +08:00 AUDIT_TEST

DELETE

AUDIT_TEST 08-SEP-09 09.21.15.914372 AM +08:00 AUDIT_TEST

DROP TABLE

6 rows selected.

SQL>