oracle10g_audit_記錄業務_solaris

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>