ISACA Journal
Volume 1, 2,016 


A Framework for Automated Compliance Monitoring in Oracle/SAP Environment 

Balraj Thuppalay, CISM, CISSP 

It is not uncommon for IT departments to be unaware of security best practices specific to SAP and to lack the business knowledge necessary to perform a segregation-of-duties (SoD) analysis. As a result, potential SAP control issues go unnoticed. Both internal auditors and compliance analysts can play a vital role in helping companies achieve compliance with the US Sarbanes-Oxley Act of 2002, with focused reviews of IT general controls (ITGCs) around SAP.

The Sarbanes-Oxley compliance analyst assists senior management in defining the control objectives and monitoring compliance efforts. The analyst reviews test findings within the internal/external audit team, facilitates the remediation of control gaps and escalates possible critical issues to the senior management of the company.

However, maintaining ongoing compliance of the controls and processes is a daunting task. Often, compliance analysts spend considerable time and effort collating information from various applications and evaluating it for compliance. This is particularly true of organizations that have a large number of systems and applications under their auspices and get audited each year for regulatory compliance, such as for compliance with Sarbanes-Oxley.

Depending on enterprise policy, the team evaluating compliance may not have access to the organization’s relevant applications and is, therefore, dependent on the operational teams to provide the relevant data. Furthermore, to ensure the continued compliance of controls for the whole year, the compliance team has to manually test the same set of controls multiple times in a year.

Apart from the monotony and rigor of testing these controls multiple times, there is still a possibility that some of the controls may get missed in the evaluation process by the team and instead come under the scrutiny of the auditors. Such situations often arise, especially when auditors commence audits after a database or an enterprise resource planning (ERP) upgrade, and the compliance team has not had adequate time to evaluate the effect of the upgrade on control parameters. The higher the number of compliance controls, the more time spent in collecting relevant evidence, collating it, documenting and analyzing it for compliance. Under these circumstances, automation of monitoring can realize potential savings in time and effort apart from providing timely information.

There are many third-party vendors that offer automated solutions for continuous compliance assessments, but often, these solutions can be expensive in terms of licensing and maintenance and perhaps may not be the best fit for all of the compliance needs of the organization. At times, these solutions can be overkill as well. It is, therefore, essential for an organization to have a good understanding of the applications, controls and processes that come under the purview of audit. The organization may also consider looking into the relevance of the frequency of compliance reviews and the time and effort spent by the operational/compliance teams on collating evidence and evaluating each control. This essentially gives the organization some food for thought on whether an automation of compliance assessment can be of any benefit and, if so, how best to go about doing it.

The automation framework discussed in this article is based on Oracle and provides a solution to generate HTML reports automatically with data collected from various ERP instances and Oracle databases. For the purpose of this discussion, the example enterprise is a large oil and gas multinational company with multiple SAP instances catering to its various business needs and spanning several geographic locations. In this enterprise, these SAP instances are all of the same SAP version of ERP Central Component 6.0 (ECC 6.0) and running on the same version of the database. This hypothetical automation framework is illustrated in the following four stages.

Stage 1: Identifying the Team Composition

As a first step in the project, it is important to identify the key people who will comprise the automation project team. It will generally consist of a mix of compliance analysts, SAP security analysts, SAP BASIS consultants, Oracle architects and, possibly, a representative from the internal audit group. This automation team will identify the initial set of controls that can be easily automated, typically those controls for which information can be fetched easily from the application/databases. Figure 1 represents this initial set, a mix of database and SAP IT general controls (ITGCs) that are suitable candidates for automation. The next step is for the automation team to walk through each of the controls to identify key tables in the Oracle database and the SAP instances that can provide relevant information for compliance evaluation (figure 1).

Assuming the organization has three different oil and gas SAP instances (OG1, OG2 and OG3, as depicted in figure 2), this framework proposes a separate Oracle database for compliance assessment. The compliance database (DB) in the proposed model will have separate schemas, one for each of the production instances of OG. Once the base tables have been identified for compliance evaluation for each of the controls (figure 1), the team can proceed to set up an automated batch job to replicate the tables in the corresponding schemas of the compliance DB using the Oracle database job scheduler. The scheduler can be used to schedule a job to run on a particular day of the week at a specific time on a repeating basis. By having a separate database for compliance, it is possible to run queries on this instance independent of the production environment, thereby not impacting the operational workload of the production instances.

The benefit of the automation is to enable compliance monitoring to be undertaken as frequently as possible rather than three to four times a year, thereby providing access to current data. The automated batch job can be scheduled to run as often as every two weeks or at least once every month. In this way, the compliance DB and its schemas will be populated with fresh data from production instances and at more frequent intervals.

Stage 2: Designing and Testing SQL Queries for Each Compliance Control

Once the schemas in the compliance DB are populated with the tables, it is time to design Structured Query Language (SQL) queries for each of the controls that are to be evaluated using the tables in the OG1 schema. This is the most important stage of the project, and it will require the project team to spend considerable time and effort in understanding the data, knowing the relationship between the tables, writing the queries, testing the output and deciding on the final format of the output for each of the controls. Oracle SQL has some useful features that can be used to spool the output of an SQL query into an HTML page with tabulated results; this automation framework makes use of those features. What is more interesting is that the SQL script can incorporate HTML tags to customize the color and design of the final output. It is left to the requirements, the imagination and the creativity of the project team members as to how they want to render the data in the final output for each of the controls. For instance, the queries can be designed to display results showing only business users in the final output. HTML tags can be applied in the queries to indicate users of dialogue and service type in red for an enhanced visual appeal.

Figure 3 presents an HTML output pertaining to an access control that shows a listing of open default database accounts in the production database. Some of the open accounts are clearly depicted in red font in the figure, as these are default accounts that are noncompliant and should remain closed at all times. Likewise, figure 4 shows a listing of users in an SAP instance who have the high SAP_ALL and SAP_NEW profiles. Any account with SAP_ALL/SAP_NEW privilege and an SAP Dialog or SAP Service type highlighted in red requires attention from the security team for appropriateness. Figure 5 is the corresponding structured query language (SQL) code that can be used to generate the output in figure 4. It is important for the project team to rigorously compare the output with the actual results that can be obtained when one logs into SAP and runs a report manually (using SAP’s User Information System [SUIM]). This is to ensure that the results generated from automation do not vary from those provided by SUIM. The SUIM is an internal (built-in) reporting feature of SAP wherein the SAP security analyst has to manually feed certain report parameters and extract results. SUIM is not an automated tool.

Once the queries for each of the controls are written and tested for accuracy individually, the queries for all 10 controls (figure 1) can be combined into a single SQL script file (OG_COMPLIANCE_ QUERY.SQL). The resulting master SQL script file, when run, creates an HTML output file that provides a tabulated display of the results for each of the controls in the table. The script can be programmed to generate the HTML file to be automatically named with instance name and the date on which it was run (figure 2).

Stage 3: Testing the Query for Each Instance and Automating Them

After the SQL script in stage 2 has been tested thoroughly for accuracy on the OG1 schema, the same script can be tested to run against each of the remaining schemas, OG2 and OG3, as the base tables are the same in each schema. The HTML output results generated for each such schema of the compliance DB are then compared diligently to the actual SUIM results generated from respective production instances to ensure accuracy, and any discrepancies found are investigated. Each such HTML report now gives a complete result-set of all the controls for the production instance (figure 2). The master SQL script can then be designed to run in a sequential manner against the schemas of the compliance DB, one after the other. Using the Oracle database job scheduler, a batch job can be designed in the compliance DB to run this master SQL script (OG_COMPLIANCE_QUERY. SQL) against each of the three schemas every time the schemas are populated with fresh instances of table extracts from the respective production instances.

Stage 4: Enterprise Compliance Summary Reports

Thus far, this article has covered ways to extract results for each control on each of the instances by running the scripts against the corresponding schemas in the compliance DB and displaying them in a single HTML file. If the organization has several SAP/Oracle instances, then it is recommended to have a single report: an enterprise compliance summary report that displays values on key control parameters for each control against each of the production instances.


The benefits of automation are obvious savings realized in the time and effort required for compliance evaluation, especially for companies that have to manage a large number of SAP and Oracle instances. Although the framework in the model depicts just three instances, OG1, OG2 and OG3, in reality, it can be scaled up to connect and monitor any number of instances—a more realistic scenario for large organizations. There is no dependency on operational teams to provide the relevant information for compliance evaluation periodically. The framework does not provide continuous assessment during the year, but assists with periodic monitoring as warranted, perhaps biweekly or monthly. With the information available automatically in a timely manner, the compliance team has ample time to inquire into new findings in the report and discuss them with the operational teams for remediation. This helps keep the organization in a state of readiness for any compliance and statutory audits and helps maintain a strong control environment. The architecture was developed without the use of expensive third-party software other than the use of a separate Oracle database for compliance assessments. Most large organizations have enough Oracle and SAP expertise in-house to evaluate, design and build such a framework. After the framework has been developed and deployed, the compliance team should take proper care to ensure the security and integrity of the scripts and the associated batch jobs. Both the scripts and the batch jobs need to be checked occasionally, especially after major SAP or Oracle database releases, to ensure that nothing is compromised.

Author’s Note

All views and opinions expressed in this article are those of the author and do not necessarily represent the views of his employer. The author does not necessarily endorse or recommend the use of any particular software or vendor through this discussion, but merely proposes a framework for compliance automation using simple tools.

Balraj Thuppalay, CISM, CISSP, is a managing consultant with IBM Security Services, IBM India Private Ltd. He has more than 10 years of experience performing a wide range of audits and internal reviews such as Sarbanes-Oxley, Statements on Standards for Attestation Engagements audits, database audits and ISO 27001 for various clients across retail, banking, technology, and oil and gas sectors. Prior to joining IBM, he worked for public accounting firms Deloitte & Touche and Crowe Horwath International as an enterprise risk consultant. Any comments or suggestions on the article are welcome at


Add Comments

Recent Comments

Opinions expressed in the ISACA Journal represent the views of the authors and advertisers. They may differ from policies and official statements of ISACA and from opinions endorsed by authors’ employers or the editors of the Journal. The ISACA Journal does not attest to the originality of authors’ content.