The OEM 12c Cloud Control Compliance Management framework provides the ability to evaluate the compliance of targets as compared to industry-wide and business defined best practices and standards related to configuration, storage and security.
It enables us to automatically determine from a Risk and Security Management viewpoint as well as IT Auditing if any of our enterprise targets are exposed to any security vulnerabilities as well as if any any industry standards or regulations (Payment Card Industry PCI for example) are being violated.
The Compliance Library contains the Compliance Frameworks which are comprised of Compliance Standards which in turn a collection of one or more Compliance Standard Rules.
Oracle provides a number of such frameworks, standards and rules out of the box with OEM 12c, but we can also create a user-defined standard or rule to satisfy the requirements of our organization as we will see in this example described below.
OEM 12c comes bundled with a number of in-built Compliance Standards which are each made up of a number of rules which evaluates if a target is meeting or violating these compliance standards which are based on industry and business accepted best practices related to different IT aspects like configuration, security, and storage.
For example there are a number of out-of-the-box standards related to storage as shown below.
Each of those standards is comprised of a number of rules.
Click on the Standard – Storage Best Practices for Oracle Database.
We see that this standard is made up of rules like no user should have default tablespace or temporary tablespace pointing to a SYSTEM tablespace or all tablespaces should be configured with ASSM or there should be at least 3 redo log groups and so on.
If any of the managed target databases to which this Compliance Standard has been applied violates any of these rules, then we can see that showing up and displayed on the Compliance Dashboard.
We now want to add another standard rule related to storage which ensures that all the datafiles of tablespaces belonging to production databases are configured with AUTOEXTEND turned on. This rule is not part of the out-of-the-box Storage Best Practices for Oracle Database standard rule.
On the Compliance Standard Rules tab, click on the Create button.
The rule type selected in Repository Rule which indicates that the rule is based on evaluating data collected and stored in the OMS repository.
We will create a rule called AUTOEXTEND Not Turned On. At this point in time the rule is still being defined and tested so we set the lifecycle state of the rule to development.After we promote a rule to production, we cannot change it back to development.
Provide a description and rationale why the rule is being implemented and also we can provide a reference link to the official Oracle documentation which provided more information on the AUTOEXTEND functionality and its usage.
We need to provide a SQL query that will execute against the Cloud Control Management Repository along with a message which will be displayed either when the rule is complied with or violated.
From a SQL*PLUS session connected as SYSMAN, we create a custom view and grant appropriate privileges on the view to the different EM users as shown below.
SQL> create view MGMT$CS_DB_DATAFILES 2 as 3 select a.TABLESPACE_NAME, a.FILE_NAME,a.AUTOEXTENSIBLE,b.target_guid 4 from MGMT_DB_DATAFILES_ECM a, MGMT$ECM_CURRENT_SNAPSHOTS b 5 where a.ecm_snapshot_id =b.ecm_snapshot_id; View created. SQL> desc MGMT$CS_DB_DATAFILES Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) FILE_NAME NOT NULL VARCHAR2(512) AUTOEXTENSIBLE VARCHAR2(3) TARGET_GUID RAW(16) SQL> grant select on MGMT$CS_DB_DATAFILES to SYSMAN_RO; Grant succeeded. SQL> grant select on MGMT$CS_DB_DATAFILES to MGMT_VIEW; Grant succeeded. SQL> grant select on MGMT$CS_DB_DATAFILES to MGMT_USER; Grant succeeded. SQL> create synonym mgmt_view.MGMT$CS_DB_DATAFILES for sysman.MGMT$CS_DB_DATAFILES; Synonym created. SQL> create synonym SYSMAN_RO.MGMT$CS_DB_DATAFILES for sysman.MGMT$CS_DB_DATAFILES; Synonym created.
We can see the columns which are returned by our SQL query and we can select the columns which will be displayed in alert messages when violations occur.
We also can set the condition we are checking against the returned query results to look for a violation.
In this example the rule checks if any rows are returned by the SQL query. If any rows are returned, it means then that the rule has been violated.
We can test our new rule by selecting a target and we see that for this particular database there are two datafiles which do not have autoextend turned on.
After the rule has been tested, we review the rule
The AUTOEXTEND Not Turned On rule has now been added to the Compliance Library.
The existing compliance standards cannot be edited to add additional rules so we will create a new custom compliance standard based on an existing standard and add a new custom rule to that custom standard.
Click on the Create Like buttton.
Provide a name for our custom standard and click Continue
This custom standard is applicable to all targets of the type Database Instance and the standard type is Repository. This means that the standard uses rules which are This means that the standard uses rules which are based on evaluating data collected and stored in the OMS repository.
Right-click the Standard we just added and select Add Rules
Search for the AUTOEXTEND rule we newly created
The rule is now added to the custom Compliance Standard
We now have to associate a target to which the compliance standard will apply. From the Compliance Standards tab, click on Associate Targets
Click on Enable and we should see the Evaluation Status for the chosen target display Enabled.
We see this message now displayed.
Now that the rule has been applied to a target (and we know that the particular target does have 2 tablespaces with datafiles not having autoextend enabled), we can view the Compliance Dashboard via the Enterprise – Compliance – Dashboard menu.
The Compliance Standard Custom Storage Best Practices for Oracle Databases displays 2 violations.