Configuring the Enterprise Manager Audit System - OEM 12c/13c

Audit Log Data:

Certain security operations should be audited to ensure compliance with the various act in different counties. In the EM environment, there is auditing for both the EM application and repository database.

EM Auditing

Enterprise Manager offers over 150 options for auditing that can be configured using EMCLI but by default, it audits a few basic and infrastructure operations and this auditing cannot be turned off. The audit data is stored in the repository and the volume of this data can impact the performance of the system so it is important to externalize or archive the data at regular intervals. An API called EM_AUDIT_EXTERNALIZATION is provided to externalize the data. The data is stored in XML format and the files are named NNNNN.xml where is a configurable value and NNNNN is a number. The audit externalization service is enabled/configured using the EMCLI update_audit_settings command and the externalization of the audit data is performed by a repository job called EM Audit Externalization Service. The status of this job can be seen in Cloud Control by clicking on Setup / Manage Cloud Control / Repository.

To enable and configure the externalization of the audit data, follow the commands below:

1. Connect to the database as sysdba and create an external directory

SQL> CONNECT / AS sysdba;

SQL> CREATE DIRECTORY EM_AUDIT AS '/u01/app/oracle/em_audit';

SQL> GRANT READ,WRITE ON DIRECTORY EM_AUDIT to SYSMAN;

e.g., Connect to OMS in console mode:

$ pwd

/u01/app/oracle/oem

$ mkdir em_audit

$ cd em_audit/

$ pwd

/u01/app/oracle/oem/em_audit

$ emtst

$ sqlplus / as sysdba

SQL> CREATE DIRECTORY EM_AUDIT AS '/u01/app/oracle/oem/em_audit';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY EM_AUDIT to SYSMAN;

Grant succeeded.

SQL>

2. Execute the command below to configure the externalization:

NOTE: be sure the select the specific operations that should be audited. Setting this to the value of “ALL” will mostly likely create more audit files than necessary.

Syntax:

emcli update_audit_settings

      -audit_switch="ENABLE/DISABLE"

      -operations_to_enable="name of the operations to enable, for all oprtations

       use ALL"

      -operations_to_disable="name of the operations to disable, for all

       oprtations use ALL"

      -externalization_switch="ENABLE/DISABLE"

      -directory_name="directory_name (DB Directory)"

      -file_prefix="file_prefix"

      -file_size="file_size (Bytes)"

      -data_retention_period="data_retention_period (Days)"

Example:

$ emcli update_audit_settings

-audit_switch="ENABLE"

-operations_to_enable="ALL"

-externalization_switch="ENABLE "

-directory="EM_AUDIT”

-file_prefix="EMAudit"

-data_retention_period="30"

3. The following command will show the settings:

The above commands will enable the externalization of the audit data using a name such as EMAudit00001.xml and will save the files in the directory /u01/app/oracle/em_audit. Once this is done, archiving of the mandatory audit records from the operating system can be done via Oracle Audit Vault or tape/disk backups. For more information, refer to the Enterprise Manager Cloud Control Security Guide.

Output: ( before enable audit) :

$ hostname

example01.company.com

$ export EMCLI_HOME=/u01/app/oracle/oem

$ export PATH=$PATH:$EMCLI_HOME/bin

$ emcli login -username=gmohapatra

Enter password :

Login successful

$ emcli show_audit_settings

User Activity Audit    : Enabled  (2 out of 171 operations)

Externalization Switch : Disabled

Directory              : Not configured

File Prefix            : em_audit

File Size              : 5000000 Bytes

Data Retention Period  : 365 Days

*Infrastructure Audit is always enabled

.................................................

$

Enabling Audit:

$ emcli show_audit_settings

User Activity Audit    : Enabled  (2 out of 171 operations)

Externalization Switch : Disabled

Directory              : Not configured

File Prefix            : em_audit

File Size              : 5000000 Bytes

Data Retention Period  : 365 Days

*Infrastructure Audit is always enabled

.................................................

$

$ emcli update_audit_settings 

-audit_switch="ENABLE" 

-operations_to_enable="ALL" 

-externalization_switch="ENABLE" 

-directory="EM_AUDIT" 

-file_prefix="EMAudit" 

-data_retention_period="30"

Successfully updated the audit settings.

$

$ emcli show_audit_settings

User Activity Audit    : Enabled  (For all Operations)

Externalization Switch : Enabled

Directory              : EM_AUDIT

File Prefix            : EMAudit

File Size              : 5000000 Bytes

Data Retention Period  : 30 Days

*Infrastructure Audit is always enabled

.................................................

$

Now Enable the Audit:

$ emcli enable_audit

Successfully enabled EM auditing.

$

Find Operations List:

emcli show_operations_list

e.g.,

$ emcli show_operations_list

Operation ID                         Operation Name                           Infrastructure Operation

ADD_AGENT_REGISTRATION_PASSWORD      Add Registration Password                 NO                     

ADD_CS_TARGET_ASSOC                  Add Standard-Target Association           NO                     

AGENT_REGISTRATION_PASSWORD_USAGE    Registration Password Usage               NO                     

AGENT_RESYNC                         Resync Agent                              NO                     

AG_AUD_CREATE                        Create Administration Groups              NO                     

AG_AUD_DELETE                        Delete Administration Groups              NO                     

AG_AUD_MODIFY                        Modify Administration Groups              NO                     

ALLOCATE_QUOTA                       Allocate quota                            NO                     

…..

Repository Database Auditing: ( As usual Database level audit):

Oracle always audits certain operations regardless of the database audit settings. This is referred to as Mandatory Auditing and the audit records are written to the operating system in the destination specified by the initialization parameter AUDIT_FILE_DEST.

Mandatory auditing includes these operations:

 » SYSDBA and SYSOPER logins

» CREATE/ALTER/DROP AUDIT POLICY

» AUDIT/NOAUDIT

» EXECUTE of the DBMS_FGA PL/SQL package

» EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package

» ALTER TABLE attempts on the AUDSYS audit trail table

» Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens. When the database opens, Oracle Database audits these users using the audit configurations in the system.

» All configuration changes that are made to Oracle Database Vault

The agent that resides on each OMS server logs into the repository every few minutes for self monitoring and generates an audit record for each login. Therefore, it is very important that the audit records are regularly archived and purged. The steps for doing this may vary according to a company’s security requirements but a sample setup is provided below.

Searching the Audit Data

You can search for audit data that has been generated over a specified period. You can also search for the following:

• Audit details of a specific user operation or all user operations
• Audit details of operations with a Success or Failure status or All operations.

From the Setup menu, select Security and then Audit Data. The Audit Data page is displayed. Specify the search criteria in the fields and click Go. The results are displayed in the Summary table.

Taking reports:
How can I take daily / weekly / monthly reports related to these reports? 

Navigate to above page in EM console to check the history of the EM user audits 

setup --> security --> audit data --> On the self of screen there is Search option 


Search with the Data Range as per your requirement "Most Recent" and "Time Interval" 

Frequently asked question:

Question: When .xml files grow in "em_audit" ( created folder for directory), can we delete older .xml files rather than reducing retention in case of file system full situations?  Or any thing to do? 

Ans: It is recommended to not to delete the files directly from the file system. 
To purge the audit data from repository database and then you can remove the files. 
How to purge audit data from the repository database?
Audit logs table will be purged if audit externalization job is executed and externalization switch is enabled.

EM Audit Externalization Service job is responsible for audit data purging. This DBMS Job is scheduled to run daily to export the audit data from the table SYSMAN.MGMT$AUDIT_LOG to xml files. Xml files with the format omsmyomshost_YYYY-MM-DD-xxxxx.xml will be created in the audit directory (example: EM_DIR) specified using emcli update_audit_settings command. The file size will not exceed 10000 bytes.
Setup --> Manage Cloud Control --> Repository -->

 Purge the records Manually:

This can be done manually or via a purge job that performs the purge at a specified time interval. The recommendation is to setup a job that will purge the records at a specified time interval and is the approach in the example shown below. Note that purging a large audit trail can take time to complete so it is wise to schedule the job so that it runs during a time when the database is not too busy. For further details on the process and an explanation for each parameter used in the example, refer to the Database Security Guide.

1. Initialize the audit trail cleanup operation.

SQL> begin

dbms_audit_mgmt.init_cleanup

( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

DEFAULT_CLEANUP_INTERVAL => 12);

end;

/

2. Setup an archive timestamp for the audit records. The RAC_INSTANCE_NUMBER refers to the instance number when using a RAC database. This must be set for each instance since the mandatory audit records are stored on the operating system and therefore for each instance. If this is a standalone database, use the number 0.

-- In Rac

SQL> begin

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP

(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

LAST_ARCHIVE_TIME => TO_DATE('2018-04-15 09:00:00','YYYY-MM-DD HH:MI:SS'), RAC_INSTANCE_NUMBER => 1);

END;

/

-- In Stand-alone

SQL> begin

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP

(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

LAST_ARCHIVE_TIME => TO_DATE('2018-04-15 09:00:00','YYYY-MM-DD HH:MI:SS'), RAC_INSTANCE_NUMBER => 0);

END;

/

3. Create and schedule the purge job

SQL> BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB

( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

AUDIT_TRAIL_PURGE_INTERVAL => 12,

AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_Cleanup',

USE_LAST_ARCH_TIMESTAMP => TRUE);

END;

/

Reference : 
How To Manage Cloud Control Audit Data with emcli show_audit_settings and emcli update_audit_settings ( Doc ID 1499609.1 ) 
Section : How to purge audit data from the repository database?