I recently reviewed SAP note 740897 and discovered that the application-specific full use license SAP customers receive when they purchase the Oracle database through SAP includes the Database Lifecycle Management Pack. This means I can make use of, among other things, the compliance checking capabilities provided by Oracle Enterprise Manager 12c.
Many of the posts I put up here serve as “how to” documents, explaining how I do something so that others can decide how they would like to do something. This post is slightly different. I will be describing how I currently use the compliance rules, but in addition to simply providing a “how to”, this is more of a plea for anyone who finds this to tell me how this can be done more easily and efficiently. The compliance functionality in EM12c appears to be much more configurable than that provided by EM11g, but one key piece that existed in EM11g appears to be gone. That key piece is the ability to ignore/suppress a particular key value from a compliance check. I would love to have someone tell me that I’m just not finding that function in EM12c.
As I recall, in EM11g, when you had compliance checks enabled you could ignore a single key value. As an example, perhaps you had the rule to flag users with access to select from DBA_* views. That is great, except that my account has the DBA role, so my account appeared as a violation. But I had the ability to ignore any violations on that rule where the key value was my account name. This does not seem to be the case with EM12c. Hence this post, where I describe how I’m achieving similar functionality in a very different way, hoping someone else knows a better way to do it.
Getting Started
The first step to using the EM12c compliance functionality for your databases is to have a license for the Database Lifecycle Management Pack. If you don’t have one already, contact your Oracle sales representative. Note that if you purchased your licenses before Oracle 11g was released, you may have a license to some retired management packs such as the Configuration Management Pack, Change Management Pack, or the Provisioning and Patch Automation Pack. These three legacy packs combined seem to provide most/all of the functionality included in the Database Lifecycle Management Pack and according to the EM12c documentation grant you a license to use the functionality provided by the Database Lifecycle Management Pack. Don’t take my word for it, review the Oracle Enterprise Manager Licensing Information document, particularly sections 2.3, 2.6, 2.7 and 2.8, then consult with your sales contact if you have questions.
Once you have confirmed your entitlement to use this feature, enable the Database Lifecycle Management Pack in EM12c as follows:
- Login to EM12c as the repository owner (SYSMAN)
- Navigate to the Management Pack Access screen via the Setup menu, then the Management Packs submenu
- If not selected already, select the “Target Based” Pack Access radio button
- If not selected already, select “Database” from the search drop-down
- Click the Go button
- Check the box in the Database Lifecycle Management Pack column for each database where you have this pack licensed and then click the Apply button
Management Pack Access screen
This setup step enables the compliance functionality, but to make use of it you will need to first enable collection of some additional information about your databases, then “attach” your database targets to a “compliance standard”.
Collecting Data Needed For Compliance Monitoring
Presumably to reduce load on systems where people don’t use the compliance functionality, EM12c does not collect the information needed to make full use of the compliance standards out of the box. You need to enable this collection. To do so:
- Click on the Enterprise menu, then the Monitoring submenu, then Monitoring Templates
- Check the box next to “Display Oracle Certified templates”
- Click the Go button
- Select the radio button next to “Oracle Certified-Enable Database Security Configuration Metrics”
- Click the Apply button
- On the next page, click the Add button to select the database targets for which you will use the compliance functionality
- Click the OK button
- Repeat these steps for the “Oracle Certified-Enable Listener Security Configuration Metrics” and your listener targets if you intend to monitor listener compliance
Applying out-of-box templates to enable security configuration metrics
Compliance Frameworks vs Compliance Standards vs Compliance Rules
EM12c uses a three-tier approach to compliance monitoring. For a full understanding of how this works you should read the Oracle Enterprise Manager Cloud Control Oracle Database Compliance Standards documentation, but to summarize it briefly a compliance rule checks a particular compliance item (like permissions on a certain file, or a specific database role), while a compliance standard groups multiple compliance rules into a set to which you then attach the targets you want to have monitored. A compliance framework then groups multiple compliance standards into a superset for reporting/auditing purposes. This gives you a single view of your overall compliance when you have multiple compliance standards applying to different target types, as a compliance standard only applies to one target type — that is, you use a separate compliance standard for your listeners than for your databases, but you then include both standards in your compliance framework for a view of your entire environment. EM12c comes with a large number of pre-built compliance rules, standards and frameworks which you can use as-is if you wish, but read on to find out why I prefer to customize them.
Working With Compliance Standards
To get started with compliance standards, click the Enterprise menu, then the Compliance submenu, and then click on Library. This will take you to a screen with tabs to move between compliance frameworks, standards and rules. For your first foray into compliance checking, start with one of the simpler Oracle-provided templates, like the “Storage Best Practices for Oracle Database” applicable to Database Instance targets. To find it, click on the Compliance Standards tab, then the little triangle next to the word “Search” at the top of the screen. Type “Storage Best Practices” into the Compliance Standard field, and select Database Instance from the Applicable To drop down, then click the Search button. Once you see that standard on your screen, click on that row of the table (NOT the name of the standard), then click the “Associate Targets” button. This will bring up a screen where you can then click the ‘Add’ button to select one or more of your database instances to attach to the standard. After adding a target, click the OK button. One more pop up window will appear asking you to confirm that you are ready to deploy the association, go ahead and click Yes on this screen.
Searching for a compliance standard and associating targets
You now have at least one target associated to a compliance standard. So what now?
Viewing Compliance Results
Once you have a target associated to a compliance standard, the main Enterprise Summary page will show an overview of the compliance check results along with a list of your least compliant targets.
Compliance region on Enterprise Summary page
The Compliance Summary region also has a Compliance Frameworks tab which provides another way of viewing the same information — further down I will cover how to set up a framework.
Compliance Summary region, Compliance Framework tab on Enterprise Summary page
For another view, you can also use the Compliance Dashboard, through the Enterprise Menu, Compliance sub-menu, and then clicking on Dashboard.
Compliance Dashboard
Compliance violations are grouped into minor warnings, warnings, and critical violations, based on the configuration of each compliance rule contained in a standard. Depending on your needs, you can change the significance of a violation as appropriate for your environment. I will cover this later as well.
To get some more information about the specific violations Enterprise Manager has found, click on the name of your compliance standard from one of those screens and you will see some more details about what is contained in the compliance standard and the status of your targets. For further detail, click on the name of a compliance rule on the left-hand side. Pardon the blurred text in these images, I have already customized some rules and standards and included my employer name, which I highly recommend doing to distinguish your customizations from the out-of-the-box configuration.
View of compliance standard check details
Drill down into compliance rule details
This page shows that of the three database instances I have associated with this compliance standard, I have only one violation, and that violation is a minor warning associated with the “Non-System Data Segments in System Tablespaces” compliance rule. Because SAP requires that users create some particular segments in the SYSTEM tablespace, this is a good one to work through as an example to show how to customize compliance monitoring to fit your environment.
Customizing Compliance Monitoring
There are a few different ways to customize your compliance monitoring beyond the high-level decision of which specific targets you associate to each specific standard. One way is to create your own compliance standards, selecting and excluding the compliance rules that are not relevant in your environment — this way, for example, you can complete disable the check for “Non-System Data Segments in System Tablespaces” if you choose to (I wouldn’t, but you might want to). Another way is to customize the specific compliance rules contained in your compliance standards. I do both.
I highly recommend not attempting to edit any of the Oracle-provided compliance frameworks, standards, or rules. The “Create Like” button in the compliance library will be very helpful to you here.
The “Create Like…” button is your friend
First create your own compliance standard by selecting an existing one (I’ll continue to demonstrate this with the “Storage Best Practices for Oracle Database” standard) and clicking on the “Create Like…” button. EM will prompt you to provide a name for the new standard. For simplicity I prefer to use some indicator like my employer’s name followed by the name of the original standard. Click Continue once you have named your new standard and you will proceed to the compliance standard editing page.
Here you specify the rules to include or exclude from your compliance standard
From this page you can add or remove compliance rules from your newly-created compliance standard. To remove a rule, right-click on it in the region on the left and choose “Remove Rule Reference”, then click OK.
You can remove individual rules or groups of rules from this screen
The rules in the predefined standards are grouped into “rule folders”. Instead of removing a single rule, you can remove an entire rule folder if you wish by right-clicking and selecting “Remove Rule Folder” and then clicking OK. You can also create a new rule folder by right-clicking on the name of the compliance standard on the left and selecting “Create Rule Folder”, providing a name, then clicking OK.
Add or remove rule folders to group compliance rules
The compliance standard we’re working with has only a few rules. If you wish, you can add one of the many other rules that are contained in other compliance standards. Right-click on the compliance standard name or a rule folder, and select “Add Rules”. A screen will appear allowing you to select one or more rules to add to the standard. You can scroll through to select your rules or search by name or keyword. Once you click OK, the selected rule(s) will be added to your compliance standard.
Select as many rules to add to your standard as you wish
The compliance standard editing screen is also where you can change the importance of a compliance rule violation. To change the importance of the “Insufficient Redo Log Size” rule from “Normal” to “High”, click on that rule, then the drop-down box next to “Importance” and select a new value.
I guess “Low”, “Normal” and “High” correspond to “Minor Warning”, “Warning” and “Critical”
Finally, click the Save button to save your new compliance standard. At this point your new standard will not have any targets associated with it, so you should click on it and then on the “Associate Targets” button to do so. You may also wish to remove the association of those targets with the original standard you used to create this new standard. Once you finish in this screen, you can return to the Enterprise Summary or Compliance Dashboard, refresh the page, and you should see the results of the checks run by this new rule.
Changing A Compliance Rule
That is all useful, but what if you want to change the actual details behind a rule? I want to get eliminate the complaints about non-system data segments in the system tablespace so that I don’t see any more violations for the SAP-required segments I have in there, but I don’t want to remove the entire rule because I do want to be notified if other segments show up in there that I wasn’t aware of. The solution is create a new rule based on the rule you want to change, edit it (finally we get to write some SQL) and then remove the old rule from your compliance standard and replace it with the new rule.
Go back to the Compliance Dashboard and click the Compliance Standard Rules tab. Open up the search widget and search for “Non-System Data Segments” for target type “Database Instance”. Click on the offending rule and then the “Create Like” button.
The lock icon shows that you can’t edit the default rules but you can duplicate them
Provide a title for your new rule following whatever scheme you like. I will call it “DEMO Non-System Data Segments in System Tablespaces”. Click Continue and you will see the edit screen for Compliance Standard Rules.
You can change the text here if you wish, or add keywords
Click Next to go to step 2 where you can edit the rule SQL.
Finally, SQL!
This screen allows you to edit the rule SQL. If you aren’t familiar with the EM12c repository, this can be difficult. I recommend pulling up a SQL*Plus window connected to your repository database as SYSMAN, then copy/pasting the SQL text into the query window so that you can see the results that it returns. In my case I want to exclude violations for the “SAPUSER” table that SAP requires us to create in the SYSTEM tablespace, so I just add the text “and OBJECT_NAME not like ‘%SAPUSER%’” to the end of the SELECT statement.
Anything you can do in SQL, you can do here
Click Next once you have edited the SQL to your liking. This will bring you to a new screen where you specify the key values and violation conditions. This is one of the clunky parts of working with compliance rules, because the predefined violation condition is lost when you “Create Like” on a built in rule.
What now?
If you just proceed with finishing the rule from here, you’ll have a problem. Every single segment in the SYSTEM and SYSAUX tablespaces will be flagged as a violation. You need a where clause. But what should it be? What was it in the original rule? Here I typically open up a second browser window, navigate to the original rule in the Compliance Library, click the “Show Details” button and then scroll down to the bottom, which brings up the following screen:
At least there’s a way to get the configuration of the original rule
The lucky part here is that, even though the area is grayed out, you can select and copy the text from the original rule’s where clause, then paste that into your new rule’s where clause, as shown below. I’ve also checked the “Key” checkboxes for TABLESPACE_NAME, OBJECT_OWNER, and OBJECT_TYPE, because I suspect (but haven’t yet confirmed) that these key values determine how many individual violation events you will receive.
You can always re-edit this later if you don’t get it perfectly right the first time
Once you click Next on that screen you’ll be presented with step 4, where you can test your new compliance rule against a specific target. You can type in the target’s name or click the magnifying glass to select the target, as with the other target selection screens in EM12c. Click Run Test after you have selected and target and confirm that the results you see are the results you wanted.
Run tests against all your targets one at a time to see what will happen when your rule goes live
If you are satisfied with the test results, click Next. Otherwise click Back and try again with your SQL code and where clause. Once you click Next you will see step 5, which is just a summary page displaying your rule’s details. Click Finish when you are done.
All done, can I go home now?
Now that you clicked Finish, your new compliance standard rule is saved in the repository and available for use. You will need to attach it to a compliance standard, as described above, before it will do anything useful, and you probably want to detach the original rule that you used as the source to create this one.
Repeat these steps for every rule you wish to edit. This is the part I referred to at the beginning of the post where I hoped someone can suggest a better way. As I recall, in EM Grid Control 11g, an admin could simply select a specific compliance violation and choose to suppress it for that key value with a couple of clicks, as compared to this long process needed to duplicate and edit a rule. EM12c compliance rules are very customizable, just not quite as easy to work with — sort of like incident rules and notifications. You need to learn a new way of doing things, but it can do a lot.
Creating A Compliance Framework
Finally, you should create a custom compliance framework. This follows essentially the same process as creating a standard and attaching rules, but instead you create a framework and attach standards. Go to the Compliance Frameworks tab on the Compliance Library page and click “Create”. Give your framework a name and click Continue, and the Compliance Framework edit screen should look familiar.
Where have I seen this before?
Right-click on the compliance framework’s name in the left bar, and select “Add Standards”. A screen will pop up from which you can select the standards you created previously, just like when you add a rule. You can also add standard subgroups, which work much like rule folders. Click on your new standards and then OK.
Easy enough, right?
Click Save and you’ll be returned to the framework tab. At this point your new framework is in “Development” state, and you will NOT see it in the Enterprise Summary page. Click on the framework, then click “Edit”. Change the Compliance Framework State to Production and click Save.
Finally done!
You’re done! You now have a custom compliance framework, one or more custom compliance standards within that framework, and several rules in your standards, including some you have edited to meet your needs. Go back to the Enterprise Summary page, wait a minute or two, click the refresh button and then admire your work.
Time for a cold beer…
Conclusion
The compliance functions in EM12c are extremely customizable and capable. There are a some rough spots where I prefer EM11g’s functionality, and a couple spots where I need to open another browser window or SQL*Plus connection to get things set up the way I want, but that’s a small inconvenience compared to their power.
So now that you have these compliance evaluations staring you in the face every time you visit the Enterprise Summary page, get to work fixing those violations!
(EDITED: 20130903, typos fixed)
Like this:
Like Loading...
Pardy DBA 