15、oracle审计

发布时间：2020-12-12 14:47:02 所属栏目：百科来源：网络整理

导读：15、oracle审计学习文档(http://blog.csdn.net/rlhua/article/category/1638551) http://www.cnblogs.com/remote-antiquity/p/6920065.html 1、数据库安全及审计的责任责任分离 2、标准审计启用数据库审计---》指定审计选项---》复查审计信息---》维护审

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审计：操作系统目录

none: 表示关闭数据库审计

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;

SQL> get fga_audit_emp.sql

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'

可以看到sysdba用户执行了这条sql都被记录下来哦。

维护审计

定期的删除aud$标准化审计这张表及 fga_log$细粒度表

定期审计sysdba存储目录 /u01/app/oracle/admin/orcl/adump

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!