Oracle 10g 审计 (audit) 实验

最新推荐文章于 2021-04-04 21:03:09 发布

冰释镜湖

最新推荐文章于 2021-04-04 21:03:09 发布

阅读量3.4k

点赞数 2

分类专栏： Oracle Database Architecture Oracle Troubleshooting Oracle 11g OCP Certification 文章标签： Oracle 审计日志挖掘 audit auditing

本文链接：https://blog.csdn.net/xiangsir/article/details/8623350

版权

本文介绍了在Oracle 10g中进行审计(audit)操作的实验过程，包括如何更新数据以及审计跟踪的重要性。通过审计，可以详细记录数据库中的数据修改行为，如示例中将员工7788的薪水更新为7000。对于日志挖掘和审计分析具有参考价值。

摘要由CSDN通过智能技术生成

1、AUDIT 的功能
AUDIT 用于监控用户在 DATABASE 的 ACTION

2、AUDIT 的分类
SESSION：在同一个 SESSION，相同语句只产生一个 AUDIT 结果（默认）
ACCESS：在同一个SESSION，每个语句产生一个 AUDIT 结果

3、启用 AUDIT （默认不启用）
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_US