实验-审计信息的清理和策略关闭.txt

最新推荐文章于 2022-01-10 21:58:15 发布

cuili9760

最新推荐文章于 2022-01-10 21:58:15 发布

阅读量365

点赞数

文章标签：数据库操作系统

实验-审计（二）

以前总是考虑如何开启审计，对于如何终止、清除，了解不是很多。
今天，发现我的dba_common_Audit_trail视图中有以下记录：
SQL> column userhost format a30
SQL> column sql_text format a30
SQL> select audit_type,db_user,userhost,sql_text from dba_common_audit_trail;

AUDIT_TYPE             DB_USER                        USERHOST                       SQL_TEXT
---------------------- ------------------------------ ------------------------------ ------------------------------
Mandatory XML Audit    /                              localhost.localdomain          CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Fine Grained Audit     SCOTT                          localhost.localdomain
Fine Grained Audit     TERRY                          localhost.localdomain          delete from scott.emp where em
                                                                                     pno=7369

记得以前在oracle文档中看到过，dba_common_audit_trail是有aud$\fga_log$两个基表得到的。
但两表的查询结果为：

SQL> select userhost from aud$;

USERHOST
------------------------------

SQL> select dbuid,lsqltext from fga_log$;

DBUID                          LSQLTEXT
------------------------------ --------------------------------------------------------------------------------
SCOTT
TERRY                          delete from scott.emp where empno=7369

前面dba_common_audit_trail中audit_type为Mandatory XML Audit 的记录是对sys用户的连接所作的审计。

对sys或其他sysdba、sysoper权限用户的审计与audit_trail参数的设置无关。
从DB_USER可以看到，在两个基表中未找到的都是sys用户的记录。因此进行以下的参数查看：

Last login: Sat May 25 21:24:26 2013 from 192.168.56.1
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 26 01:00:01 2013

SQL> conn /as sysdba
sConnected.
SQL> show parameter audit_sys

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string

以上audit_sys_operations参数是我下午关闭的。

SQL> how parameter audit_trail;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string XML, EXTENDED

在audit_trail的值为“XML，EXTENDED”时，sys及其他sysdba、sysoper用户的登录信息被保存在操作系统文件中。
文件位置为：

SQL> show parameter audit_file_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/admin/testdb/adump
SQL> host
[oracle@localhost ~]$ cd /u01/admin/testdb/adump
[oracle@localhost adump]$ ls
adx_testdb.txt         testdb_ora_8643_1.xml testdb_ora_8894_1.xml
testdb_ora_8596_1.xml testdb_ora_8747_1.xml testdb_ora_8897_1.xml
testdb_ora_8630_1.xml testdb_ora_8892_1.xml testdb_ora_9049_1.xml
注意这里是八个xml文件。
[oracle@localhost adump]$ tail adx_testdb.txt
/u01/admin/testdb/adump
testdb_ora_8747_1.xml
/u01/admin/testdb/adump
testdb_ora_8892_1.xml
/u01/admin/testdb/adump
testdb_ora_8894_1.xml
/u01/admin/testdb/adump
testdb_ora_8897_1.xml
/u01/admin/testdb/adump
testdb_ora_9049_1.xml
[oracle@localhost adump]$ more testdb_ora_9049_1.xml
<?xml version="1.0" encoding="UTF-8"?>
http://xmlns.oracle.com/oracleas/schema/dbserver_audittrail-11_2
.xsd"
   xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation=" http://xmlns.oracle.com/oracleas/schema/dbserver_audittra
il-11_2.xsd">
   11.2
812
013-05-26T05:00:04.358321Z/orac
lelocalhost.localdomain9049ss>pts/000
Returncode>SYSDBA2581100181
CONNECT

[oracle@localhost adump]$ exit
exit

SQL> select audit_type,db_user,userhost,sql_text from dba_common_audit_trail;

AUDIT_TYPE             DB_USER                        USERHOST                       SQL_TEXT
---------------------- ------------------------------ ------------------------------ ------------------------------
Mandatory XML Audit    /                              localhost.localdomain          CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    sys                            WORKGROUP\TIANLEI              CONNECT
Mandatory XML Audit    /                              localhost.localdomain          CONNECT
Fine Grained Audit     SCOTT                          localhost.localdomain
Fine Grained Audit     TERRY                          localhost.localdomain          delete from scott.emp where em
                                                                                     pno=7369

10 rows selected

以上有八条记录是db_user为sys或/.其实/是我在用secureCRT时操作系统验证方式登录。
与上面的记录数相符。

其实sys用户的审计，就是修改以上的两个参数：
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=XML, EXTENDED SCOPE=SPFILE;
然后重启数据库后生效的。

现在我们来清空记录，停止该审计。

（一）自动清空审计记录【oracle 11gR2 security guide 368页 Purging Audit Trail Records】
1.清空之前，可能需要调整在线日志和归档日志的大小。因为审计表清除的这段时间会产生审计记录。
2.计划好时间和归档策略
3.初始化审计跟踪清除操作
1）首先，执行清空的用户需要对dbms_audit_mgmt的execute权限。
2）然后运行dbms_audit_mgmt.init_cleanup过程
格式：
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => xxxxx,
DEFAULT_CLEANUP_INTERVAL => xx );
END;
/

AUDIT_TRAIL_TYPE可能的取值有：
标准审计     DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
细粒度审计   DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
以上两种     DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
操作系统审计 DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
XML操作系统审计文件–DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.
操作系统和XML格式文件–DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.
所有的跟踪记录–DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types.

也就是说dbms_audit_mgmt.audit_trail_all是最全、最彻底的解决方式。

DEFAULT_CLEANUP_INTERVAL是默认的清除间隔时间。以小时计算。
可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY修改。

4.可以为审计记录设置归档时间戳
DBA_AUDIT_MGMT_LAST_ARCH_TS视图中有上一次归档时间

5.创建和安排清空作业日程
使用dbms_audit_mgmt.create_purge_job
如：
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--可取值有五种，同上
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );--true表示删除上次审计归档时间之前的记录，false表示删除所有
END;
/

6.可选，配置审计记录按批删除
默认是每一万条数据库审计记录删除一次或一千个操作系统审计文件删除一次。
可修改。

按照以上的指导，我们删除所有操作系统和XML格式文件的审计记录：

SQL> BEGIN
2 DBMS_AUDIT_MGMT.INIT_CLEANUP(
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
4 DEFAULT_CLEANUP_INTERVAL => 24 );
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,
4 AUDIT_TRAIL_PURGE_INTERVAL => 24,
5 AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
6 USE_LAST_ARCH_TIMESTAMP => TRUE );
7 END;
8 /

PL/SQL procedure successfully completed.

但设置完成后并未立即其作用。

（二）手动清空审计记录
1.使用DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL进行清除，如：
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,--可选项同上
USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

实际中，我要将所有审计记录都清空，因此clean_audit_trail时，
audit_trail_type是DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
可以这样做的前提是init_cleanup时也制定的同样的audit_trail_type。
尝试再次指定：

SQL> BEGIN
2 DBMS_AUDIT_MGMT.INIT_CLEANUP(
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
4 DEFAULT_CLEANUP_INTERVAL => 24 );
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-46265: Subset of audit trail is already initialized
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 752
ORA-06512: at line 2

这时要取消该初始化，再重新设置：

SQL> BEGIN
2 DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-46258: Cleanup not initialized for the audit trail
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1086
ORA-06512: at line 2

SQL> BEGIN
2 DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
4 END;
5 /

PL/SQL procedure successfully completed.

重新指定init_cleanup：

SQL> BEGIN
2 DBMS_AUDIT_MGMT.INIT_CLEANUP(
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
4 DEFAULT_CLEANUP_INTERVAL => 24 );
5 END;
6 /

PL/SQL procedure successfully completed.

清除：

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, FALSE);

PL/SQL procedure successfully completed

SQL> select audit_type,db_user,userhost,sql_text from dba_common_audit_trail;

6 rows selected

以上执行了所有审计文件的清除，仍未将操作系统的XML格式文件删除，怀疑是由于我将audit_sys_operations 设置为false导致的。

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ cd /u01/admin/testdb/adump
[oracle@localhost adump]$ ls
adx_testdb.txt testdb_ora_8747_1.xml testdb_ora_9049_1.xml
testdb_ora_8630_1.xml testdb_ora_8892_1.xml
testdb_ora_8643_1.xml testdb_ora_8897_1.xml
[oracle@localhost adump]$ mkdir temp
[oracle@localhost adump]$ mv test* temp/
[oracle@localhost adump]$ ls
adx_testdb.txt temp

登录后再查看：
SQL> select audit_type,db_user,userhost,sql_text from dba_common_audit_trail;

AUDIT_TYPE DB_USER USERHOST SQL_TEXT
---------------------- ------------------------------ ------------------------------ ------------------------------

对于sys用户的审计，仅仅将设置为false是不够的。仍会在dba_common_audit_trail中产生记录。
还需要将audit_trail设置为NONE：

SQL> alter system set audit_trail=NONE scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 405929984 bytes
Fixed Size                  1336848 bytes
Variable Size             306186736 bytes
Database Buffers           92274688 bytes
Redo Buffers                6131712 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost adump]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 26 02:23:21 2013

SQL> conn /as sysdba
Connected.
SQL> select audit_type,db_user,userhost,sql_text from dba_common_audit_trail;

可以看到以前生成的登录的记录和关闭数据库的记录。修改audit_trail并重启数据库后，不会在再生成这样的审计记录了。
细粒度审计策略的停止和删除：

SQL> select object_schema,object_name,policy_name from dba_audit_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME
------------------------------ ------------------------------ ------------------------------ --------------
SCOTT EMP SYS ORDERS_FGA_POL

SQL> exec DBMS_FGA.DISABLE_POLICY('SCOTT','EMP','ORDERS_FGA_POL');

PL/SQL procedure successfully completed

SQL> exec DBMS_FGA.DROP_POLICY('SCOTT','EMP','ORDERS_FGA_POL');

PL/SQL procedure successfully completed

SQL> select object_schema,object_name,policy_name from dba_audit_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME
------------------------------ ------------------------------ ------------------------------

SQL>

（三）查看

从dba_auditxxxx视图中可以看到审计的设置信息。

若希望以后不再开启审计，相关视图不需要，可以用操作系统特定位置的CATNOAUD.SQL脚本将相关视图删除。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-765440/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-765440/