JOnline: Using Microsoft Office in Analyzing SAP SoD and Beyond 

Download Article

In essence, segregation of duties (SoD) calls for the separate performance of conflicting activities in systems, and/or the manual performance by different individuals, to prevent a single individual from conducting an unauthorized or wrongful act and then concealing it. SoD is an essential internal control in all businesses because it serves as a foundational part of an organization’s internal control infrastructure on which other internal controls are built. As a result, assessment of SoD has been an important task for both auditors and managers.

At the same time, properly assessing SoD has increasingly become a challenge in today’s businesses, due to increasing reliance on complicated information systems, especially enterprise resource planning (ERP) systems, and deficient knowledge of the new forms of risks posed by computerized business processes.

Same Risk-Based Approach

The overall approach to the assessment of SoD does not change over time, whether or not business systems are involved. The auditor must identify and prioritize risks throughout the business process across both business systems and manual processes. The auditor simply cannot afford to evaluate SoD within a single business system without assessing SoD risks across other related systems or manual activities. By focusing on a single system, the auditor may miss SoD conflicts that are not visible within the single system.

As a result of the comprehensive SoD risk assessment, the auditor should prioritize the potential SoD risks based on established risk-rating criteria, such as potential impacts to the business and likelihood of occurrence. Such risk-based prioritization allows the auditor to focus on one or more important business systems and related business activities in which SoD conflicts may most likely occur with the highest adverse impacts.

Different Means

Once the important systems, manual activities and potential SoD risks are identified and prioritized, the auditor should focus on the specific systems or manual activities that pose the most difficulty for the auditor to evaluate SoD conflicts. In most situations, a business system, especially an ERP system, would be the most difficult one for the auditor to evaluate SoD. The auditor is no longer able to rely on traditional techniques, such as interview, observation and examination of documents, to evaluate SoD in a business system because most activities are either automated or computerized. Instead, the auditor has to use different audit techniques, including:

  • Understanding the concept and design of a business system, especially an ERP system, to provide for user access rights at different levels (what a user can do) in different areas (what a user can see)
  • Identifying where user rights and authorization information are stored. These user access rights and authorizations are often stored in multiple, but related, database tables.
  • Obtaining the user rights and authorization information either directly, if the auditor has access to the information, or indirectly with the help of IT personnel
  • Analyzing and evaluating SoD status and conflicts using tools. It is very possible for an auditor with good skills in daily productivity tools, such as Microsoft Access and Excel, to use such tools in analyzing SoD even for an ERP system. The preconditions are that the auditor understands the user authorization concept and can obtain related data about user authorizations in the system. Commercial tools for managing and analyzing SoD in different ERP solutions are available, but they are usually quite expensive and require implementation. Thus, they may not be usable or available to the auditor.

The SAP R/3 ERP solutions (e.g., SAP R/3 release 4.6C or 4.7) from SAP have some of the most flexible but complicated user access and authorization design. SAP R/3 solutions are also used in many organizations globally. As a result, the auditor may find it useful to understand the SoD audit techniques for the SAP R/3 environment and use a similar method toward other business systems or ERP solutions.

SAP R/3 Authorization Concept

In a nutshell and at a conceptual level, the SAP R/3 ERP solutions use the so-called authorization objects, related fields and values of the authorization objects to manage user access rights both in terms of what a user can do (e.g., add, modify, delete, display) and what a user can see (e.g., companies, account groups, document types). To successfully perform an activity such as adding a vendor, a user needs the correct authorization objects, fields and values. Any missing or incorrect authorization object, field or value may make it impossible for the user to successfully perform the activity.

Figure 1 summarizes the typical authorization checks SAP R/3 performs when a user tries to run a standard transaction. Customized transactions or other activities may have additional authorization checks depending on configurations in SAP R/3 and/or programming design.

Figure 1

It is important to note that in SAP R/3 solutions both standard and customized transactions can require nonstandard authorization checks depending on the specific organization’s configurations. If an organization uses standard tools, such as Profile Generator, to create necessary authorizations (i.e., authorization objects, fields, values) for roles and profiles in SAP R/3, both the standard and any nonstandard authorizations required will be included in the generated profiles for assignment to users. However, some organizations may choose to manually “tweak” the standard authorizations generated by SAP R/3 for a more granular level of access. As a consequence of such authorization customization, a user who is originally assigned an “Add” or “Modify” transaction may now have read-only access despite that the name of the transaction suggests otherwise. For example, the user is no longer able to create a vendor even though the user appears to have the “create vendor” transaction assigned. From an SAP R/3 SoD analysis perspective, the auditor must be aware of the possibility of such authorization customization and include it in the SoD analysis, even though this will take more time and effort. Otherwise, certain false alarms may result from the analysis.

Finally, it is also critical to know that SAP R/3 solutions are able to turn off authorization checks globally or partially. It is not meaningful to analyze SoD in a system in which the global authorization check is deactivated. Additionally, there are other configurations in SAP R/3 solutions that may affect user access and results of SoD analysis. For example, an organization may choose to use posting tolerance for financial accounting users. If a user is assigned a zero tolerance, the user will not be able to post a financial accounting document in SAP R/3 solutions, even if the user has all other authorizations. This article does not cover all the possible situations in which the SAP R/3 SoD analysis may be affected. The auditor must evaluate whether such circumstances exist when performing the detailed SoD analysis.

SAP R/3 Authorization Tables and Relationships

As configuration-driven ERP solutions, SAP R/3 solutions store all the user authorization information (e.g., authorization objects, fields, values, profiles, roles, users) in several database tables that are related to each other.

There are many tables related to user access and authorizations in SAP R/3. The following are key tables that store users’ authorization or related data in SAP R/3:

  • UST04:  User masters—Containing all profiles to which each SAP R/3 user is assigned
  • UST10S:  User master: Single profiles—Containing all authorizations to which each (simple) profile is assigned
  • UST12:  User master: Authorizations—Containing all authorizations including authorization objects, fields and values. This is the ultimate table where actual SAP R/3 user access rights are stored.

There are also a few important reference tables to provide additional information necessary for the SoD analysis, for example:

  • UST10C—Containing composite profile to single profile mappings. Composite profiles are mainly from older releases of SAP R/3 solutions and are rarely used in more current SAP R/3 solutions, such as release 4.6C and 4.7. Examples of composite profiles include SAP_ALL and SAP_New, which are highly privileged default profiles in SAP R/3. The auditor should link table UST04 to this UST10C table to check if any normal user is assigned any composite profile, especially the default ones. If yes, the composite profiles must be converted to the corresponding simple profiles before further SoD analysis
  • USOBT_C—Containing default or required authorization objects, fields and values for transactions. This is an important table for SAP R/3 to determine what kind of authorizations a user must have to run a transaction.
  • USOBX_C—Containing information regarding what specific authorization objects are checked by SAP R/3 for different transactions and the types of entries in the previous USOBT_C table. This is where standard authorization checks can be deactivated through configuration, which basically overrides standard SAP R/3 authorizations. The auditor should check this table to make sure no major authorization deactivation exists in this table.
  • TOBJT—Description of authorization objects that help the auditor understand what the authorization is about (e.g., account groups, company codes, document types)
  • USR02—SAP R/3 user login data, containing username, account type, account status, expiration date, etc. The auditor needs to differentiate a normal user from a back-end system or communication account. The auditor may also want to focus on active users.
  • USER_ADDR—Containing general user information including first name, last name, department, address, etc.
  • TSTCT—Containing transaction names, which are more meaningful to the auditor than the transaction codes. This table contains names in multiple languages. The auditor should use his/her preferred language to display the transaction names in the analysis.
  • AGR_USERS—Containing roles assigned to users. The auditor can use this table and the AGR_PROF table to analyze authorizations assigned through roles to users, by linking them to the key authorization tables.
  • AGR_PROF—Containing profiles for each role

Figure 2 lists both the key tables and some reference tables as well as their relationships that are important to the SoD data generation and analysis described in subsequent sections. (Please note: Not all the tables listed will be covered in this article.)

Figure 2

Perform SOD Analysis Using Microsoft Office

After the auditor understands the SAP R/3 authorization concept and where authorization data are stored in various SAP R/3 tables, the auditor should be ready to evaluate SAP R/3 SoD by following the following steps (see figure 3).

Figure 3

Obtain Data
To perform the SAP R/3 SoD analysis, the auditor should obtain contents for the previously mentioned tables—ideally as part of preliminary requests prior to the audit. The auditor should request whole tables that are in an easily importable format such as tab-delimited text files or in table format, e.g., in Microsoft Access. To ensure consistency, reusability and the possibility of automation if necessary, the auditor should also make sure that the original SAP R/3 names for the tables and fields are not changed.

Clean Data
This is an optional step, dependent on the format of the SAP R/3 authorization data files obtained by the auditor. If the individual providing the data files downloads them directly from SAP R/3 using standard SAP R/3 functionalities, the files may have unnecessary lines before or after the field name row, which must be the first row for correct importation into Microsoft Access. These unnecessary rows should be removed to ensure that the auditor can import the files into Access successfully.

Import Data Into Access
The auditor should use standard features of Microsoft Access to import SAP R/3 authorization data files that the auditor has obtained and cleaned. The auditor should watch out for import errors reported by Access to make sure that all the required data have been correctly imported.

SAP R/3 SoD Analysis

Addressing the layered authorization approach that SAP R/3 adopts, as described previously, the auditor should also adopt a layered approach to analyzing SoD in SAP R/3. The two levels of SoD analysis (figure 4) differ in terms of time required, difficulty and reliability of the results.

Figure 4

Level 1:  SoD Analysis Based on Transaction Codes Assigned to Users
The level 1 SoD analysis evaluates only SAP R/3 transactions assigned to users without scrutinizing the specific authorizations underlying these transactions. This level of analysis is rather straightforward to perform and takes less time. It works quite well in organizations that do not customize or tweak standard authorization values in SAP R/3. In those that do, there is a risk of “false alarms” in that some users actually cannot run certain privileged transactions, contrary to what the analysis reveals.

Figure 5 depicts the design for the auditor to generate the data table necessary to perform the level 1 SoD analysis using Microsoft Access. The design is based on the content of figure 2. The key is to understand that in SAP R/3, the S_TCODE authorization object in the authorization table (UST12) indicates transaction code assignment.

Figure 5

Based on this design, the auditor should be able to generate a “transactions by user” table, similar to what is shown in figure 6.

Figure 6

The auditor should pay special attention to the use of wildcards in the assignment of transactions, for example:

  • “*” means all transactions
  • “S*” means all transactions starting with letter “S”
  • “F-01” to “FV70” refers to all transactions sorted alphabetically from “F-01” to “FV70,” inclusive

The auditor should make sure such wildcard assignments are included in the SoD analysis. To do so, the auditor should design a way in Microsoft Access to replace these wildcard transactions with the corresponding individual transactions that are necessary for subsequent level 2 analysis.

The auditor should then use the Pivot Table feature in Microsoft Access or Excel (if the data have been exported into Excel) to visually and clearly display all the critical transactions assigned to users in a “matrix” layout, similar to what is shown in figure 7.

Figure 7

The auditor should refer to a well-defined SoD conflict rules or standards document tailored to the client’s organization and risks in determining which critical SAP transactions should be included in the evaluation. Before looking into the details, the auditor should look for users with the highest number of critical transactions assigned (in addition to users with “wildcard” transactions, as described above), as well as for transactions with the most users assigned. This may allow the auditor to quickly focus on risky SoD areas. At the individual user level, the more numbers a user has corresponding to different transactions, the more SoD conflict risk the user’s access may pose.

Once again, the auditor should not forget to evaluate SoD conflicts from users’ access to other upstream or downstream systems or activities.

Level 2:  SoD Analysis Based on Precise User Authorizations
This level of SoD analysis in SAP R/3 is more reliable, but requires more time and effort, especially for auditors not familiar with the usage of multiple table-related queries in Microsoft Access.

The design to generate data for the level 2 SoD analysis is depicted in figure 8. The key concept is to find the required authorization values for SAP R/3 transactions and match them with actual authorizations to determine whether users actually have the authorizations to run specific transactions. The aforementioned USOBT_C table, together with the “transactions by user” table from the level 1 SoD analysis and other related tables, should be used to generate the required data for the level 2 SoD analysis.

Figure 8

Once the auditor generates the authorization data necessary to perform the level 2 SoD analysis, the actual analysis is identical to the level 1 SoD analysis, using the Pivot Table feature in Microsoft Access or Excel. The additional information available for the analysis is the actual authorization level (e.g., add, modify, delete). The auditor will be able to differentiate when a user is assigned a critical transaction with read-only access, which is less risky.

Within the level 2 SoD analysis, the auditor can evaluate users’ ability to access different companies’ records within SAP R/3. This is important, for example, in a shared service model in which several companies share one single SAP R/3 “instance” or system. It is certainly a risk for users from other companies to be able to perform activities in the concerned company. From an SoD perspective, what a user can see is also important, in addition to what a user can do. Evaluating users’ access capabilities to different company codes in SAP R/3 measures what users can see. In some large organizations, users may even be assigned to different vendor, customer or general ledger groups so that the users can only perform related transactions on their assigned vendor, customer or general ledger accounts. The level 2 analysis is able to reveal situations in which users can access only their assigned vendor, customer or general ledger account groups, similar to company code access, as described previously.

Figure 9 depicts a simplified SoD level 2 analysis based on an Access or Excel Pivot Table, including all the required and actual authorization levels by transaction code, for the auditor to determine whether a user can perform a transaction before SoD evaluation is performed.

Figure 9

Other Considerations

While the steps for level 1 and level 2 SoD analyses will not change in practice, the auditor should always watch out for the exceptional situations (e.g., the use of “wildcard” transactions and composite profiles) that may affect the SoD analysis. By using standard functionalities in tools such as Microsoft Access or Excel, the auditor should be able to include the exceptional situations in the data tables before the level 1 or level 2 SoD analysis.

It is also possible to automate both level 1 and level 2 SoD data generation steps, as described here, as long as the auditor makes sure the authorization tables and fields obtained use standard SAP R/3 names. This makes sense if the auditor needs to regularly evaluate SoD for identical business systems such as SAP R/3. Automating time-consuming steps increases efficiency and lowers manual mistakes.


Evaluating SoD in a business application system is an integral part of evaluating SoD in interrelated business processes. The auditor should follow the same risk-based approach in the evaluation process. The auditor must evaluate major SoD conflicts both within the business system and across other connected systems or manual activities, based on well-defined and risk-justified SoD conflict rules.

Given the complicated nature of many business systems, especially ERP solutions, the auditor should use computer tools starting with those that are inexpensive and readily available, for example, those in the Microsoft Office suite, to aid the efficient and effective SoD evaluation. Once auditors master the concept, design and steps in evaluating SoD in complicated systems such as SAP R/3, they should be able to apply the same approach to other ERP or business systems as long as they take the time to understand the similarities and differences in authorizations in different systems. As auditors become more comfortable and skillful with the methods and tools, they can further improve and automate the SoD evaluation process, especially if the same types of business systems are evaluated regularly. However, auditors must use their judgment and knowledge in determining SoD risks based on good understanding of the risks in the specific business.

Haiyan Chen, CISA, CIA
is head of IT internal audit in the group internal audit department of Sodexo. He has many years of experience in information systems design, audit and data analytics. He welcomes comments or suggestions at

Enjoying this article? To read the most current ISACA® Journal articles, become a member or subscribe to the Journal.

The ISACA Journal is published by ISACA. Membership in the association, a voluntary organization serving IT governance professionals, entitles one to receive an annual subscription to the ISACA Journal.

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/or the IT Governance Institute® and their committees, and from opinions endorsed by authors’ employers, or the editors of this Journal. ISACA Journal does not attest to the originality of authors’ content.

© 2010 ISACA. All rights reserved.

Instructors are permitted to photocopy isolated articles for noncommercial classroom use without fee. For other copying, reprint or republication, permission must be obtained in writing from the association. Where necessary, permission is granted by the copyright owners for those registered with the Copyright Clearance Center (CCC), 27 Congress St., Salem, MA 01970, to photocopy articles owned by ISACA, for a flat fee of US $2.50 per article plus 25¢ per page. Send payment to the CCC stating the ISSN (1526-7407), date, volume, and first and last page number of each article. Copying for other than personal use or internal reference, or of articles or columns not owned by the association without express permission of the association or the copyright owner is expressly prohibited.