1.sys用户审计
SQL> alter system set audit_sys_operations=true scope=spfile; --启用操作系统审计
System altered.
SQL> alter system set audit_trail=os scope =spfile;
System altered.
sys用户作如下操作会被记录在windos事件查看器
SQL> insert into test.t values(7);
1 row created.
SQL> commit;
Commit complete.
也可以以xml格式把审计记录写入操作系统文件。
SQL> alter system set audit_trail=xml scope=spfile;
System altered.
sys用户操作以下记录
SQL> insert into test.t values(8);
1 row created.
SQL> commit;
Commit complete.
会在audit_file_dest指定的目当中产生xml文件,可以通过v$xml_audit_trail视图查图审计结果
SQL> select audit_type,session_id,db_user,os_user,action,statement_type from v$xml_audit_trail;
AUDIT_TYPE SESSION_ID DB_USER OS_USER ACTION STATEMENT_TYPE
---------- ---------- -------------------- -------------------- ---------- --------------
8 0 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
8 0 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
2.标准审计
标准审计的结果既可以写入到操作系统文件,也可以把结果记录到数据库中,由audit_trail参数的值来决定.
1)语句审计
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
audit_file_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string OS --审计结果记录在windows事件查看器
审计用户登录
SQL> audit session;
Audit succeeded.
查看审计选项
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
---------- -------------------- -------------------- ------------------------------ ---------------
CREATE SESSION BY ACCESS BY ACCESS
SQL> select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- -------------------- -------------------- ------------------------------ ---------------
CREATE SESSION BY ACCESS BY ACCESS
用test用户登录系统
C:\>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 19 10:08:26 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
在window事件查看器中查看审计结果
Audit trail:
SESSIONID: "224"
ENTRYID: "1"
STATEMENT: "1"
USERID: "TEST"
USERHOST: "WORKGROUP\DEBAOLIU"
TERMINAL: "DEBAOLIU"
ACTION: "100"
RETURNCODE: "0"
COMMENT$TEXT: "Authenticated by: DATABASE"
OS$USERID: "Administrator"
PRIV$USED: 5.
ACTION可以通过查询audit_actions视图查看具体是什么动作
SQL> select * from audit_actions where action=100;
ACTION NAME
---------- ---------------------------------------------
100 LOGON
同样可以把审计结果记录到adump文件下
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
audit_file_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string XML
审计用户登录
SQL>audit session;
test用户登录系统
C:\>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 19 14:39:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
取消审计
SQL> noaudit session;
Noaudit succeeded.
2)权限审计(系统权限审计)
把审计结果保存到数据库表中
SQL> show parameter audi
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
audit_file_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> audit create table by test; --审计test用户创建表
Audit succeeded.
SQL> select *from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
--------------- --------------- -------------------- ------------------------------ ------------
TEST CREATE TABLE BY ACCESS BY ACCESS
使用test用户创建一个表
SQL> create table test(id int);
表已创建。
3)对象审计
SQL> audit select on test.t by session; --审计test用户下t表的访问
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --------- ----- ----- ----- ----- -----
TEST T TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/-
SQL> select *from t;
查看审计结果
SQL> select username,obj_name,action,action_name,audit_option,priv_used,obj_privilege from dba_audit_trail;
USERNAME OBJ_NAME ACTION ACTION_NAME AUDIT_OPTION PRIV_USED OBJ_PRIVILEGE
---------- ---------- ---------- -------------------- -------------------- ------------------------------ ---------------
TEST TEST 1 CREATE TABLE CREATE TABLE
TEST T 103 SESSION REC
by access:同一个语句访问一次记录一次
SQL> select * from all_def_audit_opts;
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --------------- --------------- --------------- --- --- --------------- --- --------- --------------- --------------- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SQL> select * from dba_stmt_audit_opts;
no rows selected
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL> select * from dba_obj_audit_opts;
no rows selected
3.精细度审计
测试表
SQL> select *from a;
ID CODE SAL
---------- ---------- ----------
1 10 20
1 20 30
1 30 40
添加审计策略
SQL> exec dbms_fga.add_policy(object_schema=>'test',object_name=>'a',policy_name=>'audit_a',audit_condition=>'code=10',audit_column=>'sal',enable=>tru
e,statement_types=>'select');
PL/SQL procedure successfully completed.
Security Guide -->http://docs.oracle.com/cd/B19306_01/network.102/b14266/toc.htm
SQL> alter system set audit_sys_operations=true scope=spfile; --启用操作系统审计
System altered.
SQL> alter system set audit_trail=os scope =spfile;
System altered.
sys用户作如下操作会被记录在windos事件查看器
SQL> insert into test.t values(7);
1 row created.
SQL> commit;
Commit complete.
也可以以xml格式把审计记录写入操作系统文件。
SQL> alter system set audit_trail=xml scope=spfile;
System altered.
sys用户操作以下记录
SQL> insert into test.t values(8);
1 row created.
SQL> commit;
Commit complete.
会在audit_file_dest指定的目当中产生xml文件,可以通过v$xml_audit_trail视图查图审计结果
SQL> select audit_type,session_id,db_user,os_user,action,statement_type from v$xml_audit_trail;
AUDIT_TYPE SESSION_ID DB_USER OS_USER ACTION STATEMENT_TYPE
---------- ---------- -------------------- -------------------- ---------- --------------
8 0 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
8 0 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
4 4294967295 sys Administrator 0 0
2.标准审计
标准审计的结果既可以写入到操作系统文件,也可以把结果记录到数据库中,由audit_trail参数的值来决定.
1)语句审计
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
audit_file_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string OS --审计结果记录在windows事件查看器
审计用户登录
SQL> audit session;
Audit succeeded.
查看审计选项
SQL> select * from dba_stmt_audit_opts;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
---------- -------------------- -------------------- ------------------------------ ---------------
CREATE SESSION BY ACCESS BY ACCESS
SQL> select * from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- -------------------- -------------------- ------------------------------ ---------------
CREATE SESSION BY ACCESS BY ACCESS
用test用户登录系统
C:\>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 19 10:08:26 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
在window事件查看器中查看审计结果
Audit trail:
SESSIONID: "224"
ENTRYID: "1"
STATEMENT: "1"
USERID: "TEST"
USERHOST: "WORKGROUP\DEBAOLIU"
TERMINAL: "DEBAOLIU"
ACTION: "100"
RETURNCODE: "0"
COMMENT$TEXT: "Authenticated by: DATABASE"
OS$USERID: "Administrator"
PRIV$USED: 5.
ACTION可以通过查询audit_actions视图查看具体是什么动作
SQL> select * from audit_actions where action=100;
ACTION NAME
---------- ---------------------------------------------
100 LOGON
同样可以把审计结果记录到adump文件下
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
audit_file_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string XML
审计用户登录
SQL>audit session;
test用户登录系统
C:\>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 19 14:39:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
在adump目录(E:\oracle\product\10.2.0\admin\orcl\adump)下生成xml文件.
查看审计内容
SQL> select audit_type,action,db_user,os_user,statement_type,auth_privileges from v$xml_audit_trail;
AUDIT_TYPE ACTION DB_USER OS_USER STATEMENT_TYPE AUTH_PRIVI
---------- ---------- ---------- -------------------- -------------- ----------
8 0 sys Administrator 0
8 0 sys Administrator 0
1 100 TEST Administrator 0
8 0 sys Administrator 0
已有test用户登录的记录.
在adump目录(E:\oracle\product\10.2.0\admin\orcl\adump)下生成xml文件.
查看审计内容
SQL> select audit_type,action,db_user,os_user,statement_type,auth_privileges from v$xml_audit_trail;
AUDIT_TYPE ACTION DB_USER OS_USER STATEMENT_TYPE AUTH_PRIVI
---------- ---------- ---------- -------------------- -------------- ----------
8 0 sys Administrator 0
8 0 sys Administrator 0
1 100 TEST Administrator 0
8 0 sys Administrator 0
取消审计
SQL> noaudit session;
Noaudit succeeded.
把审计结果保存到数据库表中
SQL> show parameter audi
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
audit_file_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB
SQL> audit create table by test; --审计test用户创建表
Audit succeeded.
SQL> select *from dba_priv_audit_opts;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
--------------- --------------- -------------------- ------------------------------ ------------
TEST CREATE TABLE BY ACCESS BY ACCESS
使用test用户创建一个表
SQL> create table test(id int);
表已创建。
查看审计结果
SQL> select username,obj_name,action,action_name,audit_option,priv_used from dba_audit_trail;
USERNAME OBJ_NAME ACTION ACTION_NAME AUDIT_OPTION PRIV_USED
---------- ---------- ---------- -------------------- -------------------- ---------------------
TEST TEST 1 CREATE TABLE CREATE TABLE
SQL> select username,obj_name,action,action_name,audit_option,priv_used from dba_audit_trail;
USERNAME OBJ_NAME ACTION ACTION_NAME AUDIT_OPTION PRIV_USED
---------- ---------- ---------- -------------------- -------------------- ---------------------
TEST TEST 1 CREATE TABLE CREATE TABLE
3)对象审计
SQL> audit select on test.t by session; --审计test用户下t表的访问
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --------- ----- ----- ----- ----- -----
TEST T TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/-
SQL> select *from t;
查看审计结果
SQL> select username,obj_name,action,action_name,audit_option,priv_used,obj_privilege from dba_audit_trail;
USERNAME OBJ_NAME ACTION ACTION_NAME AUDIT_OPTION PRIV_USED OBJ_PRIVILEGE
---------- ---------- ---------- -------------------- -------------------- ------------------------------ ---------------
TEST TEST 1 CREATE TABLE CREATE TABLE
TEST T 103 SESSION REC
SQL> audit select on test.t by session whenever not successful; --查询不成功的时候审计
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --------- ----- ----- ----- ----- -----
TEST T TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/S -/- -/- -/- -/- -/- -/- -/-
SQL> audit select on test.t by session whenever successful;
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --------- ----- ----- ----- ----- -----
TEST T TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/- -/- -/- -/- -/- -/- -/- -/-
by session:同一个语句只记录一次
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --------- ----- ----- ----- ----- -----
TEST T TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/S -/- -/- -/- -/- -/- -/- -/-
SQL> audit select on test.t by session whenever successful;
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
OWNER OBJECT_NAM OBJECT_TYP ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- --------- ----- ----- ----- ----- -----
TEST T TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/- -/- -/- -/- -/- -/- -/- -/-
by access:同一个语句访问一次记录一次
SQL> select * from all_def_audit_opts;
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --------------- --------------- --------------- --- --- --------------- --- --------- --------------- --------------- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SQL> select * from dba_stmt_audit_opts;
no rows selected
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL> select * from dba_obj_audit_opts;
no rows selected
3.精细度审计
测试表
SQL> select *from a;
ID CODE SAL
---------- ---------- ----------
1 10 20
1 20 30
1 30 40
添加审计策略
SQL> exec dbms_fga.add_policy(object_schema=>'test',object_name=>'a',policy_name=>'audit_a',audit_condition=>'code=10',audit_column=>'sal',enable=>tru
e,statement_types=>'select');
PL/SQL procedure successfully completed.
查看审计策略
SQL> select * from dba_audit_policies;
OBJECT_SCH OBJEC POLICY_NA POLICY_TEX POLICY_COLUM PF_SC PF_PACKAGE PF_FUNCTIO ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COLU
---------- ----- --------- ---------- ------------ ----- ---------- ---------- --------- ----- ----- ----- ----- --------------- -----------
TEST A AUDIT_A code=10 SAL YES YES NO NO NO DB+EXTENDED ANY_COLUMNS
参考:
SQL> select * from dba_audit_policies;
OBJECT_SCH OBJEC POLICY_NA POLICY_TEX POLICY_COLUM PF_SC PF_PACKAGE PF_FUNCTIO ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COLU
---------- ----- --------- ---------- ------------ ----- ---------- ---------- --------- ----- ----- ----- ----- --------------- -----------
TEST A AUDIT_A code=10 SAL YES YES NO NO NO DB+EXTENDED ANY_COLUMNS
SQL> select id,sal from a where code=10; --查询表
ID SAL
---------- ----------
1 20
查看审计结果
SQL> select db_user,object_name,policy_name,statement_type,sql_text,sql_bind from dba_fga_audit_trail;
DB_USER OBJEC POLICY_NA STATEMENT_TYPE SQL_TEXT SQL_BIND
---------- ----- --------- --------------------- ------------------------------------------------------------ -------------
TEST A AUDIT_A SELECT select * from a
TEST A AUDIT_A SELECT select id,sal from a where code=10
TEST A AUDIT_A SELECT select *from a
SQL> exec dbms_fga.disable_policy(object_schema=>'test',object_name=>'a',policy_name=>'audit_a'); --禁用审计策略
PL/SQL procedure successfully completed.
SQL> select * from dba_audit_policies;
OBJECT_SCH OBJEC POLICY_NA POLICY_TEX POLICY_COLUM PF_SC PF_PACKAGE PF_FUNCTIO ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COL
---------- ----- --------- ---------- ------------ ----- ---------- ---------- --------- ----- ----- ----- ----- --------------- ----------
TEST A AUDIT_A code=10 SAL NO YES NO NO NO DB+EXTENDED ANY_COLUMN
ID SAL
---------- ----------
1 20
查看审计结果
SQL> select db_user,object_name,policy_name,statement_type,sql_text,sql_bind from dba_fga_audit_trail;
DB_USER OBJEC POLICY_NA STATEMENT_TYPE SQL_TEXT SQL_BIND
---------- ----- --------- --------------------- ------------------------------------------------------------ -------------
TEST A AUDIT_A SELECT select * from a
TEST A AUDIT_A SELECT select id,sal from a where code=10
TEST A AUDIT_A SELECT select *from a
SQL> exec dbms_fga.disable_policy(object_schema=>'test',object_name=>'a',policy_name=>'audit_a'); --禁用审计策略
PL/SQL procedure successfully completed.
SQL> select * from dba_audit_policies;
OBJECT_SCH OBJEC POLICY_NA POLICY_TEX POLICY_COLUM PF_SC PF_PACKAGE PF_FUNCTIO ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COL
---------- ----- --------- ---------- ------------ ----- ---------- ---------- --------- ----- ----- ----- ----- --------------- ----------
TEST A AUDIT_A code=10 SAL NO YES NO NO NO DB+EXTENDED ANY_COLUMN
Security Guide -->http://docs.oracle.com/cd/B19306_01/network.102/b14266/toc.htm
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26937943/viewspace-1222421/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26937943/viewspace-1222421/