Director of Product Management – Oracle
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.
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:
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.
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.
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.
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.
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’.
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.
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’.
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.
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.
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.
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.
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.
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;
/
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.
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_ccs = 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.
So to fix our problem we first need to check if this collection is enabled on the target associated to the compliance standard.
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.
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’;
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.
Refresh the UI every few seconds until the Configuration Extension successfully deploys.
We now have the Configuration Extension deployed to the target. Let’s double check by running the emctl query on the agent.
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.
Success! We see the Required Data Available is now ‘Yes’ for our target.
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.
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.
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.
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.
Last Friday, Oracle released new Enterprise Manager compliance standards based on DISA’s Security Technical Information Guide ( STIG ) for Oracle Database 11.2g Version 1 Release 2. These standards are specifically for use with Oracle Database 11g Release 2, both single instance and RAC.
This is actually the second compliance content update since February. The previous one included an update to the Oracle Database 11g STIG standard bringing support from Release 8 to 11.
Both updates were made available via Enterprise Manager’s Self-Update feature. To download them, go to Setup->Extensibility->Self Update. Select ‘Compliance Content’ which will take you to the ‘Compliance Content Updates’ page. Here you should see the compliance standards in either Available, Downloaded or Applied state. Simply select the row and click Download to retrieve the standard. When download has completed, click Apply to make it available in the compliance library.
After applying both compliance standards, you should see six STIG related standards in the Compliance Library.
If you are currently using one of the ‘Security Technical Implementation Guide (STIG Version 1.8)’ standards you should move to one of the updated versions. Which new version to use depends on the version of each database. For 11gR1 databases use the updated ‘STIG Version 8 Release 1.11’ standard. For 11gR2 databases, you should use the new ‘Oracle 11.2g Database STIG – Version 1, Release 2’ standards.
To migrate, simply associate the targets to new standard and unassociate them from the old version. You could even run both in parallel until you are comfortable with the new results.
If you were not previously using the STIG standards but planning to start, I suggest first reviewing my original blog on this topic which gives a quick overview of these standards and contains a link to a compliance overview screenwatch.
Documentation on these and other Oracle Database Compliance standards can be found in the Oracle Database Compliance Standards reference guide on OTN.
David Wolf 