Shulou(Shulou.com)06/01 Report--

Talking about the Audit Management of Oracle11gR2

Author: Zhao full text net name: guestart

Among the security features of Oracle database, audit is regarded as a particularly important aspect. The audit function of the database is mainly used to audit various types of DDL and DML statements. As a new feature, audit management is introduced into the 11g R1 version of Oracle. At this time, its audit function is not powerful and there are many bug. However, when it comes to 11gR2, a lot of bug has been repaired and its audit function has been further enhanced.

Today I would like to share with you some features about auditing in the version of Oracle 11gR2. Due to the US Security Act, the 11g version of Oracle changed its audit management policy. The default value of the initialization parameter AUDIT_TRAIL is' DB', which means that all audit data is stored in the AUD$ table, which is in the SYSTEM table by default.

When we deploy a set of Oracle databases in the production environment, the default audit function is enabled. When the business is just online and the amount of data is small, the capacity of the SYSTEM tablespace is very abundant and there is no pressure. When the business is running for a period of time, suddenly one day, the front-end application will reflect that the database is particularly slow. At this time, when we DBA do various checks on the database, we will find that the percentage of used space in SYSTEM tablespaces is 99.97%, which is no exaggeration. I have personally experienced this. We say that this is to put out a fire afterwards, but in fact, we can migrate the AUD$ table to other tablespaces dedicated to storing audit data before launch, evaluate the amount of audit data after the business is online and run for a period of time, and then set up the maintenance policy for audit data.

Next, we take three ways to set up the audit of the Oracle database. Strictly speaking, there are two methods, the first is to turn off the audit function directly, which is to set the initialization parameter AUDIT_TRAIL to 'NONE', and then restart the database to make it effective; the second and the third is to turn on the maintenance and management of the database audit function. In a production environment, the first is not recommended because after the audit is turned off, it is not conducive to troubleshooting and analysis after the database has a security risk. In order for you to understand, I will also demonstrate the operation.

First, look at the version of the database. The environment I demonstrate is Oracle 11.2.0.4.0.

The first method is to turn off the audit function.

The second method is to migrate the AUD$ table in the SYSTEM tablespace to other tablespaces to reduce the pressure on the SYSTEM tablespace.

(1) query the tablespace where the AUD$ table resides

(2) query the amount of data in the AUD$ table and find that there are more than 80 gigabytes

(3) create a separate tablespace AUDIT_TBS dedicated to AUD$ tables

(4) migrate AUD$ tables to a new tablespace AUDIT_TBS

This is implemented by using the stored procedure SET_AUDIT_TRAIL_LOCATION in the package DBMS_AUDIT_MGMT that comes with Oracle, which accepts two parameters, in the order of AUDIT_TRAIL_TYPE and AUDIT_TRAIL_LOCATION_VALUE, see the screenshot below in the official documentation

Parameter AUDIT_TRAIL_TYPE has the following values. See the screenshot below in the official document.

Various values are explained in Chinese as follows:

All audit types of AUDIT_TRAIL_ALL, including standard database audit, fine-grained audit, operating system audit, and XML file audit

AUDIT_TRAIL_AUD_STD standard database audit

AUDIT_TRAIL_DB_STD standard database audit and fine-grained audit

AUDIT_TRAIL_FGA_STD fine-grained audit

Audit of AUDIT_TRAIL_FILES operating system and XML files

AUDIT_TRAIL_OS operating system audit, audit data are stored in operating system files

AUDIT_TRAIL_XML XML file audit, audit data are stored in XML file

Here, we use the standard database audit, so we use the parameter AUDIT_TRAIL_AUD_STD.

The value of the parameter AUDIT_TRAIL_LOCATION_TYPE is the name AUDIT_TBS of the tablespace to be migrated to, and the stored procedure to be executed is shown in the following figure

It was found that it took nearly an hour for the migration to complete.

At this point, the AUDIT_TBS tablespace already has data, the SYSTEM tablespace has been released, and the pressure is reduced. See the figure below

Third, although the above reduces the pressure on the SYSTEM tablespace, if the audit data is not regularly cleared and archived, the capacity of the new tablespace will be insufficient, so it is necessary to expand the capacity from time to time. Obviously, this is not a perfect solution either. Next, set the maintenance policy for the audit, 6 / 12

(1) the timestamp of querying the data of the AUD$ table began to be generated. It is now February 8, 2017, indicating that the audit data has been retained for nearly 8 months.

(2) use the stored procedure SET_AUDIT_TRAIL_PROPERTY in the package DBMS_AUDIT_MGMT that comes with Oracle to set the audit maintenance property, that is, how many pieces of data are cleared and submitted. The stored procedure accepts three parameters, in the order AUDIT_TRAIL_TYPE, AUDIT_TRAIL_PROPERTY, and AUDIT_TRAIL_PROPERTY_VALUE.

The value of parameter AUDIT_TRAIL_TYPE is described in the second method. The values of parameters AUDIT_TRAIL_PROPERTY and AUDIT_TRAIL_PROPERTY_VALUE are described in the official document below.

Here, we use parameter AUDIT_TRAIL_TYPE with a value of AUDIT_TRAIL_AUD_STD, parameter AUDIT_TRAIL_PROPERTY with a value of DB_DELETE_BATCH_SIZE, and parameter AUDIT_TRAIL_PROPERTY_VALUE with a value of 10000. Then execute the following stored procedure

(3) set the number of days of audit data retention with the stored procedure INIT_CLEANUP in the package DBMS_AUDIT_MGMT that comes with Oracle. The stored procedure accepts two parameters, in the order of AUDIT_TRAIL_TYPE and DEFAULT_CLEANUP_INTERVAL. See the official documentation, where the parameter DEFAULT_CLEANUP_INTERVAL ranges from 1 to 999 (in hours).

Use the stored procedure SET_LAST_ARCHIVE_TIMESTAMP in the package DBMS_AUDIT_MGMT that comes with Oracle to set the timestamp of the last archived audit record, which accepts two parameters in the order of AUDIT_TRAIL_TYPE, LAST_ARCHIVE_TIME, and RAC_INSTANCE_NUMBER. See the description of the official document. The parameter RAC_INSTANCE_NUMBER is the default value NULL, which can be left unwritten.

Here, we set the number of days to retain audit data as 30 days, that is, 720 hours, and the timestamp of the last archived audit record is 30 days ago. Then execute the following stored procedure

(4) use the stored procedure CREATE_PURGE_JOB in the package DBMS_AUDIT_MGMT that comes with Oracle to set the JOB of how often audit data is cleared. The stored procedure accepts four parameters, in the order of AUDIT_TRAIL_TYPE, AUDIT_TRAIL_PURGE_INTERVAL, AUDIT_TRAIL_PURGE_NAME and USE_LAST_ARCH_TIMESTAMP. See the description of the official document

Here, we clear the audit data every 7 days, 168hours, so set the value of the parameter AUDIT_TRAIL_PURGE_INTERVAL to 168and the value of the parameter USE_LAST_ARCH_TIMESTAMP to TRUE (also the default). Then execute the following stored procedure

At the same time, a JOB named "PURGE_AUD_STD" has been seen running under the monitoring interface of EMCC 12C, and the corresponding SQL is also running. See the picture below

Since no audit clean-up has been carried out since the last time, nearly 8 months of data has been retained, so it will be a bit time-consuming to retain only 30 days of data at a time. However, after performing this JOB, it will be easy to clean up the historical data later.

All three methods of maintaining audit are introduced above. What we demonstrate is that audit data can be saved in DB, which can also be stored in OS and XML, but I do not recommend this. According to official documents, keeping audit data in DB for maintenance by Oracle will reduce the IO operations that communicate with OS.

Summary:

1. Directly close the audit, change the initialization parameter AUDIT_TRAIL to NONE, and restart the database to take effect

two。 Migrate AUD$ tables from SYSTEM table spaces to other table spaces to reduce the pressure on SYSTEM table spaces

3. On the basis of the second method, the number of days to retain audit data is set and the expired audit data is cleared regularly.

In addition, in the process of writing this article, I refer to the following URL, which specifically states

Official document https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_audit_mgmt.htm#BABDAHBG

Oraclewiki http://www.oracle-wiki.net/startdocshowtomanageaudit

Laurent Leturgez https://laurent-leturgez.com/2011/06/09/managing-database-audit-trail-in-oracle-11gr2/

Suresh Karthikeyan https://www.pythian.com/blog/oracle-database-script-to-purge-aud-table-using-dbms_audit_mgmt-package/

If you think this article is helpful to you, you are welcome to follow the DBA study notes on Wechat official account: guestart. Your support is my greatest encouragement!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.