Oracle 10g审计(audit)实验
1、AUDIT 的功能
AUDIT 用于监控用户在 DATABASE 的 ACTION
www.zzzyk.com
2、AUDIT 的分类
SESSION：在同一个 SESSION，相同语句只产生一个 AUDIT 结果（默认）
ACCESS：在同一个SESSION，每个语句产生一个 AUDIT 结果
3、启用 AUDIT （默认不启用） www.zzzyk.com
SQL> SHOW PARAMETER AUDIT_TRAIL
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
AUDIT_TRAIL STRING NONE
SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
SYSTEM ALTERED.
SQL> SHUTDOWN IMMEDIATE;
DATABASE CLOSED.
DATABASE DISMOUNTED.
ORACLE INSTANCE SHUT DOWN.
SQL> STARTUP
ORACLE INSTANCE STARTED.
TOTAL SYSTEM GLOBAL AREA 171966464 BYTES
FIXED SIZE 1279144 BYTES
VARIABLE SIZE 96471896 BYTES
DATABASE BUFFERS 71303168 BYTES
REDO BUFFERS 2912256 BYTES
DATABASE MOUNTED.
DATABASE OPENED.
SQL> SHOW PARAMETER AUDIT_TRAIL
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
AUDIT_TRAIL STRING DB
AUDIT_TRAIL 参数的取值及含义
AUDIT_TRAIL ENABLES OR DISABLES DATABASE AUDITING.
VALUES:
NONE
DISABLES DATABASE AUDITING.
•
OS
ENABLES DATABASE AUDITING AND DIRECTS ALL AUDIT RECORDS TO THE OPERATING SYSTEM'S AUDIT TRAIL.
•
DB
ENABLES DATABASE AUDITING AND DIRECTS ALL AUDIT RECORDS TO THE DATABASE AUDIT TRAIL (THE SYS.AUD$ TABLE).
•
DB,EXTENDED
ENABLES DATABASE AUDITING AND DIRECTS ALL AUDIT RECORDS TO THE DATABASE AUDIT TRAIL (THE SYS.AUD$ TABLE). IN ADDITION, POPULATES THE SQLBIND AND SQLTEXT CLOB COLUMNS OF THE SYS.AUD$ TABLE.
•
XML
ENABLES DATABASE AUDITING AND WRITES ALL AUDIT RECORDS TO XML FORMAT OS FILES.
•
XML,EXTENDED
ENABLES DATABASE AUDITING AND PRINTS ALL COLUMNS OF THE AUDIT TRAIL, INCLUDING SQLTEXT AND SQLBIND VALUES.
YOU CAN USE THE SQL STATEMENT AUDIT TO SET AUDITING OPTIONS REGARDLESS OF THE SETTING OF THIS PARAMETER.
4、审计的对象：（默认情况：SESSION，对成功和不成功的同时审计）
语句审计
SQL> AUDIT TABLE;
AUDIT SUCCEEDED.
SQL> AUDIT TABLE BY SCOTT;
AUDIT SUCCEEDED.
SQL> AUDIT TABLE BY SCOTT WHENEVER SUCCESSFUL;
AUDIT SUCCEEDED.
----------查看审计设置
SQL> SELECT USER_NAME,AUDIT_OPTION FROM DBA_STMT_AUDIT_OPTS;
USER_NAME AUDIT_OPTION
-------------------- --------------------
TABLE
SCOTT TABLE
SQL> CONN SCOTT/TIGER
CONNECTED.
SQL> DROP TABLE RECOVER_TEST;
TABLE DROPPED.
SQL> CREATE TABLE DEPT1 AS SELECT * FROM DEPT;
TABLE CREATED.
SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;
TABLE CREATED.
SQL> ALTER TABLE DEPT1 ADD CONSTRAINT DEPTNO_PK PRIMARY KEY(DEPTNO);
TABLE ALTERED.
SQL> ALTER TABLE EMP1 ADD CONSTRAINT EMPNO_PK PRIMARY KEY (EMPNO);
TABLE ALTERED.
SQL> ALTER TABLE EMP1 ADD CONSTRAINT DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT1(DEPTNO);
TABLE ALTERED.
SQL> DROP TABLE DEPT1;
DROP TABLE DEPT1
*
ERROR AT LINE 1:
ORA-02449: UNIQUE/PRIMARY KEYS IN TABLE REFERENCED BY FOREIGN KEYS
SQL> DROP TABLE DEPT1 CASCADE CONSTRAINT PURGE;
TABLE DROPPED.
SQL> CREATE USER TOM IDENTIFIED BY TOM;
SQL> CONN / AS SYSDBA
CONNECTED.
SQL> GRANT CREATE SESSION,RESOURCE TO TOM;
GRANT SUCCEEDED.
SQL> CONN TOM/TOM
CONNECTED.
SQL> CREATE TABLE TEST(ID NUMBER(2));
TABLE CREATED.
SQL> DROP TABLE TEST;
TABLE DROPPED
SQL> CONN / AS SYSDBA
CONNECTED.
SQL> CONN / AS SYSDBA
CONNECTED.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
SESSION ALTERED.
SQL> DESC DBA_AUDIT_TRAIL;
NAME NULL? TYPE
----------------------------------------- -------- ----------------------------
OS_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
OWNER VARCHAR2(30)
OBJ_NAME
