15、oracle审计
学习文档(http://blog.csdn.net/rlhua/article/category/1638551)
http://www.cnblogs.com/remote-antiquity/p/6920065.html
1、数据库安全及审计的责任
责任分离
2、标准审计
启用数据库审计---》指定审计选项---》复查审计信息---》维护审计
audit_trail
启用数据库审计
sql> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
audit_trail选项有 none |os| DB| db_extended | xml |xml_extended 6个
audit_file_dest表示存储的位置
os审计： 操作系统目录
db或者true: 表示保存在 aud$ 这张表
db_extended: sys.aud$包括（sqltext）
指定审计选项：
(1)审计sql语句 audit table; //审计DDL，表结构有关
(2)审计系统权限(非重点和重点)
audit select any table,create any trigger;
audit select any table by hr by session; //审计hr用户 by session 记录一次
(3)审计对象权限
audi all on hr.employees;
audit update,delete on hr.employees by access; //审计hr一张表的（update,delete ）发生多少次，记录多少次
系统默认审计有哪些呢？
alter any procedure;
alter any table;
alter database;
alter profile;
alter system;
alter user;
create any job;
create any library;
create any table;
create any procedure;
create external job;
create public database link;
create session;
create user;
grant any object privilege;
grant any privilege;
grant any role;
drop any procedure;
drop any table;
drop profile;
drop user;
exempt access policy;
system audit by access;
role by access;
审计 AUD$表 默认在sys表空间，将来审计内容越来越多，为了防止sys表空间过大，现在改变AUD$表的默认表空间
select owner,segment_name,tablespace_name from dba_segments where segment_name='AUD$';
OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------ -----------------------------
-SYS AUD$ SYSTEM
alter table AUD$ move tablespace users; //移动到users表空间
sql> alter table AUD$ move tablespace users;
Table altered.
实验1：scott登录
truncate table AUD$;//清空之前审计内容
假如 scott登录 多了一条审计信息
select count(*) from AUD$;
desc dba_audit_trail;
select username,timestamp,ses_actions,obj_name,action_name from dba_audit_trail;
USERNAME TIMESTAMP SES_ACTIONS OBJ_NAME A
CTION_NAME------------------------------ ------------------- ------------------- --------------------
------------------------------------------------------------------------------------------------------------ ----------------------------SCOTT 2017-06-24 20:22:09 logoN
实验2：
关闭create session 这个默认审计
noaudit create session;
实验3：审计一个用户HR的表操作
audit delete any table by scott by session;打开
audit table by scott by access;（审计scott用户每一次对表的CREATE、DROP、Truncate操作）。
create table a3 as select * from dept;
sql> select username,action_name from dba_audit_trail;
USERNAME TIMESTAMP SES_ACTIONS OBJ_NAME ACTION_NAME
SCOTT 2017-06-24 21:10:50 A3 CREATE TABLE
查看数据库语句级别都开了那些审计？
desc dba_stmt_audit_opts;
sql> col USER_NAME for a10;
sql> col AUDIT_OPTION for a30;
sql> col SUCCESS for a10;
sql> col FAILURE for a10;
select USER_NAME,AUDIT_OPTION,SUCCESS,FAILURE from dba_stmt_audit_opts;
USER_NAME AUDIT_OPTION SUCCESS FAILURE
---------- ------------------------------ ---------- ----------
ALTER SYSTEM BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
SCOTT TABLE BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
SCOTT DELETE ANY TABLE BY SESSION BY SESSION
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
29 rows selected.
查询系统开启那些审计？
desc dba_priv_audit_opts
select USER_NAME,privilege,FAILURE from dba_priv_audit_opts;
对象审计：
desc dba_obj_audit_opts;
默认审计
desc all_def_audit_opts;
3、基于值的审计（自定义触发器）
用户进行更改---》触发器---》触发器创建了审计记录。
4、细粒度审计(更加浪费资源)
审计 select insert update delete merge
可链接到表或视图的一列或者多列。
使用存储过程DBMS_FGA
FGA策略：
审计标准
审计操作
使用方法创建
DBMS_FGA.add_policy
例子：
DBMS_FGA.add_policy(
object_schema =>'HR',
object_name =>'EMPLOYEES',
policy_name =>'audit_emps_salary',
audit_condition =>'department_id=10',
audit_column =>'SALARY,COMMISSION_PCT',
handler_schema =>'secure',
handler_module =>'log_emps_salary',
enable =>TRUE,
statement_type =>'SELECT,UPDATE');
审计结果保存在这张表 dba_fga_audit_trail
sql> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
POLICY_OWNER VARCHAR2 IN DEFAULT
审计scott用户 emp 表 empno=7788 这sal,comm 2列
begin
dbms_fga.add_policy(
OBJECT_SCHEMA =>'scott',
OBJECT_NAME =>'emp',
POLICY_NAME =>'audit_emp',
AUDIT_CONDITION =>'empno=7788',
AUDIT_COLUMN =>'sal,comm',
ENABLE =>true,
STATEMENT_TYPES =>'select,update'
);
end;
1 begin
2 dbms_fga.add_policy(
3 OBJECT_SCHEMA =>'scott',
4 OBJECT_NAME =>'emp',
5 POLICY_NAME =>'audit_emp',
6 AUDIT_CONDITION =>'empno=7788',
7 AUDIT_COLUMN =>'sal,
8 ENABLE =>true,
9 STATEMENT_TYPES =>'select,update'
10 );
11* end;
sql> /
PL/sql procedure successfully completed.
select count(*) from fga_log$;
truncate table fga_log$;
查看审计结果
select TIMESTAMP,DB_USER,OS_USER,OBJECT_SCHEMA,OBJECT_NAME,sql_TEXT from dba_fga_audit_trail;
select EMPNO,ENAME,SAL,COMM from emp where empno=7788;
查看都有哪些策略？
desc dba_audit_policies;
sql> select OBJECT_SCHEMA,POLICY_NAME from dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME
SCOTT EMP AUDIT_EMP
如何删除 AUDIT_EMP 策略；
desc dbms_fga;
begin
dbms_fga.drop_policy(
object_schema =>'SCOTT',
object_name =>'EMP',
policy_name =>'AUDIT_EMP');
end;
5、审计sysdba、维护审计线索和OAV
审计线索存储在数据库外部
总是审计sysdba
可以使用audit_sys_operations启用sysdba操作的附加审计
audit_file_dest提供审计线索
sql> show parameter audi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
rm -rf /u01/app/oracle/admin/orcl/adump/*
打开可以审计sysdba精确的操作，可以查看sysdba都干了什么？？
alter system set audit_sys_operations=true scope=spfile;
重启数据库。
select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
sql> select spid from v$process where addr=(select paddr from v$session where sid=(select s
id from v$mystat where rownum=1));
SPID
------------------------
9144
ps -ef| grep 9144
[oracle@oel adump]$ ps -ef| grep 9144
oracle 9144 9143 0 20:01 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS
=(PROTOCOL=beq)))oracle 9162 8116 0 20:03 pts/3 00:00:00 grep 9144
sql> select * from scott.emp;
[oracle@oel adump]$ ll
total 4
-rw-r----- 1 oracle oinstall 1498 Jun 25 20:03 orcl_ora_9144_1.aud
[oracle@oel adump]$ pwd
/u01/app/oracle/admin/orcl/adump
查看审计的具体内容：
cat orcl_ora_9144_1.aud
Sun Jun 25 20:03:00 2017 +08:00
LENGTH : '274'
ACTION :[119] 'select spid from v$process where addr=(select paddr from v$session where sid
=(select sid from v$mystat where rownum=1))'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '1471082119'
Sun Jun 25 20:06:21 2017 +08:00
LENGTH : '177'
ACTION :[23] 'select * from scott.emp'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '1471082119'
维护审计
定期的删除aud$标准化审计这张表及 fga_log$细粒度表
定期审计sysdba存储目录 /u01/app/oracle/admin/orcl/adump
