Contents

1 螳
2 覓語 螳
3 蟠螳
4 螳豌 螳


[edit]

1 螳 #

一危覯伎り 譬 苦朱 蟯襴 譯手鍵 覈磯 . 企 一危覯伎 蟯襴 ろ 殊企 れ 覈磯 讌 Auditing企手 . 螳 譬襯 覓語リ, 蟠 螳, 螳豌 螳 企蟆 3螳讌襦 蟲覿. 企蟆 手係覃 襾殊 螳 ″ 伎伎狩讌 覈襯願給. 覿 螻り. 一危覯伎 ろ 覲螻 覈磯企 企螳 蠍磯 覿 蟆. る狩伎 蠏語 Audit Trail ロ蟆 . 譴蟆 螳 覦覯 螳 覈 襷 襦 螳襯 蟆. る ′ 豢螻, 螳 豢 一危襯 覲危誤伎 . 覓伎 螳 豢襷 り 蟆 讌. 覓朱 一危 螳 碁Μ蟇磯 螻 譟一襯 豬蟆 .

螳 燕/觜燕 . 覓伎 螳襯 る ろ るジ るる襦 蟆讌. 蠏碁 覓伎 螳 給. 蠏 覓伎 螳 る狩 語ろ伎れ startup/shutdown, 一危覯伎 蟯襴 蟠 螳讌 螳 . る狩伎 朱誤 殊 AUDIT_TRAIL 螳 覈朱 螳襯 燕 給.

AUDIT_TRAIL = DB | OS | NONE

3螳讌 螳 れ願螳 給. NONE 朱誤一 蠍磯蓋螳願規...DB 朱誤郁朱 れ覃 螳 豢一危 sys.AUD$ 一危 觀磯ゼ 牛 覲殊螳 給. OS 朱誤郁朱 れ覃 豢 殊 伎豌伎 ル. 企轟語 螳 燕/觜燕螳 螳ロ. 朱誤 殊 螳 燕朱 覈螳 襷. 語 覃 螳 企朱 詞伎. AUDIT NOAUDIT 覓語朱 燕/觜燕 給. 覓語レ る AUDIT SYSTEM 蟠 伎 蟲.

朱 螳襯 る 蟯襴襯 ろ襴渚誤襯 ろ伎 . 讀, 螳襯 企螻 觀磯ゼ 燕 蟆伎. 願 $ORACLE_HOME/rdbms/admin/cataudit.sql ろ覃 . 螳螳 豺 企螻 觀一 $ORACLE_HOME/rdbms/admin/catnoaud.sql ろ覃 .

螳襯 朱 蟆 覃 螳 覯襯 蟆一 螳襯 伎 蟆讌. 覓 襷 伎 螳 覲願 ルる 願碓 覿蠍 れ 讌蟆. 蠏碁 覯 蟆一 襷れ 譴 . 覯 SQL ろ 炎概覿 磯殊 語 一危覯伎れ 蠏殊 螳煙朱 蟲覿讌 螳螻 覯襯 譬 .

蠏碁 手係 螳 譬襯 3螳讌 蟆 螳螳 企慨蟆給.

[edit]

2 覓語 螳 #

覓語リ SQL覓語 螳. 覈 覓語レ 螳螳 螳ロ讌襷 蟆 一危覯伎れ 企伎 一危覯伎れ ′ 螳 語. 覓語リ 覓碁 れ螻 螳給.

AUDIT statement_option
BY user
[BY SESSION | ACCESS]
[WHENEVER NOT] SUCCESSFUL

BY SESSION 旧朱 螳襯 語覲 螳企襦 DML 覓語レ ろ 蟆曙一襷 企麹螻, BY ACCESS 旧朱 螳襯 SQL覓語 伎 螳豌企襦 螳襯 蟆 . WHENEVER NOT SUCCESSFUL 覓語レ 炎概/ろ 磯殊 螳 覿襯 蟆一 給. 語 DBA_STMT_ADUIT_OPTS

[edit]

3 蟠螳 #

蟠 螳 る狩伎 覈 蟠 伎 螳襯 蟆 . 麹 襷 覿覿. 蟾 手係 螳 譴 覦覯 覯襯 豕 豢る 蟆. 覓碁 覓語リ 觜訣.

AUDIT system_privileges
BY user
[BY SESSION | ACCESS]
[WHENEVER NOT] SUCCESSFUL

system_privileges ALL PRIVILEGE襯 讌覃 覈 蟠 螳襯 れ. ろ 蟠 螳 語 DBA_PRIV_AUDIT_OPTS 觀磯 誤.

[edit]

4 螳豌 螳 #

覈 蠏碁襦 螳豌伎 螳. 讀, 螳豌 螳 DML 覓語リ骸 GRANT, REVOKE 螳. 磯殊 螳豌 螳 覈 襯 朱 蟆一. 覓碁 れ螻 螳給.

AUDIT object_opt ON [[schema.]object | DEFAULT]
[BY SESSION | ACCESS]
[WHENEVER NOT] SUCCESSFUL

ろ 蟠 螳 語 DBA_OBJ_AUDIT_OPTS 觀磯 誤. れ襦 企.

SQL> connect internal                                   
郁屋給.                                         		
SQL> shutdown                                           
一危磯伎り 給.           
一危磯伎り 襷危 伎給.
ORACLE 語ろ伎り 譬襭給.    

SQL> !vi $ORACLE_HOME/dbs/initORCL.ora
                         .
                         .
                         .
                         .
# estimates for computer memory availability. You should adjust values upward  
# for modern machines.                                                         
#                                                                              
###############################################################################
                                                                               
AUDIT_TRAIL =DB                  <------------- 豢螳 貅譴...                                                      
db_name = "ORCL"                                                               
db_domain = yasicom                                                            
                                                                               
instance_name = ORCL                                                           
                                                                               
service_names = ORCL.yasicom                                                   
                          .
                          .
                          .
                          .

SQL> startup                                                       
ORACLE 語ろ伎り 給.        
                                         
Total System Global Area   87818400 bytes
Fixed Size                    73888 bytes
Variable Size              70795264 bytes
Database Buffers           16777216 bytes
Redo Buffers                 172032 bytes
一危磯伎り 襷危碁給.         
一危磯伎り 企語給.               

SQL> @$ORACLE_HOME/rdbms/admin/cataudit.sql
                .
                .
                .
                .
                .
                .
SQL> audit create table                  
  2  by scott            
  3  by access           
  4  whenever successful;
                         
螳 炎概.         

SQL> connect scott/tiger            
郁屋給.                     
SQL> drop table test;               
                                    
企 給.            
                                    
SQL> create table test ( id number);
                                    
企 焔給.            
                                    
SQL> connect system/manager         
郁屋給.                     
SQL> connect system/manager                                        
郁屋給.                                                    
SQL> select username, timestamp, action_name                       
  2  from dba_audit_trail;                                         
                                                                   
USERNAME                       TIMESTAM ACTION_NAME                
------------------------------ -------- ---------------------------
SCOTT                          01/12/06 LOGOFF                     
SCOTT                          01/12/06 DROP TABLE                 
SCOTT                          01/12/06 CREATE TABLE               

SQL> select username,                                                           
  2  to_char(timestamp, 'mm-dd-yy hh24:mi:ss') login_time,                      
  3  to_char(logoff_time, 'mm-dd-yy hh24:mi:ss') logoff_time,                   
  4  logoff_lread logical_reads,                                                
  5  logoff_pread physical_reads,                                               
  6  logoff_lread + logoff_pread total_reads                                    
  7  from sys.dba_audit_session;                                                
                                                                                
USERNAME                       LOGIN_TIME        LOGOFF_TIME       LOGICAL_READS PHYSICAL_READS TOTAL_READS 
------------------------------ ----------------- ----------------- ------------- -------------- ----------- 
                                                      
SCOTT                          12-06-01 20:03:32 12-06-01 20:04:05           660            40         700                                                      

SQL> desc sys.aud$
 企                                      ?      
 ----------------------------------------- -------- ---------------------------
 SESSIONID                                 NOT NULL NUMBER
 ENTRYID                                   NOT NULL NUMBER
 STATEMENT                                 NOT NULL NUMBER
 TIMESTAMP#                                NOT NULL DATE
 USERID                                             VARCHAR2(30)
 USERHOST                                           VARCHAR2(128)
 TERMINAL                                           VARCHAR2(255)
 ACTION#                                   NOT NULL NUMBER
 RETURNCODE                                NOT NULL NUMBER
 OBJ$CREATOR                                        VARCHAR2(30)
 OBJ$NAME                                           VARCHAR2(128)
 AUTH$PRIVILEGES                                    VARCHAR2(16)
 AUTH$GRANTEE                                       VARCHAR2(30)
 NEW$OWNER                                          VARCHAR2(30)
 NEW$NAME                                           VARCHAR2(128)
 SES$ACTIONS                                        VARCHAR2(19)
 SES$TID                                            NUMBER
 LOGOFF$LREAD                                       NUMBER
 LOGOFF$PREAD                                       NUMBER
 LOGOFF$LWRITE                                      NUMBER
 LOGOFF$DEAD                                        NUMBER
 LOGOFF$TIME                                        DATE
 COMMENT$TEXT                                       VARCHAR2(4000)
 SPARE1                                             VARCHAR2(255)
 SPARE2                                             NUMBER
 OBJ$LABEL                                          RAW(255)
 SES$LABEL                                          RAW(255)
 PRIV$USED                                          NUMBER

SQL> noaudit create table
  2  by scott
  3  by access
  4  whenever successful;
by access
   *
3 る:
ORA-01718: BY ACCESS | SESSION NOAUDIT 伎 讌 給


SQL> noaudit create table
  2  by scott;

螳 伎 炎概.

SQL> audit all privilege                      <---------------------覈 蟠 伎 螳襯 .
  2  by access;

螳 炎概.

SQL> connect scott/tiger
郁屋給.
SQL> create user test_user
  2  identified by test_user;
identified by test_user
              *
2 る:
ORA-01031: 蟠 覿豢覿


SQL> select username, timestamp, action_name
  2  from dba_audit_trail;
from dba_audit_trail
     *
2 る:
ORA-00942: 企  觀郁 譟伎讌 給


SQL> connect system/manager
郁屋給.
SQL> select username, timestamp, action_name
  2  from dba_audit_trail;

USERNAME                       TIMESTAM ACTION_NAME
------------------------------ -------- ---------------------------
SCOTT                          01/12/06 LOGOFF
SCOTT                          01/12/06 DROP TABLE
SCOTT                          01/12/06 CREATE TABLE
SCOTT                          01/12/06 LOGOFF
SCOTT                          01/12/06 EXECUTE PROCEDURE
SCOTT                          01/12/06 EXECUTE PROCEDURE
SCOTT                          01/12/06 CREATE USER
SCOTT                          01/12/06 SELECT
SYSTEM                         01/12/06 LOGON
SYSTEM                         01/12/06 EXECUTE PROCEDURE
SYSTEM                         01/12/06 EXECUTE PROCEDURE

11 螳  給.

SQL> noaudit all privilege;

螳 伎 炎概.

SQL> audit select, insert, update, delete
  2  on scott.dept
  3  by access
  4  whenever successful;

螳 炎概.

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select username, timestamp, action_name
  2  from dba_audit_trail;

USERNAME                       TIMESTAM ACTION_NAME
------------------------------ -------- ----------------------
SCOTT                          01/12/06 LOGOFF
SCOTT                          01/12/06 DROP TABLE
SCOTT                          01/12/06 CREATE TABLE
SCOTT                          01/12/06 LOGOFF
SCOTT                          01/12/06 EXECUTE PROCEDURE
SCOTT                          01/12/06 EXECUTE PROCEDURE
SCOTT                          01/12/06 CREATE USER
SCOTT                          01/12/06 SELECT
SYSTEM                         01/12/06 LOGON
SYSTEM                         01/12/06 EXECUTE PROCEDURE
SYSTEM                         01/12/06 EXECUTE PROCEDURE
SYSTEM                         01/12/06 SYSTEM NOAUDIT
                        .
                        .
                        .
                        .
                        .
                        .
                        .
                        .
                                                
SYSTEM                         01/12/06 SYSTEM NOAUDIT
SYSTEM                         01/12/06 SYSTEM NOAUDIT
SYSTEM                         01/12/06 SYSTEM NOAUDIT
SYSTEM                         01/12/06 AUDIT OBJECT
SYSTEM                         01/12/06 SELECT                   <-------------蠍磯..

129 螳  給.

SQL> noaudit select, insert, update, delete
  2  on scott.dept;

螳 伎 炎概.

螳襯 豢 蠎 襷 螳襯 ろ 伎 . 400 128 貉伎り 朱Μ8 覯蟾蟲 る狩8i(8.1.7) 蟾 蠏碁一 覈磯朱 蠏 谿螳 蟷伎れ. ~ ろ語 蟆曙 蟲 伎 語 覈郁給. 螳 覯 譴願 伎 螳 ろ ろ 豌伎 焔レ 覩語 レ 豕 譴譯殊伎 . 蠏碁Μ螻 螳 豢 螻糾 豢覿 襷伎 . リ概螳 蟒谿蟆 覃 る狩伎 螳豢 一危襯 sys.AUD$ ロ伎 ロ讌 覈詩伎 襯 襴危蟆 . 譯殊蟲.

螳豢 蟯襴 譯手鍵朱 螳 豢 一危襯 蟇壱伎 覿ｸ 給. 覈螳讌 覦覯 給. 豌覯讌 覦覯 朱 覦覯, 覯讌碁 企豌企ゼ るジ 企襦 覲旧伎 蠍危 螳 豢 一危襯 覦覯, 碁讌碁 伎豌伎 朱 覲旧 覦覯. れ 蠏 .

SQL> create table backup_audit_trail            -- 15殊 讌 螳豢 一危襯 覲旧.
  2  as
  3  select * from sys.AUD$
  4  where timestamp# < sysdate - 15;

企 焔給.

yasicom% exp system/manager tables = sys.AUD$ file = /export/home/oracle/audit_trail_1.dmp             --   伎   .

Export: Release 8.1.7.0.0 - Production on 覈 Dec 6 20:47:01 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


 : Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
KO16KSC5601 覓語 れ螻 KO16KSC5601 NCHAR 覓語 れ ろ瑚 譬襭給

讌 企 ろ誤り  via 蠏 蟆暑...
  SYS襦 覲蟆暑給
. . 企                           AUD$(襯) ろ 譴        131  ろ碁
ろ瑚 蟆所  朱 譬襭給.
yasicom% ls -l /export/home/oracle/audit_trail_1.dmp
-rw-r--r--   1 oracle   dba        16384 12  6  20:47 /export/home/oracle/audit_trail_1.dmp
yasicom%
yasicom% sqlplus system/manager

SQL*Plus: Release 8.1.7.0.0 - Production on 覈 Dec 6 20:48:25 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> truncate table sys.AUD$;

企 語給.

SQL> select count(*) from sys.AUD$;

  COUNT(*)
----------
         0

れ 螳覲企ゼ 譟壱 一危 觀 .

* DBA_PRIV_AUDIT_OPTS
* DBA_STMT_AUDIT_OPTS
* DBA_OBJ_AUDIT_OPTS
* STMT_AUDIT_OPTION_MAP
* AUDIT_ACTIONS
* ALL_DEF_AUDIT_OPTS
* USER_OBJ_AUDIT_OPTS
* DBA_AUDIT_TRAIL
* USER_AUDIT_TRAIL
* DBA_AUDIT_SESSION
* USER_AUDIT_SESSION
* DBA_AUDIT_STATEMENT
* USER_AUDIT_STATEMENT
* DBA_AUDIT_OBJECT
* USER_AUDIT_OBJECT
* DBA_AUDIT_EXISTS