Oracle 10g 审计 (audit) 实验

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_USERNAME VARCHAR2(255)
USERNAME VARCHAR2(30)
USERHOST VARCHAR2(128)
TERMINAL VARCHAR2(255)
TIMESTAMP DATE
OWNER VARCHAR2(30)
OBJ_NAME VARCHAR2(128)
ACTION NOT NULL NUMBER
ACTION_NAME VARCHAR2(28)
NEW_OWNER VARCHAR2(30)
NEW_NAME VARCHAR2(128)
OBJ_PRIVILEGE VARCHAR2(16)
SYS_PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(1)
GRANTEE VARCHAR2(30)
AUDIT_OPTION VARCHAR2(40)
SES_ACTIONS VARCHAR2(19)
LOGOFF_TIME DATE
LOGOFF_LREAD NUMBER
LOGOFF_PREAD NUMBER
LOGOFF_LWRITE NUMBER
LOGOFF_DLOCK VARCHAR2(40)
COMMENT_TEXT VARCHAR2(4000)
SESSIONID NOT NULL NUMBER
ENTRYID NOT NULL NUMBER
STATEMENTID NOT NULL NUMBER
RETURNCODE NOT NULL NUMBER
PRIV_USED VARCHAR2(40)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
SESSION_CPU NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
SCN NUMBER
SQL_BIND NVARCHAR2(2000)
SQL_TEXT NVARCHAR2(2000)


SQL> SELECT USERNAME,USERHOST,TIMESTAMP,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;


USERNAME USERHOST TIMESTAMP OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- -------------------- ---------------
TOM SUN10G 20120915 09:55:14 TEST CREATE TABLE
SCOTT SUN10G 20120914 23:21:24 EMP1 CREATE TABLE
SCOTT SUN10G 20120914 23:21:01 DEPT1 CREATE TABLE
TOM SUN10G 20120915 09:55:26 TEST DROP TABLE
SCOTT SUN10G 20120915 09:51:23 DEPT1 DROP TABLE
SCOTT SUN10G 20120915 09:50:57 DEPT1 DROP TABLE
SCOTT SUN10G 20120914 23:06:26 RECOVER_TEST DROP TABLE


-------------审计结果存放在名为 AUD$ 的基表中，可以通过 DBA_AUDIT_TRAIL 视图查询




SQL> SELECT COUNT(*) FROM AUD$;


COUNT(*)
----------
7


SQL> DESC AUD$;


--------------删除审计结果


SQL> DELETE FROM AUD$;


7 ROWS DELETED.


SQL> COMMIT;


COMMIT COMPLETE.




--------------关闭审计
SQL> NOAUDIT TABLE;


NOAUDIT SUCCEEDED.


2、权限审计


SQL> AUDIT CREATE TABLE;


AUDIT SUCCEEDED.


SQL> AUDIT SELECT ANY TABLE, CREATE ANY TRIGGER;


AUDIT SUCCEEDED.


SQL> AUDIT SELECT ANY TABLE BY HR BY SESSION;


AUDIT SUCCEEDED.


SQL> CONN SCOTT/TIGER
CONNECTED.
SQL> SELECT TABLE_NAME FROM USER_TABLES;


TABLE_NAME
------------------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP1


SQL> CREATE TABLE DEPT1 AS SELECT * FROM DEPT;
TABLE CREATED.


SQL> CONN / AS SYSDBA
CONNECTED.


-------- CREATE TABLE 操作审计
SQL> SELECT USERNAME,USERHOST,TIMESTAMP OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;


USERNAME USERHOST OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- ---------------
SCOTT SUN10G 2012-09-15 10:09:56 CREATE TABLE






3、对象审计
SQL> AUDIT ALL ON SCOTT.EMP;


AUDIT SUCCEEDED.


SQL> NOAUDIT ALL ON SCOTT.EMP;


NOAUDIT SUCCEEDED.


SQL> AUDIT ALL ON SCOTT.EMP1;


AUDIT SUCCEEDED.


SQL> CONN SCOTT/TIGER
CONNECTED.
SQL> COL ENAME FOR A20
SQL> SELECT * FROM EMP1;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------------------------- ---------- ------------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10


14 ROWS SELECTED.




SQL> UPDATE EMP1 SET ENAME='MAOMI' WHERE EMPNO=7788;


1 ROW UPDATED.


SQL> DELETE FROM EMP1 WHERE ROWNUM <2;


1 ROW DELETED.


SQL> COMMIT;


COMMIT COMPLETE.


SQL> CONN / AS SYSDBA
CONNECTED.


SQL> COL SES_ACTIONS FOR A20
SQL> SELECT USERNAME,USERHOST,TIMESTAMP,SES_ACTIONS,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;


USERNAME USERHOST TIMESTAMP SES_ACTIONS OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- -------------------- -------------------- ---------------
SCOTT SUN10G 2012-09-15 10:09:56 DEPT1 CREATE TABLE
SCOTT SUN10G 2012-09-15 10:20:36 ---S-----SS----- EMP1 SESSION REC




CONN SCOTT/TIGER




SQL> UPDATE EMP1 SET ENAME='SCOTTMAOMIMAOMI' WHERE EMPNO=7788;
UPDATE EMP1 SET ENAME='SCOTTMAOMIMAOMI' WHERE EMPNO=7788
*
ERROR AT LINE 1:
ORA-12899: VALUE TOO LARGE FOR COLUMN "SCOTT"."EMP1"."ENAME" (ACTUAL: 15, MAXIMUM: 10)


CONN / AS SYSDBA




SQL> SELECT USERNAME,USERHOST,TIMESTAMP,SES_ACTIONS,OBJ_NAME,ACTION_NAME FROM DBA_AUDIT_TRAIL;


USERNAME USERHOST TIMESTAMP SES_ACTIONS OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- -------------------- -------------------- ---------------
SCOTT SUN10G 2012-09-15 10:09:56 DEPT1 CREATE TABLE
SCOTT SUN10G 2012-09-15 10:20:36 ---S-----SS----- EMP1 SESSION REC
SCOTT SUN10G 2012-09-15 10:30:10 ----------F----- EMP1 SESSION REC
SCOTT SUN10G 2012-09-15 10:26:42 ---------SB----- EMP1 SESSION REC




-----------S 表示会话操作成功，F 表示会话中的操作失败，B 表示会话中的操作既有成功，也有失败。


5、精细审计FINE GRAINED AUDITING (FGA)
根据内容监控数据访问
对 SELECT、INSERT、UPDATE、DELETE 和 MERGE 操作进行审计
可以对表或视图甚至是一个活多个列进行审计
可能会调用存储过程
使用 DBMS_FGA 包进行管理


建立审计策略


SQL> EXEC DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA=>'SCOTT',-
> OBJECT_NAME=>'EMP1',POLICY_NAME=>'CHK_EMP',-
> AUDIT_CONDITION=>'DEPTNO=20',AUDIT_COLUMN=>'SAL',-
> STATEMENT_TYPES=>'UPDATE,SELECT');


PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.




CONN SCOTT/TIGER


SQL> UPDATE EMP1 SET DEPTNO=10 WHERE EMPNO=7788;


1 ROW UPDATED.


SQL> UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788;


1 ROW UPDATED.


SQL> UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20;


3 ROWS UPDATED.


SQL> COMMIT;


COMMIT COMPLETE.


SQL> SET LINESIZE 80
SQL> DESC DBA_FGA_AUDIT_TRAIL
NAME NULL? TYPE
----------------------------------------- -------- ----------------------------
SESSION_ID NOT NULL NUMBER
TIMESTAMP DATE
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
EXT_NAME VARCHAR2(4000)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
SCN NUMBER
SQL_TEXT NVARCHAR2(2000)
SQL_BIND NVARCHAR2(2000)
COMMENT$TEXT VARCHAR2(4000)
STATEMENT_TYPE VARCHAR2(7)
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
STATEMENTID NUMBER
ENTRYID NUMBER


SQL> COL SQL_TEXT FOR A45
SQL> SET LINESIZE 120
SQL> COL DB_USER FOR A15
SQL> SELECT DB_USER,TIMESTAMP , SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;


DB_USER TIMESTAMP SQL_TEXT
--------------- -------------------- ---------------------------------------------
SCOTT 2012-09-15 10:44:39 SELECT * FROM EMP1
SCOTT 2012-09-15 10:45:47 UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788
SCOTT 2012-09-15 10:46:04 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20


--------------------精细审计结果存放到FGA_LOG$的基表里，通过DBA_FGA_AUDIT_TRAIL 查看。


--------------------清除精细审计结果


SQL> SELECT COUNT(*) FROM FGA_LOG$;


COUNT(*)
----------
3


SQL> DELETE FROM FGA_LOG$;


3 ROWS DELETED.


--------------------禁止精细审计


SQL> EXEC DBMS_FGA.DISABLE_POLICY(-
> OBJECT_SCHEMA=>'SCOTT',OBJECT_NAME=>'EMP1',-
> POLICY_NAME=>'CHK_EMP');


PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.


CONN SCOTT/TIGER




SQL> UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20;


3 ROWS UPDATED.


SQL> COMMIT;


COMMIT COMPLETE.


CONN / AS SYSDBA


SQL> SELECT DB_USER,TIMESTAMP , SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;


DB_USER TIMESTAMP SQL_TEXT
--------------- -------------------- ---------------------------------------------
SCOTT 2012-09-15 10:44:39 SELECT * FROM EMP1
SCOTT 2012-09-15 10:45:47 UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788
SCOTT 2012-09-15 10:46:04 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20


------------------启用精细审计
SQL> EXEC DBMS_FGA.ENABLE_POLICY(-
> OBJECT_SCHEMA=>'SCOTT',OBJECT_NAME=>'EMP1',-
> POLICY_NAME=>'CHK_EMP');


PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.


CONN SCOTT/TIGER


SQL> UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20;


3 ROWS UPDATED.


SQL> COMMIT;


COMMIT COMPLETE.


SQL> SELECT DB_USER,TIMESTAMP , SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;


DB_USER TIMESTAMP SQL_TEXT
--------------- -------------------- ---------------------------------------------
SCOTT 2012-09-15 10:44:39 SELECT * FROM EMP1
SCOTT 2012-09-15 10:45:47 UPDATE EMP1 SET SAL=10010 WHERE EMPNO=7788
SCOTT 2012-09-15 10:46:04 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20
SCOTT 2012-09-15 11:00:57 UPDATE EMP1 SET SAL=8000 WHERE DEPTNO=20


----------删除精细审计结果


SQL> DELETE FROM SYS.FGA_LOG$;


4 ROWS DELETED.


SQL> COMMIT;


COMMIT COMPLETE.


FGA 的指导原则


要对所有语句进行审计，可以使用 NULL 条件
POLICY_NAME 必须唯一
在创建 POLICY 时，被审计的表或视图必须已经存在
如果审计条件语法无效，则在访问被审计的对象时会报 ORA-28112 错误。
如果被审计的列在表中不存在，则不会对任何行进行审计。
如果 EVENT HANDLER 不存在，则不会返回任何错误，仍然会创建审计记录。


DBA 审计
具有 SYSDBA 或 SYSOPER 的用户
具有在数据库处于 CLOSED 状态时可以创建 CONNECTION 的权限
audit trail 必须存储在数据库外部
始终对以 sysdba 或 sysoper 身份创建的 connection 进行的审计
可以使用 audit_sys_operations 参数对 sysdba 或 sysoper 进行额外的审计
使用 audit_file_dest 参数指定 audit trail 的位置


获取审计信息
all_def_audit_opts
dba_stmt_audit_opts
dba_priv_audit_opts
dba_obj_audit_opts


获取审计记录信息
dba_audit_trail
dba_audit_exists
dba_audit_object
dba_audit_session
dba_audit_statement


应用审计（通过触发器实现）
用于记载DML操作所引起的数据变化


---------创建审计表


SQL> conn scott/tiger
Connected.
11:59:23 SQL> create table audit_emp_change(
11:59:34 2 name varchar2(10),
11:59:45 3 oldsal number(6,2),
11:59:53 4 newsal number(6,2),
12:00:02 5 time date);


Table created.


Elapsed: 00:00:00.11


---------创建 DML 触发器
12:00:06 SQL> create or replace trigger tr_sal_change
after update of sal on scott.emp
for each row
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change
where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change
values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change
set oldsal=:old.sal,newsal=:new.sal,time=sysdate
where name=:old.ename;
end if;
end;
/




----------执行 DML 操作


12:05:58 SQL> update scott.emp set sal=6000 where empno=7788;


1 row updated.


Elapsed: 00:00:00.08
12:07:31 SQL> commit;


Commit complete.


查看审计结果
12:09:15 SQL> select * from audit_emp_change;


NAME OLDSAL NEWSAL TIME
------------------------------ ---------- ---------- -------------------
SCOTT 3000 6000 2012-09-15 12:07:31


Elapsed: 00:00:00.02

12:09:42 SQL> update scott.emp set sal=7000 where empno=7788;

转载请注明出处及原文链接：

http://blog.csdn.net/xiangsir/article/details/8623350