oracle 安全与审计

来源：互联网发布：李选民淘宝上卖的真吗编辑：程序博客网时间：2024/05/01 17:38

概述

数据库审计是数据库安全的一个中要方面，它实现了操作跟踪功能，是用户对数据库操作的一种历史记录。

审计涉及监控和记录用户对数据库行为的功能记录。

1 审计的5个层次

1.1 强制审计

1.2 标准数据库审计

1.3 基于值得审计（使用触发器定制审计）

1.4 精细化审计(FGA)

1.5 对DBA的审计audit_sys_operations

1.1 强制审计

此审计始终生效，不依赖于数据库某个具体的

初始化参数某个特殊配置，只要以sysdba选项登陆到数据库

就会对此进行记录，记录的信息包括何时在哪个客户端登陆到数据库里等，数据库关闭同样进行审计，因此审计没

有记录在数据库表里而是在操作系统文件里面，对于linux（unix）来说则由初始化参数audit_file_dest的值决定。

SQL> show parameter audit_file_destNAME     TYPE VALUE------------------------------------ ----------- ------------------------------audit_file_dest      string /u01/PROD/db/tech_st/11.2.0/rdbms/audit[root@erpdb01 audit]# ls |wc -l111870[root@erpdb01 audit]#

该参数的默认值是*.aud

每次以sysdba登录数据库，都会产生一个aud文件，具体命名方式是ora_进程号.aud

SQL> select spid, pid from v$process where addr=(                 select paddr from v$session where sid=(                          select sid from  v$mystat where rownum=1));  2    3  SPIDPID------------------------ ----------21445299[root@erpdb01 audit]# ls -lrt *21445*-rw-r----- 1 oracle asmadmin 3443 Jan 15 09:11 PROD1_ora_21445_20160115085402410527143795.aud[root@erpdb01 audit]#

[root@erpdb01 audit]#find . -type f -mtime -1      <span style="font-family: Arial, Helvetica, sans-serif;">最近一天的审计文件</span>./PROD1_ora_79891_20160115094027729485143795.aud./PROD1_ora_79789_20160115094021213100143795.aud./PROD1_ora_79787_20160115094021142287143795.aud./PROD1_ora_9981_20160115003005531034143795.aud./PROD1_ora_9982_20160115003005547047143795.aud./PROD1_j000_87328_20160114233812554238143795.aud./PROD1_ora_9979_20160115003005343001143795.aud./PROD1_ora_40118_20160114204915155270143795.aud./PROD1_ora_104506_20160114174516062778143795.aud./PROD1_ora_21445_20160115085402410527143795.aud./PROD1_j000_78336_20160115033812380370143795.aud./PROD1_ora_9978_20160115003005326551143795.aud./PROD1_ora_9983_20160115003005564732143795.aud./PROD1_j000_101411_20160115113815390053143795.aud./PROD1_ora_40121_20160114204915200891143795.aud./PROD1_ora_9992_20160115003005876064143795.aud./PROD1_ora_46658_20160115010306115894143795.aud./PROD1_ora_9980_20160115003005360734143795.aud[root@erpdb01 audit]# ls -ltr |tail -10-rw-r----- 1 oracle asmadmin    76281 Jan 15 01:26 PROD1_ora_46658_20160115010306115894143795.aud-rw-r----- 1 oracle asmadmin    70482 Jan 15 01:26 PROD1_ora_9992_20160115003005876064143795.aud-rw-r----- 1 oracle asmadmin    37277 Jan 15 01:26 PROD1_ora_9983_20160115003005564732143795.aud-rw-r----- 1 oracle asmadmin  1300956 Jan 15 01:26 PROD1_ora_9982_20160115003005547047143795.aud-rw-r----- 1 oracle asmadmin     1366 Jan 15 03:38 PROD1_j000_78336_20160115033812380370143795.aud-rw-r----- 1 oracle asmadmin     3244 Jan 15 09:40 PROD1_ora_79789_20160115094021213100143795.aud-rw-r----- 1 oracle asmadmin     1368 Jan 15 11:38 PROD1_j000_101411_20160115113815390053143795.aud-rw-r----- 1 oracle asmadmin    61362 Jan 15 11:48 PROD1_ora_79787_20160115094021142287143795.aud-rw-r----- 1 oracle asmadmin    25708 Jan 15 11:48 PROD1_ora_79891_20160115094027729485143795.aud-rw-r----- 1 oracle asmadmin     7414 Jan 15 14:30 PROD1_ora_21445_20160115085402410527143795.aud[root@erpdb01 audit]#

[root@erpdb01 audit]# more PROD1_ora_21445_20160115085402410527143795.aud
Audit file /u01/PROD/db/tech_st/11.2.0/rdbms/audit/PROD1_ora_21445_20160115085402410527143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/PROD/db/tech_st/11.2.0
System name: Linux
Node name: erpdb01.com
Release: 2.6.39-400.128.21.el5uek
Version: #1 SMP Thu Apr 2 15:13:06 PDT 2015
Machine: x86_64
Instance name: PROD1
Redo thread mounted by this instance: 1
Oracle process number: 299
Unix process pid: 21445, image: oracle@erpdb.com (TNS V1-V3

Fri Jan 15 08:54:15 2016 +08:00
LENGTH : '442'
ACTION :[289] 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big
integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDE
R BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[9] '244891841'

Fri Jan 15 09:11:31 2016 +08:00
LENGTH : '357'
ACTION :[204] ' select spid, pid 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/0'
STATUS:[1] '0'
DBID:[9] '244891841'

[root@erpdb01 audit]#

1.2 标准数据库审计

标准审计允许在三种级别上实现审计，即语句级审计，权限级审计以及对象审计，针对不同级别的审计的内容，数据量都有所区别。

语句级审计：指定数据库对象的所有活动的一种审计，如审计某个表上的所有活动。

权限级审计：审计某种权限的所有活动，如对所有create and context语句的审计。

对象级审计：审计特定表上的活动，如对表的dml操作的审计。

初始化参数AUDIT_TRAL设置成true，可以启用标准数据库审计功能。这个是静态参数需要重启数据库

            SQL> show parameter audit_trail                 NAME     TYPE VALUE                 ------------------------------------ ----------- ------------------------------                 audit_trail     string DB, EXTENDED            SQL>

注意：如果audit_trail的值为OS，则说明审计信息放在操作系统文件里面，对于linux来说就是audit_file_dest下面，对于win来说就是放在事件查看器里。

如果audit_trail的值为DB或者TRUE，则说明审计信息放在数据库里面，也就是sys用户的aud$表，可以通过视图dba_audit_trail视图来查看。

如果audit_trail的值为XML，则审计信息直接写入XML格式的文件里，该文件位于audit_file_dest参数指定的路径下，通过v$xml_audit_trail视图查询XML信息。

如果audit_trail的值为DB_EXTENDED，具有DB/TRUE的功能，另外填写AUD$的SQLBIND和SQLTEXT字段

如果audit_trail的值为None默认值，不做审计；
如果audit_trail的值为DB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；
如果audit_trail的值为DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；

建议：DBA应定期对审计信息进行查看和归档，并将归档后的审计信息删除，释放空间。

OS_USERNAME                                                                      USERNAME                       USERHOST                                                                         TIMESTAMP   OWNER                          OBJ_NAME-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ----------- ------------------------------ --------------------------------------------------------------------------------oracle                                                                           APPS                           erpdb01.szgas.com                                                                2016/1/10 2 SYS                            APPS_DATA_FILE_DIRoracle                                                                           APPS                           erpdb01.szgas.com                                                                2016/1/10 2 SYS                            ECX_UTL_LOG_DIR_OBJoracle                                                                           APPS                           erpdb01.szgas.com                                                                2016/1/10 2 SYS                            ECX_UTL_XSLT_DIR_OBJ 317 rows selected SQL>

审计选项（审计范围-审计类型-审计特定用户-审计内容）

审计范围：1.by session

2.by access

审计类型： 1.只审计成功的

2.只审计失败的

3.审计成功的和失败的（默认）

审计内容： 1.审计SQL

SQL> select audit_option,success,failure from dba_stmt_audit_opts; （对所有用户）
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
DIRECTORY BY ACCESS BY ACCESS

SQL> select action_name,count(*) from dba_audit_trail group by action_name; （现有的审计内容）
ACTION_NAME COUNT(*)
---------------------------- ----------
EXECUTE PROCEDURE 257
CREATE DIRECTORY 60

2.审计系统权限

SQL> select privilege,success,failure from dba_priv_audit_opts; （对所有用户）

3.审计对象权限

1.3 基于值得审计（使用触发器定制审计）

在标准审计中，只能记录用户做过什么，但不能记录前后变化的实际值，基于值得审计则可以通过捕获用户进行操作之前和之后的实际值，从而扩展审计

功能，这个功能是通过触发器来实现的。

1.4 精细化审计(FGA)

前面的审计，都只是针对某种操作进行审计。但是如果用户的需求为：只有当用户更新了表的某些数据行的时候才需要审计，更新了其他的数据行则不需

要审计，或者只有检索了某些数据行才需要审计，等等，需要使用到精细化审计。

FGA从oracle 9i开始引入，FGA不但对行和列精细化审计，而且还记录触发审计的语句，通过调用DBMS_FGA包来实现FGA，将需要审计的数据内容作为

一个策略，在数据库里面进行定义，审计信息记录在fga_log$里，通过查询dba_fga_audit_trail可以获得FGA的审计数据。

1.5 对DBA的审计audit_sys_operations

audit_sys_operations默认为false，当设置为true时，所有sys用户（包括以sysdba,sysoper身份登录的用户）的操作都会被记录,audit trail不会写在aud$表中，

这个很好理解，如果数据库还未启动aud$不可用，那么像conn /as sysdba这样的连接信息，只能记录在其它地方。

2. 如何开启审计

2.1 开启强制审计，就修改审计audit_trail和审计信息目录

SQL> select ISSES_MODIFIABLE  from v$parameter where name='audit_trail';ISSES-----FALSESQL> select ISSES_MODIFIABLE  from v$parameter where name='audit_sys_operations';ISSES-----FALSESQL> select isses_modifiable from v$parameter where name ='audit_file_dest';ISSES-----FALSESQL> alter system set audit_sys_operations =true scope=spfile;  重启后生效

2.2 审计选项

AUDIT sql_statement_clause BY {SESSION | ACCESS}WHENEVER [NOT] SUCCESSFUL;

2.3 关闭审计

使用noaudit命令来关闭审计

例如：

SQL> noaudit index by kshelton;Noaudit succeeded.

现有的审计信息如下：

SQL> select audit_option,success,failure from dba_stmt_audit_opts;AUDIT_OPTION SUCCESS    FAILURE---------------------------------------- ---------- ----------ALTER SYSTEM BY ACCESS  BY ACCESSSYSTEM AUDIT BY ACCESS  BY ACCESSCREATE SESSION BY ACCESS  BY ACCESSCREATE USER BY ACCESS  BY ACCESSALTER USER BY ACCESS  BY ACCESSDROP USER BY ACCESS  BY ACCESSPUBLIC SYNONYM BY ACCESS  BY ACCESSDATABASE LINK BY ACCESS  BY ACCESSROLE BY ACCESS  BY ACCESSPROFILE  BY ACCESS  BY ACCESSDIRECTORY BY ACCESS  BY ACCESSAUDIT_OPTION SUCCESS    FAILURE---------------------------------------- ---------- ----------CREATE ANY TABLE BY ACCESS  BY ACCESSALTER ANY TABLE  BY ACCESS  BY ACCESSDROP ANY TABLE BY ACCESS  BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS  BY ACCESSGRANT ANY ROLE BY ACCESS  BY ACCESSSYSTEM GRANT BY ACCESS  BY ACCESSALTER DATABASE BY ACCESS  BY ACCESSCREATE ANY PROCEDURE BY ACCESS  BY ACCESSALTER ANY PROCEDURE BY ACCESS  BY ACCESSDROP ANY PROCEDURE BY ACCESS  BY ACCESSALTER PROFILE BY ACCESS  BY ACCESSAUDIT_OPTION SUCCESS    FAILURE---------------------------------------- ---------- ----------DROP PROFILE BY ACCESS  BY ACCESSGRANT ANY PRIVILEGE BY ACCESS  BY ACCESSCREATE ANY LIBRARY BY ACCESS  BY ACCESSEXEMPT ACCESS POLICY BY ACCESS  BY ACCESSGRANT ANY OBJECT PRIVILEGE BY ACCESS  BY ACCESSCREATE ANY JOB BY ACCESS  BY ACCESSCREATE EXTERNAL JOB BY ACCESS  BY ACCESS29 rows selected.SQL> select privilege,success,failure from dba_priv_audit_opts; PRIVILEGE SUCCESS    FAILURE---------------------------------------- ---------- ----------CREATE EXTERNAL JOB BY ACCESS  BY ACCESSCREATE ANY JOB BY ACCESS  BY ACCESSGRANT ANY OBJECT PRIVILEGE BY ACCESS  BY ACCESSEXEMPT ACCESS POLICY BY ACCESS  BY ACCESSCREATE ANY LIBRARY BY ACCESS  BY ACCESSGRANT ANY PRIVILEGE BY ACCESS  BY ACCESSDROP PROFILE BY ACCESS  BY ACCESSALTER PROFILE BY ACCESS  BY ACCESSDROP ANY PROCEDURE BY ACCESS  BY ACCESSALTER ANY PROCEDURE BY ACCESS  BY ACCESSCREATE ANY PROCEDURE BY ACCESS  BY ACCESSPRIVILEGE SUCCESS    FAILURE---------------------------------------- ---------- ----------ALTER DATABASE BY ACCESS  BY ACCESSGRANT ANY ROLE BY ACCESS  BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS  BY ACCESSDROP ANY TABLE BY ACCESS  BY ACCESSALTER ANY TABLE  BY ACCESS  BY ACCESSCREATE ANY TABLE BY ACCESS  BY ACCESSDROP USER BY ACCESS  BY ACCESSALTER USER BY ACCESS  BY ACCESSCREATE USER BY ACCESS  BY ACCESSCREATE SESSION BY ACCESS  BY ACCESSAUDIT SYSTEM BY ACCESS  BY ACCESSPRIVILEGE SUCCESS    FAILURE---------------------------------------- ---------- ----------ALTER SYSTEM BY ACCESS  BY ACCESS23 rows selected.SQL>

0 0