>ORACLE JDE Risks and Gaps

When JDE is installed on an Oracle database and the first user logs onto the system, many people have a false sense of security that the hard work is done. Unfortunately, there are a multitude of technical issues lurking just beneath the surface that will ultimately need to be addressed -- sooner or later. The choice is a simple one -- pay a reasonable price to fix it now or pay a much steeper price to fix it later.

Like so many other aspects of Information Technology, playing the waiting game and ignoring the inevitable will lead to nasty side effects like costly system outages and firefighting to fix production issues. Most of these issues can be avoided altogether with a bit of planning and the proactive implementation of industry-accepted best practices.

DBConnect Solutions has summarized just a few of the basic risks and gaps inherent with any JDE EnterpriseOne implementation on an Oracle database. If you'd like to learn more about these issues and to find out how we can help you address these areas, please give us a call.

>Oracle PUBLIC Access

By default, whenever an Oracle table is generated through JDE, full Oracle database access (SELECT, INSERT, UPDATE, DELETE, ALTER, etc.) is granted to PUBLIC. This means that any Oracle account in the database can essentially do anything to your JDE data! Unfortunately, this means that your production data can be completely compromised, and it may not be readily apparent for weeks, or even months. This type of unmanaged security architecture will raise serious issues when it is discovered in a system audit, but worse yet you run a substantial risk that your entire business could be irreparably harmed if someone intentionally, or unintentionally, modifies or destroys data.

At first glance you may be tempted to simply revoke access from PUBLIC but this is only a temporary solution (and it will also likely break JDE depending on the security model you have in place). In addition, whenever your JDE CNC Team recreates a table or applies a JDE patch, the PUBLIC access is reapplied to the impacted tables! So there is simply no easy way to protect your JDE data without constant manual intervention.

We solved this problem at DBConnect Solutions with the creation of a tool we call the "JDE Security Sniffer". This tool monitors the database, and when it detects the presence of PUBLIC grants on JDE-owned tables, it removes the PUBLIC access and assigns appropriate access to Oracle roles. It then automatically emails a log file to the Oracle DBA so there is an audit trail of the activity. We also provide an Oracle database security report that identifies any JDE-owned tables that may still have PUBLIC access, or those tables in which appropriate access has not been granted to Oracle roles. This solution is entirely automated to ensure our databases always remain secure.

The DBConnect "JDE Security Sniffer" has been in place at numerous companies for over a decade. It has been vertified by internal and external auditors as providing database compliance that meets the requirements of Sarbanes-Oxley.

>More Oracle Security Architecture for JDE

Like other leading ERP systems, JDE is intended to be database vendor-agnostic. Therefore the product is not engineered to take advantage of the vendor-specific features available in each database platform. From a JDE perspective, Oracle (and other supported database platforms) is essentially a flat file manager. JDE connects to the database via a driver that communicates with software libraries provided by the database vendor. In terms of Oracle, the JDE database driver does not utilize any sophisticated security mechanisms. It relies totally on the security restrictions in place within the database itself. Out of the box, JDE grants PUBLIC access across the board and creates a single Oracle role that is assigned to JDE Oracle schema owners. JDE creates a number of Oracle accounts, including a DBA account, that are not typically used by the application. Further, the Oracle service account used by JDE is sometimes granted Oracle DBA access, which is not only unnecessary but can create Sarbanes-Oxley compliance issues for publicly-traded companies.

It is the responsibility of the Oracle DBA to reverse-engineer the default security model and implement an appropriate one to comply with the security requirements of the organization. These engineering efforts revolve around the definition of appropriate Oracle accounts, Oracle roles, Oracle system privileges, JDE Oracle object privileges, custom Oracle object privileges, Oracle password complexity and password expiration rules, and many other Oracle security issues.

DBConnect Solutions has extensive experience implementing appropriate Oracle security models for JDE. This experience can be immediately leveraged to set up SOX-compliant Oracle security models for new JDE implementations, or to remediate non-compliant security models that are already in place. DBConnect has written a number of tools that can be quickly put into place to manage all aspects of the JDE Oracle security model.

>JDE Oracle Bolt-Ons

It is common for customers installing JDE to implement 3rd-party solutions to address functionality not available within the JDE software. Some customers also write custom code to handle integrations between JDE and external systems, such as Demantra's Demand Planning module or Manhattan's Warehouse Management System. From a database perspective, managing the "bolt-on" process is particularly challenging if the DBA desires to implement a security model that doesn't compromise the rest of the system. Unfortunately, a typical security model that is widely seen in the industry is to grant the Oracle owner of a bolt-on product full read/write access to all JDE tables in the database -- generally as a matter of convenience. However, such decisions of convenience can lead to serious production and compliance issues, if not disastrous data integrity issues down the road.

Another big challenge of bolt-on packages and bolt-on customizations is managing the change control process after the initial go-live date. This is because there are often numerous database changes needed to integrate these bolt-ons, including custom permissions, private synonyms, database triggers, and other custom database objects. Because these changes are not managed within JDE, nor specifically known to JDE, there is a very high probability that any bolt-on tools will malfunction when any JDE-driven changes are made to the Oracle database.

We have addressed these bolt-on issues at DBConnect Solutions by writing Oracle object inventory management tools that maintain lists of the approved non-JDE changes made to the Oracle database. The tools monitor the database to make sure all necessary components are in place, and issue alerts when any missing components are detected. In the area of Oracle permissions, the tools automatically re-apply any missing custom privileges and alert the DBA for follow-up.

>JDE Oracle Refreshes

The JDE project implementation process requires numerous environment refreshes. This is an extremely time-consuming, if not impossible task, to accomplish via the JDE toolset when the database grows larger than 10-20 gigabytes. However, it can be accomplished using native Oracle utilities in a much faster and more reliable fashion. But refreshes are not limited to the implementation phase of the project. After go-live it is necessary to conduct periodic refreshes as well.

DBConnect Solutions has written a fully-automated, parameter-driven Oracle database refresh tool that can be leveraged to make environment refreshes much more efficient and reliable. The tool also supports a "refresh with exception list" option to allow one environment to be refreshed to another while skipping certain key tables such as UDCs and menu definitions

>JDE Job Performance on Oracle

JDE provides poor visibility to the root cause of performance issues on Oracle. It is extremely difficult to correlate JDE jobs or processes to the corresponding Oracle processes running against the database. It is therefore also difficult to trace the related database activity (i.e. SQL statements and their efficiency) of these corresponding Oracle processes.

DBConnect Solutions has developed a tool that automates the monitoring of designated JDE batch jobs and optionally will allow the associated Oracle processes to be fully-traced at the database level for review and remediation. This information provides in-depth visibility to the inner-workings of JDE batch jobs running on the Oracle database.

>JDE Oracle Performance Management

Because JDE is not optimized for Oracle, it does not perform operations on sets of data at a time. It tends to operate on Oracle data a single record-at-a-time, which presents unique challenges to the Oracle DBA as historical data accumulates in the database.

DBConnect Solutions has extensive experience evaluating database performance and addressing performance issues through the use of Oracle statistics, including histograms and SQL profiles, as well as setting up appropriate techniques for calculating Oracle statistics. We have also written data archival utilities to move data from a master set of JDE Oracle tables to archival tables to maintain good performance within the system.

These are just a few of the areas that are very specific to JDE EnterpriseOne on Oracle. DBConnect Solutions has years of experience addressing these and many other issues, and we stand ready to leverage this experience for our clients.

Call us today to discuss how we can help you with your JDE Oracle projects.