Saving You 15 Minutes: Oracle, ASM and SAP

If you are looking to drag your Oracle SAP implementations into the 21st century then you might want to take a look at this document.

http://www.oracle.com/us/solutions/sap/asm-configguidelines-304656.pdf

Historically running SAP on Oracle had some interesting pre-requisites and requirements, most specifically about having hard coded database SIDs in file system paths and specific OS users per database / SAP installation.  If you are looking to deploy SAP on ASM which is supported from 11.2.0.2 onwards then a lot of the hard coded elements of your Oracle / SAP implementation disappear, which is good.  However one of the pre-requisites is that the OS user which runs the GRID and RDBMS installation is called oracle.  What isn’t clear in this document is that it is an absolute necessity that the OS user is called oracle; which for anyone coming from a security background would argue isn’t exactly best practice.  Don’t try and use anything else as the SAP installation won’t accept it.  This unfortunately creates a situation in which you have to choose between operational standardisation and security best practices.  Not a great choice to make!

Tweet Me @pbedba

 

Saving you 15 minutes: Compliance Rules 12.1.0.3 to 12.1.0.4 Part 2:- Agent Rules

Having covered Manual rules in the last blog post now it is time to look at Agent Rules.  Agent Rules have been designed to take away some of the complexity in defining custom compliance checks in 12c OEM.

Developing compliance checks in 12.1.0.3 takes a standard approach:

  • select the data based that you want to check – Configuration Extension
  • select the selected data and check if it is a violation  – Compliance Rule

Here is a 12.1.0.3 example:

We are going to check that the SYSTEM account is locked.

First the configuration extension:

select account_status from dba_users where username in ('SYSTEM')

This data then gets stored in the table within 12c OEM.

SELECT
        s2.target_guid      ,
        s3.VALUE            ,
       s3.attr
FROM
        MGMT$CCS_DATA s3                 ,
        MGMT$ECM_CURRENT_SNAPSHOTS s2gen1,
        MGMT$TARGET s2
WHERE
        (
                s2gen1.TARGET_GUID     = s2.TARGET_GUID
            AND s2gen1.ECM_SNAPSHOT_ID = s3.ECM_SNAPSHOT_ID
            AND s2.TARGET_TYPE         = 'oracle_database'
            AND s2gen1.SNAPSHOT_TYPE   = 'ccs_c_FF69ABA6C57800C4E0431EDFF569E26D'  – KEY IDENTIFIER
            AND s3.data_source_name    = 't7'                                      – ALIAS IN CONFIG EXTENSION
       )

Each column that is generated by a configuration extensions is stored as two columns by 12c. It stores the attribute and the value.  The attribute is the column name of your extensions and the value is the value of that attribute.

TARGET GUID                         VALUE       ATTR
A0885F27EF10114EFBD33B3ACEFE7DB0    OPEN        ACCOUNT_STATUS

So to define the violation in 12c you know that the violation is when VALUE=OPEN.  In 12c OEM it will look like this.

AGENTRULES

Here is a 12.1.0.4 example:

We are going to check that the SYSTEM account is locked.  In this example we are going to use an Agent Side Rule and here you can see the reduced work in creating a custom compliance rule.

First of all we create a configuration extension to return the specific violation we are seeking, a requirement of agent sides rules is that they are single column.

AGENTRULES_1

Then we can create the agent side rule.

AGENTRULES_2

AGENTRULES_3

The big difference here is that you just select your configuration extension.  We can test this against the target.  There is a known bug which is that the test score shows 100% compliance unless there is more than one violation, however this isn’t the case when it is applied to the target.  The addition benefit of agent side rules is that the underlying configuration extension will be applied to the target when the compliance rule is applied, i.e. you don’t need to deploy configurations separately or via monitoring templates.

AGENTRULES_4

 

 

Saving you 15 minutes: Compliance Rules 12.1.0.3 to 12.1.0.4 Part 1:- Manual Rules

One of the very simple but very effective changes in 12c OEM 12.1.0.4 is the introduction of manual compliance rules.  Manual compliance rules effectively give you the ability to associate a rule with the target that requires some kind of manual check by an operator.  Some compliance checks are so difficult to script via SQL or OS commands that it makes it prohibitively expensive and complicated to put them into 12c OEM.  However those checks which are very difficult to automate can actually be checked by an individual very easily.  Couple of examples:

Remove unused oracle binaries

If you were looking to validate this item then it would involve a lot of grep, awk and other shell scripting dark arts to ascertain the state of a set of Oracle binaries.  However the evaluation of this by a skilled operator could be done in a couple of minutes.

Password management software must be used to managed privileged accounts

Whereas the previous check could be technically possible via some complex shell scripting this one couldn’t.  But an operator could log into the password management software to ensure all the relevant accounts are there and that the passwords can be validated.  Not an interesting job but something that could be executed in 5 minutes say.

The other nice feature of the manual rules is that they can be set as compliant for an pre-determined period of time.  What this means is that the operator could verify a manual rule and then set the compliance period for six months.  During that time that target would be compliant against that rule however after the six month period it will once again become a violation until it is manually checked.  This ensures that manual rules which could fall outside the normal automated checks continue to be evaluated on a regular basis.

Simple but very effective.

Saving you 15 minutes: Host OS Configuration Extensions and Compliance Checks Part 3

Following on from the last blog here is a quick way to capture the Oracle home of the database in a OS configuration extension.

echo "ORACLE_HOME = `cat /etc/oratab | grep product | cut -d ":" -f2 | sort -u`"

OSCEXT_1

So now we can get the Oracle Home value we need to do something with it.  This quickly becomes a shell scripting challenge.  So if I want to check the directory permissions of the bin directory for an Oracle Home I need to use the following command:

ls -ld `cat /etc/oratab | grep product | cut -d ":" -f2 | awk '{print $1"/bin"}' | sort -u`

If I do this in the OS Configuration Extension I get the following:

OSCEXT_4

What has occurred is that the java parser has just created the first field as an attribute and the rest as the value.  Not ideal, so if I use the same approach as before I can put in the following:

echo "BIN_DIRECTORY = ls -ld `cat /etc/oratab | grep product | cut -d ":" -f2 | awk '{print $1"/bin"}' | sort -u`"

OSCEXT_5

So now I have the attribute as BIN_DIRECTORY but the value is interpreted incorrectly.  This is because the ls -ld wasn’t interpreted as a command i.e. preceded by the `.  What I need to do is to get the parameter I want and then pass it to ls –ld, for this I use xargs and then a bit of awk as I am only interested in the permissions and not the whole string.

echo "BIN_DIRECTORY = `cat /etc/oratab | grep product | cut -d ":" -f2 | awk '{print $1"/bin"}' | sort -u | xargs ls -ld | awk '{print $1}'`"

Executing this gives me the correct attribute and value.

OSCEXT_6

The key is ensuring that your shell commands return a static ‘attribute’ as the first column and then a single value as a second column.  This will mean any compliance checks will look for the static attribute (as you know what that will always be) and then check the corresponding value.

Tweet Me @pbedba

 

 

 

Saving you 15 minutes: Host OS Configuration Extensions and Compliance Checks in 12c OEM Part 2

If I was ever confident that this blog will actually save you 15 minutes then it would be the following.

A simple OS configuration extension would be the following:

echo “EXTPROC = `ls -l $ORACLE_HOME/bin/extproc | awk ‘{print $1}’`”

This would in theory would show you the permissions for the extproc binary which you could then compare to a security standard, our security standard says this should be set to 000 by default.

However when you run this as a metric extension it says that the collection fails.  The output is below and one of the first things you should ignore is the \ in front of the $.  That isn’t the reason it is failing.

OSCEXT_2

The problem can be explained by running another metric extension.

echo “ORACLE_HOME = `echo $ORACLE_HOME`”

OSCEXT_3

The output then shows what the issue is.  The environment variables that the configuration extensions will automatically inherit is that of the agent, therefore if you want to do any OS configuration extensions that involve files in the database Oracle Home you need a way to extract and set that as part of the configuration extension.

Tweet Me @pbedba