Oracle11g温习-第十九章：审计（audit）
2013年4月27日 星期六
10:52
|
1、审计的功能：监控用户在database 的 action （操作） |
|
|
2、审计分类 |
|
|
1） session ：在同一个session，相同的语句只产生一个审计结果（默认） 2） access ： 在同一个session，每一个语句产生一个审计结果 |
|
|
3、启用审计（默认不启用） |
|
|
|
|
|
4、审计的对象：（默认情况：session ，对成功和不成功的同时审计) |
|
|
1）语句审计 ——创建语句审计 SYS @ prod > audit table; 【涉及到table关键字的都会列入审计内容】 Audit succeeded. SYS @ prod > noaudit table; 【关闭审计】 SYS @ prod > desc dba_audit_tail; SYS @ prod > audit table by tom; 【对用户tom进行语句审计】 Audit succeeded.
SYS @ prod > audit table by tom whenever successful ; 【对用户tom进行成功的语句审计，失败的语句不审计】 Audit succeeded. ——查看审计设置 SYS @ prod > select user_name,audit_option from dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION ------------------------------ ---------------------------------------- SCOTT TABLE SCOTT @ prod >drop table emp1; Table dropped.
SCOTT @ prod >create table emp1 as select * from emp; Table created.
SYS @ prod > conn tom/tom Connected.
TOM @ prod > create table t01 (id int); Table created.
TOM @ prod > drop table t01 purge; Table dropped. TOM @ prod > conn /as sysdba Connected.
SYS @ prod > alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered.
SYS @ prod > col username for a10 SYS @ prod > col obj_name for a10 SYS @ prod > desc dba_audit_tail ; SYS @ prod >select USERNAME,TIMESTAMP,OBJ_NAME,ACTION_NAME from dba_audit_trail;
USERNAME TIMESTAMP OBJ_NAME ACTION_NAME ---------- ------------------- ---------- ---------------------------- SCOTT 2013-01-18 08:31:43 DEPT1 DROP TABLE SCOTT 2013-01-18 08:32:33 DEPT DROP TABLE SCOTT 2013-01-18 08:32:51 EMP1 DROP TABLE SCOTT 2013-01-18 08:33:38 EMP1 CREATE TABLE
2）权限审计
——创建权限审计
SYS @ prod > audit create table; 【只审计create 语句，其他语句不审计 】 Audit succeeded.
SYS @ prod > conn scott/tiger Connected.
SCOTT @ prod > create table dept1 as select * from dept;
Table created.
SCOTT @ prod > drop table dept1 purge; 【不经过回收站，直接将文件删除】 Table dropped.
SCOTT @ prod > conn /as sysdba Connected.
SCOTT @ prod > select USERNAME,TIMESTAMP,OWNER,OBJ_NAME,ACTION_NAME from dba_audit_trail;
USERNAME TIMESTAMP OBJ_NAME ACTION_NAME ---------- --------- ---------- ---------------------------- SCOTT 11-AUG-11 DEPT1 CREATE TABLE
3）对象审计 ——创建对象审计
SYS @ prod > audit all on scott.emp1; 【在某个对象上建立审计】 Audit succeeded.
SYS @ prod > conn scott/tiger Connected.
SCOTT @ prod > select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 100 40 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
SCOTT @ prod >update emp1 set sal=9000 where empno=7788; 1 row updated.
SCOTT @ prod > delete from emp1 where rownum<2; 1 row deleted.
SCOTT @ prod >conn /as sysdba Connected.
SYS @ prod >select username,ses_actions,obj_name,to_char(timestamp,'yyyy-mm-dd HH24:MI:SS') from dba_audit_trail;
USERNAME SES_ACTIONS OBJ_NAME TO_CHAR(TIMESTAMP,' ---------- ------------------- ---------- ------------------- SCOTT ---S-----SS----- EMP1 2013-01-18 08:45:34
【其中S表示successful ，表示在这个位置操作是成功的，F表示failure 失败，B表示both，两者都有】。 |
|
|
5、精细审计Fine Grained Auditing (FGA)
用于审计用户在特定数据行或列上的SQL操作 精细审计是通过DBMS_FGA包实现。 |
|
|
——建立审计策略 SYS @ prod >exec dbms_fga.add_policy(object_schema=>'scott',object_name=>'emp',policy_name=>'chk_emp', audit_condition =>'deptno=20',audit_column =>'sal', statement_types =>'update,select');
【policy_name=> 可以随便写】
PL/SQL procedure successfully completed.
SYS @ prod > conn scott/tiger Connected.
SCOTT @ prod > select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 100 40 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
SCOTT @ prod > select * from emp where deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
SCOTT @ prod > update emp set deptno=10 where empno=7788; 1 row updated.
SCOTT @ prod > update emp set sal=8000 where empno=7788; 1 row updated.
SCOTT @ prod > update emp set sal=8000 where deptno=20; 4 rows updated.
SCOTT @ prod > commit; Commit complete.
【验证审计结果 结果存放于 dba_fga_audit_trail 视图】
SCOTT @ prod > conn /as sysdba Connected.
SYS @ prod > select db_user,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') "time" ,sql_text from dba_fga_audit_trail;
DB_USER time SQL_TEXT ---------- ------------------- -------------------------------------------------- SCOTT 2011-08-11 11:31:42 select * from emp SCOTT 2011-08-11 11:31:49 select * from emp where deptno=20 SCOTT 2011-08-11 11:32:12 update emp set sal=8000 where empno=7788 SCOTT 2011-08-11 11:32:21 update emp set sal=8000 where deptno=20
——【精细审计结果存放到fga_log$的基表里，通过dba_fga_audit_trail 查看。】
SYS @ prod > select count(*) from fga_log$;
COUNT(*) ---------- 4
SYS @ prod > delete from fga_log$; 4 rows deleted.
SYS @ prod > select db_user,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') "time" ,sql_text from dba_fga_audit_trail; no rows selected
——【禁止精细审计】 SYS @ prod > exec dbms_fga.disable_policy( object_schema=>'scott',object_name=>'emp', policy_name=>'chk_emp'); PL/SQL procedure successfully completed.
——【激活精细审计】 SYS @ prod > exec dbms_fga.enable_policy( object_schema=>'scott',object_name=>'emp', policy_name=>'chk_emp'); PL/SQL procedure successfully completed.
——【删除FGA策略】 SYS @ prod > exec dbms_fga.drop_policy( object_schema=>'scott',object_name=>'emp', policy_name=>'chk_emp'); PL/SQL procedure successfully completed.
——【删除精细审计的结果】 SYS @ prod > delete from sys.fga_log$; |
|
|
6、应用审计（通过触发器来实现）——用于记载DML操作所引起的数据变化 |
|
|
1）建立审计表（用来存放审计结果） SCOTT @ prod > create table audit_emp_change ( name varchar2(10), oldsal number(6,2), newsal number(6,2) ,time date); Table created. 2）建立DML 触发器
3）执行DML操作 SCOTT @ prod > update scott.emp set sal=6000 where empno=7788; 1 row updated.
4）查看审计结果
SYS @ prod > select name,oldsal,newsal, to_char(time,'YYYY-MM-DD HH24:MI') FROM AUDIT_EMP_CHANGE;
NAME OLDSAL NEWSAL TO_CHAR(TIME,'YY ---------- ---------- ---------- ---------------- SCOTT 2000 6000 2011-03-03 04:28 |
