Auditing JDE: Correcting Production Data and being Sox Compliant

To err is human. To update wrong data in the wrong field at the wrong time is the hallmark of every business analyst's career.  More often than not the responsibility to correct this data falls on the head of the IT team and for that they need UPDATE ACCESS TO PRODUCTION!

This brings us to the nightmare scenario of a developer who may need update access in production to make updates as and when needed. Even though this scenario is legitimate and the data needs to be updated, a SOX auditor would not agree to the fact that a DEV has update access on production data. To make the auditor happy and maintain his faith in the integrity of your financial data you would need to design a process that not only helps you get the job done, but also keeps you Sox Compliant.

Let's examine one such process:

1. For this to work perfectly you would need the Help-desk team, the dba team and the application support team.
2. Let the dba team design a functional account on the database with update access and self expiring password. The account should be such that the password, once generated, should expire in 4 hours
3. Add all your support team members who may update data in prod to a list which captures responses for few secret questions personalized for each member of the team. 
4. Share this list with help-desk team. They should be trained to challenge the caller with the secret question and tally their response before forwarding their request for password to the dba team.
5. Once a data issue is identified, the Business Analyst raises a High Priority incident in the Incident Management System.
6. The Incident gets assigned to the app support team, who then raise an Emergency Preventive Change Order to update the data, citing the Incident that was just created by the Business Analyst.
7.  Once the CO is raised, the app Support team member calls the Help-Desk to get the password for the functional account.
8. The Help-Desk team co-ordinates with the DBA team to get the password generated and then share the same with the app support team member.
9. Once the password is generated, the app support team member updates the data that was requested in the INC, closes the INC and closes the CO, leaving the password to expire.
10. This maintains chain of control over the account being used to make the update and also makes sure the account was used for intended purposes.

This 10 step plan, not only maintains data integrity but also helps document each update , commonly referred to as back-end update to the database.

Auditing JDE: Let's look at the Packages

I have always stated that auditors need to have a basic understanding of the JDE system before they set about designing their "Test of Design" documents for gathering evidence from a JDE system.

In that light, lets talk about the area of interest: Package information in JDE


Simply put, the below query when run on the database shall give you the information you are looking for as an auditor:

SELECT * FROM SYSXXX.F96215 where UPMJ > '116000' 

Let's look at what the query entails. In plain English, the query is looking for data in the table F96215 in the SYSXXX schema of the database where the build date is greater than 1st Jan 2015.

SYSXXX stands for the System schema in a JDE database, XXX denotes the version number of the JDE product in question. For instance, an E812 system will have a System schema named SYS812.A Schema in a database is a specialized area which has a particular type of tables characterized by the type of data they hold. A simple JDE setup will have the following schema in its database which may be of interest to the auditor:

SYS812 - System Schema
SVM812 - Server Map Schema
PRODDTA - Business Data
PRODCTL - Production Control tables

The list provided by the query should have the package names that were built during the past year and it should be fairly simple for the auditor to match the names with those on the list of Change Orders that they collect as evidence of approval for the changes that led to those packages being built.

It's always a good practice as a CNC to put the name of the package built, on the change order that was raised for the change being carried out. This makes it easier for all the parties involved to gather evidence showing only approved changes made their way into the system.

Auditing JDE : Let's look at the Users

I was recently asked by a peer about F00925 and it's value in JDE audit. The specific question was about a particular column on it and why and when that column populates.

When I answered that question for him, it occurred to me that may be this is something that a lot of auditors and CNC's alike would like to know about.

A typical Test of Design document for JDE talks about getting extracts from the following tables to get to know about the User population of a particular JDE setup:

F0092 - Library Lists - User
F0101 - Address Book
F98OWSEC - One World Security
F00950 - Security WorkBench Table

The general idea is that if you have records in the User profile Table (F0092) and can match it with the Address Book (F0101), you would be able to judge the access a user has based upon the roles in the security work bench (F00950). F98OWSEC, is used to judge whether the user is active, when was the security last updated along with the frequency of password change set in the system.

In theory the above practice would give you enough opportunity to look through any unscrupulous access a system may have provided to any given user or a role. However, JDE is a much more nuanced system to be judged based upon just 4 tables. They may be able to lead you to the particular set of fields in a given table a given user may or may not have access to, but you would not be able to answer what all a given user can see, based upon data in just the above four tables concerned.

For that level of knowledge, you would need to have an idea of the normalized tables in the Address book as well as the Control tables that are involved in the setup of the Address Book. The default Address Book application provides 30 category codes. These codes can be altered via the UDC application to point to various aspects of business, like Profit Centers, Customer type, etc. For all of this to make any sort of sense the following list of tables need to be extracted from the JDE setup:

F00924 - User Install Packages
F00922 - User Display Preferences Tag File
F00921 - User Display Preferences
F0093 - Library List Control
F98OWSEC - One World Security
F00950  - Security Workbench Table
F00925 - User Access Definition
F95921 - Role Relationships Table
F00926 - Anonymous User Access Table
F9005 - Variant Description
F9006 - Variant Detail
F0092 - Library Lists - User
F0005 - User Defined Codes
F0004 - User Defined Code Type

 Armed with the above tables you can now determine any nook and corner that a user of interest may have access to in the JDE system. It still will not tell you whether the user gets powers to change statuses of projects etc, buts that's topic  for a different blog post :)

Work with Activity Rules

One of the core feature's of OMW(Object Management Workbench) administration in JDE is the Activity Rules and their setup.

Activity rules govern the type of actions that are feasible for a given status of a project in OMW.

Let's begin with a specific example. My test setup has a status 71 which is used by the developers to denote that the project is ready for production move. This status however does not allow the CNC to do a "get", which is a good practice to ensure the latest code is promoted to production.

So if 71 is prod-ready, the CNC will have to move the code to 65 (QA) to do a get and then send it to 71 and finally send it to 72 (Production).

If the CNC now decides to update the activity rule so that "get" can be done for all the projects in status 71, following are the steps the CNC would have to carry out:

1. Go to GH9081
2. P98230
3. Launch the Activity Rules
4.  Click find
5. Highlight Status 71
6. Click Add 
7. On the Project Status Activity Rule screen, put 71 in the From Project Status
8. Add an entry for the role for which the "get" needs to be enabled
9. Click OK
10. Click Find
11. Double click the new entry for 71 that just got created
12. Add the Object Types that are needed for Allowed action type 05
13. Click OK

This  enabled get for status 71. Just make sure all the object types are covered in the list for the action that is implemented.

The following SQL should give all the entries in F98225  for our scenario:

SELECT TROMWFPS, TROMWTPS, TROMWUSER, TROMWOT, TRSRCRLS, TRTRGRLS, TROMWSRCLOC, TROMWTRGLOC, TROMWAC, TROMWRLSTK, TROMWACTFLG, TROMWTMS1, TROMWTMS2, TROMWTMN1, TROMWTMN2, TROMWTMN3, TRPID, TRMKEY, TRUSER, TRUPMJ, TRUPMT, TRTIMEZONES, TRDSAVNAME FROM SYSXXX.F98225  WHERE  ( TROMWTPS = '71' AND TROMWFPS = '71' )  ORDER BY TROMWOT ASC,TROMWSRCLOC ASC