EM12c Compliance ‘Required Data Available’ flag – Understanding and Troubleshooting

This is the first of two posts in the area of compliance management that has been previously published by a colleague of mine, Dave Wolf. Dave has moved to another part of Oracle now, and I’ve inherited some of his reponsibilities as product manager, including change management, compliance management and configuration management, so I wanted to copy his material to my blog so it’s not lost. Thanks Dave, for putting this material together originally!

Overview

Starting in release 12.1.0.3 of Enterprise Manager a new column was added to the top level Compliance Standard Results page named ‘Required Data Available’. This column is meant to convey whether the data required for proper compliance evaluation of all of the rules within the Compliance Standard for each associated target is available in the repository. If the value for a given target is ‘No’ then the compliance results for that target should not be considered valid. A value of ‘Yes’ indicates the required configuration data is being collected and results valid; at least from a data availability perspective.

One challenge facing customers is the lack of understanding of the column and how to correct the cause of the ‘No’ status. The goal of this guide is to explain how this column’s value is derived and provide some tools to correct the situation.

Background

To understand how to correct the ‘Required Data Available’ issue, let’s take a step back to understand why it was added.

In Enterprise Manager 12c, the compliance framework was redesigned to be event driven. The primary event for re-evaluation is a detected configuration change for a target. Changes are detected by the agent that typically runs configuration collection on a daily basis. By default each target collects configuration that most administrators would be interested in viewing, tracking for change or comparing across targets. However, there are many additional specialized collections that are disabled by default. Some of these are related to monitoring while others are configuration. They can be enabled manually via the ‘Metric and Collections Settings’ page of a single target or in bulk using monitoring templates.

Enterprise Manager provides many out of the box compliance standards for various target types. A vast majority of these standards are Repository type. Repository type means they are based on data stored in the EM repository and whose evaluation will be triggered by updates to that data. These include several Oracle Database Security standards including:

  • Basic Security Configuration For Oracle Database
  • High Security Configuration for Oracle Database
  • Basic Security Configuration For Oracle Cluster Database
  • High Security Configuration for Oracle Cluster Database
  • Basic Security Configuration For Oracle Cluster Database Instance
  • High Security Configuration for Oracle Cluster Database Instance
  • Basic Security Configuration For Oracle Listener
  • High Security Configuration For Oracle Listener

The configuration needed for proper evaluation of these standards is not collected by default. The collections must be enabled using one of the Oracle provided monitoring templates listed below.

  • Oracle Certified – Enable Database Security Configuration Metrics
  • Oracle Certified – Enable RAC Security Configuration Metrics
  • Oracle Certified-Enable Listener Security Configuration Metrics

These collections are somewhat unique in that the configuration data, once collected, is not viewable on the target’s Configuration->Last Collected page where the rest of the configuration can be seen. The reason is that this configuration is compliance specific and therefore only contains violation data. For example, one of the Oracle provide rules named ‘Well Known Accounts’, runs a query to find any unlocked default accounts in the database ( Scott, HR, etc ). Only unlocked accounts are sent into the OMS and stored in the repository. This was done to keep the size of the repository from growing uncontrollably if the user were to enable these collections across a large number of databases.

The need for ‘Required Data Available’

Repository rules are basically SQL queries in which the ‘where’ clause determines if a violation exists. If the user forgets to enable the collections for the security compliance standards, all of the queries return zero rows and therefore generate no violations. This situation results in a 100% compliance score that is likely erroneous. Without a warning the user cannot tell the difference between a valid 100% compliance score and one as a result of the missing data.
Therefore the ‘Required Data Available’ column was added to warn the user of this condition. A popup warning was also added at the point when the user manually associates a target in this state. However, a user associating targets via template collections would obviously not see the initial warning message hence something was needed in both places.

The Security compliance standards are not the only cause of the ‘No’ condition. Customization is one of the key features of the compliance framework which allows users to implement their own compliance standards based on whatever standard they follow. One method of customization is using Configuration Extensions to collect additional data which can then be audited using compliance rules. These configuration extensions must be deployed prior to association or the rules depending on that data will not evaluate properly and give erroneous results. Along the same lines, users can create custom compliance standards using Oracle provided rules. Should they leverage any of the security rules, they need to enable the collections regardless of the number of rules being included.

Finding the cause of the ‘No’

As detailed above the cause of the ‘No’ in the ‘Required Data Available’ column is because one or more tables used by member rules are not being populated. The question remains: “How does the compliance framework know the data is missing?” To explain this there is one more somewhat internal concept that must be understood. Throughout this explanation I have used the word “collection” when referring generically to obtaining configuration for a target. In fact, a collection is a real entity also sometimes referred to internally as a ‘snapshot’. Regardless, a collection is a group containing 1 or more items which is scheduled to be collected by the agent periodically. Collections can contain either metrics or configuration data that determines under what conditions it is sent to the repository (always vs change only ), in which EM tables the data is stored and how long it is retained.

Viewing enabled collections on the agent

One can see the enabled collections on an agent along with their next scheduled collection time by running ‘emctl status agent scheduler’. Below I have used grep to filter the collections just for the database ‘testdb7’

collections1
collections2

As we can see there can be a lot of collections just for a single target. The date and time at the beginning of the line indicates the next scheduled time the agent will run that collection.

For the database security compliance standards to work properly the collections named ‘oracle_security’ and ‘oracle_security_inst2’ must be running. Filtering the above results further easily shows if they are enabled. If they are not enabled, the command would not return zero rows.

collections3

Here we can clearly see these collections are enabled for this database. Therefore, we should see the required flag = ‘Yes’ for this target in the UI for the ‘Basic Security Configuration For Oracle Database’.

requiredno

Should either or both of these collections not have been found to be enabled for this target, we would simply need to apply the appropriate Oracle Certified monitoring template to enable them.

This method is useful when the Oracle provided standards are used directly and on a small number of targets. However, as mentioned earlier, many customers take advantage of the customizability of the compliance framework to tailor a standard to their exact requirements. This can include a standard containing references to Oracle provided rules as well as custom configuration collections. So another method must be used.

Finding Collections for a Compliance Standard

Before moving on, one detail about configuration extensions should be mentioned. In short, a configuration extension will become a schedule collection on an agent. All specified commands, files and SQL queries within that configuration extension will be executed during that single collection. The collection name of custom configuration extensions will start with ‘ccs_’. Below you can see a custom configuration extension as well as the oracle provided STIG configuration extension scheduled on the agent.

collections4

Now on to the repository based method. There are a few important repository tables we will be querying.

em_rule This is the table containing all of the details about each compliance rule in EM
mgmt_cs_rqs_hierarchy This table contains the compliance standard to rule hierarchy
mgmt$compliance_standard We use this table to get the compliance standard guid given the name
mgmt_ecm_snapshot_md_tables mgmt_ecm_snapshot_metadata em_ccs_metadata Contain mappings from snapshot/collection to populated table information

Using these tables the query below displays all rules for a given compliance standard, repository tables on which each member rule depends and the populating collection name for that table. In this example, the query is retrieving the information for the Oracle provided “Basic Security Configuration for Oracle database” standard.

BEGIN
   for rec in
      (SELECT DISTINCT column_value,
       r.rule_dname, r.target_type, r.rule_guid
       FROM em_rule r,
       TABLE(r.table_names),
       mgmt_cs_rqs_hierarchy rqs
       WHERE rqs.child_guid = r.rule_guid
       AND root_cs_guid =
           (SELECT cs_guid FROM mgmt$compliance_standard
            WHERE cs_name='Basic Security Configuration For Oracle Database') ) loop
   for rec1 in
      (SELECT DISTINCT m.snapshot_type, m.is_ccs, m.target_type
       FROM mgmt_ecm_snapshot_md_tables t,
            mgmt_ecm_snapshot_metadata m,
            em_ccs_metadata ccs_md
       WHERE m.target_type = rec.target_type
       AND t.name = rec.column_value
       AND t.metadata_id = m.metadata_id
       AND m.target_type = ccs_md.target_type(+)
       AND m.snapshot_type = ccs_md.snapshot_type(+)
       AND (m.is_ccs = 0
       OR ccs_md.testing_snapshot_type IS NULL)) loop
   dbms_output.put_line( rec.rule_dname || '| Table:' || rec.column_value ||
                         ' | Collection:' || rec1.snapshot_type );
         end loop;
      end loop;
   end;
/


Password Complexity Verification Function Usage| Table:ESM_DBPASSWORDSETTING | Collection:oracle_security
Execute Privileges on DBMS_SYS_SQL to PUBLIC| Table:ESM_EXECUTEPRIVILEGES | Collection:oracle_security
Oracle Net Client Trace Directory Permission| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Oracle Net Server Trace Directory Permission(Windows)| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Control File Permission| Table:ESM_DBCONTROLFILESPERM | Collection:oracle_security
Auditing of SYS Operations Enabled| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Access to DBA_ROLES View| Table:ESM_TABLEACCESS | Collection:oracle_security
Access to SYS.USER_HISTORY$ Table| Table:ESM_TABLEACCESS | Collection:oracle_security
Audit File Destination(Windows)| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Enable Database Auditing| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Password Grace Time| Table:ESM_DBPASSWORDSETTING | Collection:oracle_security
User Dump Destination(Windows)| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Default Passwords| Table:ESM_DEFAULTACCOUNTPASS | Collection:oracle_security
SSL Versions supported| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Access to DBA_SYS_PRIVS View| Table:ESM_TABLEACCESS | Collection:oracle_security
Oracle Home Datafile Permission(Windows)| Table:ESM_DBDATAFILESPERM | Collection:oracle_security
PROTOCOL ERROR FURTHER ACTION| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Using Externally Identified Accounts| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Initialization Parameter File Permission(Windows)| Table:ESM_DBINITPARAMFILESPE | Collection:oracle_security_inst2
Oracle Home Executable Files Owner| Table:ESM_OHBINFILESOWNER | Collection:oracle_security_inst2
Restricted Privilege to Execute UTL_SMTP| Table:ESM_EXECUTEPRIVILEGES | Collection:oracle_security
Access to SYS.SOURCE$ Table| Table:ESM_TABLEACCESS | Collection:oracle_security
Background Dump Destination(Windows)| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Check Network Data Integrity on Server| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Force Client SSL Authentication| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Core Dump Destination(Windows)| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Oracle Net Client Trace Directory Permission(Windows)| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
PROTOCOL ERROR TRACE ACTION| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Remote OS Authentication| Table:ESM_REMOTEROLESANDAUTH | Collection:oracle_security_inst2
Access to SYS.AUD$ Table| Table:ESM_TABLEACCESS | Collection:oracle_security
Oracle Home File Permission| Table:ESM_OHFILESPERMISSION | Collection:oracle_security_inst2
Password Life Time| Table:ESM_DBPASSWORDSETTING | Collection:oracle_security
Oracle Net Server Log Directory Permission(Windows)| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Oracle Net Server Log Directory Permission| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Utility File Directory Initialization Parameter Setting| Table:ESM_UTLFILEDIRSETTING | Collection:oracle_security_inst2
Use of Remote Listener Instances| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Access to DBA_ROLE_PRIVS View| Table:ESM_TABLEACCESS | Collection:oracle_security
Access to DBA_USERS View| Table:ESM_TABLEACCESS | Collection:oracle_security
Allowed Logon Version| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Well Known Accounts| Table:ESM_INSTALLANDDEMOACCC | Collection:oracle_security
Execute Privileges on DBMS_JOB to PUBLIC| Table:ESM_EXECUTEPRIVILEGES | Collection:oracle_security
Initialization Parameter File Permission| Table:ESM_DBINITPARAMFILESPE | Collection:oracle_security_inst2
Oracle Net Server Trace Directory Permission| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Restricted Privilege to Execute UTL_TCP| Table:ESM_EXECUTEPRIVILEGES | Collection:oracle_security
Access to DBA_TAB_PRIVS View| Table:ESM_TABLEACCESS | Collection:oracle_security
Oracle Home File Permission| Table:ESM_OHFILESPERMISSION | Collection:oracle_security_inst2
Password Locking Time| Table:ESM_DBPASSWORDSETTING | Collection:oracle_security
Control File Permission(Windows)| Table:ESM_DBCONTROLFILESPERM | Collection:oracle_security
Server Parameter File Permission(Windows)| Table:ESM_DBINITPARAMFILESPE | Collection:oracle_security_inst2
Oracle Net Client Log Directory Permission| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Oracle Home Datafile Permission| Table:ESM_DBDATAFILESPERM | Collection:oracle_security
Use of Appropriate Umask on UNIX Systems| Table:ESM_UMASKSETTING | Collection:oracle_security_inst2
Access to STATS$SQL_SUMMARY Table| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Encrypt Network Communication on Server| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Audit File Destination| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Core Dump Destination| Table:ESM_DBAUDITPERM | Collection:oracle_security_inst2
Use of Database Links with Cleartext Password| Table:ESM_DBLINKUSAGE | Collection:oracle_security
Public Trace Files| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Server Parameter File Permission| Table:ESM_DBINITPARAMFILESPE | Collection:oracle_security_inst2
Oracle Net Client Log Directory Permission(Windows)| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Data Dictionary Protected| Table:ESM_DBPARAMCOLLECTION | Collection:oracle_security_inst2
Restricted Privilege to Execute UTL_HTTP| Table:ESM_EXECUTEPRIVILEGES | Collection:oracle_security
Remote OS Role| Table:ESM_REMOTEROLESANDAUTH | Collection:oracle_security_inst2
Access to STATS$SQLTEXT Table| Table:ESM_TABLEACCESS | Collection:oracle_security
Access to STATS$SQL_SUMMARY Table| Table:ESM_TABLEACCESS | Collection:oracle_security
SSL Cipher Suites supported| Table:ESM_SQLNETPARAMS | Collection:oracle_security_inst2
Access to SYS.USER$ Table| Table:ESM_TABLEACCESS | Collection:oracle_security

A quick scan of the results shows that all of the rules depend on either the ‘oracle_security’ or ‘oracle_security_inst2’ as previously mentioned.

Finding Available Collections in Repository

To understand what collections are available in the EM repository for a given target, use the following query substituting your target name for ‘testdb7’:

SELECT distinct s.snapshot_type
FROM mgmt_ecm_gen_snapshot s
WHERE s.target_guid = (SELECT target_guid FROM mgmt$target WHERE target_name = 'testdb7')
AND s.is_current = 'Y'
AND s.target_type = 'oracle_database';

The above query results in the following, which clearly shows the oracle_security and oracle_security_inst2 collected.

snapshots1

Configuration Extension Example

Below is the results page of a custom compliance standard which contains two rules. The ‘Well Known Accounts’ rule is an out of box rule from the security standards and the CE_Test1 is a custom rule based on a configuration extension ( which I did not deploy to this target. ) As we can see, the column is ‘No’. Let’s run the query for this standard to see the results.

requiredno2

BEGIN
   for rec in
      (SELECT DISTINCT column_value, r.rule_dname, r.target_type, r.rule_guid
       FROM em_rule r,
       TABLE(r.table_names), mgmt_cs_rqs_hierarchy rqs
       WHERE rqs.child_guid = r.rule_guid
       AND root_cs_guid = (select cs_guid from mgmt$compliance_standard where cs_name=’CS_Test2′) ) loop
   for rec1 in
      (SELECT DISTINCT m.snapshot_type, m.is_ccs, m.target_type
       FROM mgmt_ecm_snapshot_md_tables t, mgmt_ecm_snapshot_metadata m, em_ccs_metadata ccs_md
       WHERE m.target_type = rec.target_type
       AND t.name = rec.column_value
       AND t.metadata_id = m.metadata_id
       AND m.target_type = ccs_md.target_type(+)
       AND m.snapshot_type = ccs_md.snapshot_type(+)
       AND (m.is_ccs = 0 OR ccs_md.testing_snapshot_type IS NULL)) loop
   dbms_output.put_line( rec.rule_dname || '| Table:' || rec.column_value || ' | Collection:' || rec1.snapshot_type );
      end loop;
   end loop;
end;
/

ccsdata1

As you can see, the results are not what we expected or as helpful as we would want. What do these results mean? Well they mean that all of the data collected by custom configuration collections end up in a single table called “EM_CCS_PARSED_DATA”, or at least its parsed version which is what the SQL query in the rule references. Our query returns results for all collections that populate a given table referenced by the rule hence we get all configuration collections for the target type oracle_database.

There is a way to narrow the results further by adding the em_rule_details table to the mix. This table contains the SQL query used in the rule. If you examine a rule based on a custom configuration extension closely ( assuming it is built using the SQL modeler ) you will see a reference to the collection name ( configuration extension internal name ) in the query.

ruledef1

We can add the INSTR function to our query to check if the collection name ( snapshot_type ) is contained in the rule query to eliminate all that are not. We need to break out the sub-queries to have one for non-configuration extension related rules and one for only configuration extension rules. The updated SQL looks like this:

BEGIN
   for rec in
      (SELECT DISTINCT column_value, r.rule_dname, r.target_type, r.rule_guid
       FROM em_rule r,
       TABLE(r.table_names),
       mgmt_cs_rqs_hierarchy rqs
       WHERE rqs.child_guid = r.rule_guid
       AND root_cs_guid = (SELECT cs_guid FROM mgmt$compliance_standard WHERE cs_name = 'CS_Test2') ) loop
   for rec1 in
      (SELECT DISTINCT m.snapshot_type, m.is_ccs, m.target_type
       FROM mgmt_ecm_snapshot_md_tables t, mgmt_ecm_snapshot_metadata m, em_ccs_metadata ccs_md
       WHERE m.target_type = rec.target_type
       AND t.name = rec.column_value
       AND t.metadata_id = m.metadata_id
       AND m.target_type = ccs_md.target_type(+)
       AND m.snapshot_type = ccs_md.snapshot_type(+)
       AND m.is_ccs = 0) loop
   dbms_output.put_line( rec.rule_dname || '| Table:' || rec.column_value || ' | Collection:' || rec1.snapshot_type );
      end loop;
   for rec2 in
      (SELECT DISTINCT m.snapshot_type, m.is_ccs, m.target_type
       FROM mgmt_ecm_snapshot_md_tables t,
            mgmt_ecm_snapshot_metadata m,
            em_ccs_metadata ccs_md,
            em_rule_details rule_details
       WHERE m.target_type = rec.target_type
       AND t.name = rec.column_value
       AND t.metadata_id = m.metadata_id
       AND m.target_type = ccs_md.target_type(+)
       AND m.snapshot_type = ccs_md.snapshot_type(+)
       AND m.is_cc = 1
       AND rec.rule_guid = rule_details.rule_guid
       AND INSTR( rule_details.source,m.snapshot_type ) != 0) loop
       dbms_output.put_line( rec.rule_dname || '| Table:' || rec.column_value || ' | Collection:' || rec2.snapshot_type );
      end loop;
   end loop;
end;
/

The following are the updated results which give us exactly what we are looking for.

collections5

So to fix our problem we first need to check if this collection is enabled on the target associated to the compliance standard.

emctl1

We can see it is not enabled for this target. To enable a configuration extension, we need to deploy it using the Configuration->Configuration Extension UI or using a monitoring template.

Finding the Configuration Extension Name

But what if we don’t know what the name of the Configuration Extension is given the cryptic internal name shown in the query results?
We can use the MGMT_ECM_SNAPSHOT_METADATA table to convert the internal name to the name seen in the UI.

SELECT ui_name FROM mgmt_ecm_snapshot_metadata
WHERE snapshot_type='ccs_c_E96B24C5058F23D3E04368AFF50A3BFB'
AND kind='P';

uiname1

Here we can see the “Dell Config Data” configuration extension is the name in the UI. We will use the Manage Deployments option, select our ‘testdb7’ and save.

ceui2

Refresh the UI every few seconds until the Configuration Extension successfully deploys.

ceui3

We now have the Configuration Extension deployed to the target. Let’s double check by running the emctl query on the agent.

emctl2

We see the collection is now running against our database target. Next, let’s head back to the compliance results page to see if we have corrected the issue.

requiredyes

Success! We see the Required Data Available is now ‘Yes’ for our target.

Summary

The ‘Required Data Available’ column is critical for ensuring accurate compliance results. In general, if best practices for automation are followed users should not encounter this issue. However, when this condition occurs, following a methodical approach as shown can easily uncover and rectify the issue.

Other possible causes

The above explanation and troubleshooting is based on EM version 12.1.0.4 with all patches and updates applied. There have been a few bugs and issues particularly in the area of the Database Security compliance standards in past releases which could cause similar issues even in 12.1.0.4 if upgrade procedures were not followed exactly.

Collection Name Change

In the DB plugin released with 12.1.0.3, a change was made to one of the DB security collections. This change was intended to fix a performance issue and resulted in many additional tables being created. It was also supposed to change collection name from oracle_security_inst to oracle_security_inst2. However a bug in the upgrade process failed to make the change. This meant that although the rules were updated to point to the new tables, the collection was not started on the agents and therefore evaluation was not happening.

BUG 17979658 – SECURITY STANDARDS FOR ORACLE DATABASE NOT EVALUATING CORRECTLY
EM12c: Post Installation Steps for Patch 17979658 (Doc ID 1625526.1)

To complete the process, the users must manually re-apply the oracle monitoring templates specified to enable the collection. A simple first step would be to just re-apply the template to a target to see if the issue gets resolved.

Copied Out of Box Rules

This is related to the previous possibility. As mentioned, the collection name as well as the populated tables changed in EM 12.1.0.3. During the upgrade process to 12.1.0.3, the existing security rules were automatically updated to reference the new tables. However, if the user had previously done a “Create Like…” of the existing rule for the purposes of customization then those copied rules would not get updated and would still be referring to the old tables. Since the collections are now populating new tables, the data will not get updated or even initially sent for new targets. Users must either edit the customized rules to refer to the updated table names or simply switch to using the Oracle provided rule.

Pete

After 22 years of working at Oracle in just about every role except Marketing and Support, I am now working as a Senior Managed Services Consultant with Red Stack Tech, specializing in Oracle Database technology, High Availability and Disaster Recovery solutions. I am also a member of the OakTable Network, and have presented at RMOUG Training Days, Hotsos Symposia, Oracle OpenWorld conferences, and other user group events. I have co-authored the Expert Oracle Enterprise Manager 12c and Practical Oracle Database Appliance books published by Apress, and am one of the authors of the Building Database Clouds in Oracle Database 12c book published by Addison Wesley.

Comments are closed