JDE Best Practice - Security

One of the most overlooked best practices when managing JDE E1 is user security. When you set up a new user inside E1 you have to assign them a password. Pretty simple, right? Unfortunately that’s not all there is to security inside E1. As shipped, JDE E1 is essentially wide open for all users and companies must develop a plan to handle all aspects of E1 security. Once the plan is in place it requires regular maintenance and adjustment as users come and go or company needs change.

Any good CNC knows that in a vanilla E1 installation, the JDE user and all the schema accounts (PRODDTA, SY900, CRPCTL, etc.) all have very well known default passwords on the database. Many times during an implementation those passwords will stay as shipped to make the process easier and the company may have the best intentions to address that problem after go-live, but that doesn’t always happen. If you don’t change these passwords, anyone with basic knowledge of E1 could access any data on your system.

A little-known tidbit about JDE E1 database security – in versions 9.1 and older, all database users have all permissions to all tables. This means if you give someone a login directly to the database they will be able to view, change, or delete any record or any table regardless of what permissions you give that user. The only way to fix that is by running the Oracle Public Shutdown procedure.

There’s also operating system security. The physical files that reside on your E1 servers are fully accessible by default. Some of them need to be available, but many do not need to be wide-open. The most important files to protect are your INIs since they contain passwords for your key system users. Prior to tools release 9.1.4.x you had to rely on OS level security to protect those files, but in TR 9.1.4.x and above the passwords are encrypted.

Inside JDE you can control the E1 user passwords as well as other details, such as password complexity. Most CNCs already know about that, but one overlooked part of E1 security is within OMW. If a user has access to OMW they will be able to assign themselves to any project as any type of user – developer, manager, PVC administrator – the sky’s the limit! There’s no built-in security management application for OMW users, so you have to use row or application security to keep people from assigning themselves to whatever they want.

There are many other questions related to JDE E1 security that you might have and it’s likely that the answers you get will create even more questions. JDE E1 security is a very complex topic and if you require unique or specialized restrictions you’re going to need an expert.

Generating indexes with OMW is a bad thing

You've analyzed the performance of your application or UBE, and after working with the DBA you've all decided that adding an index to the F0911 is the best way to speed up your slow process. You tell your developer who goes in to Table Design Aid in OMW and adds the new index. They check in the table and the CNC promotes it to Production, after which a package is built and deployed. The only thing left is to generate the new index in Production. The CNC opens OMW, goes to Table Design Aid, clicks the Table Operations tab, and clicks Generate Indexes.

Within a few minutes the calls start coming in: Everything is running slow. Processes are taking forever. Shipments aren't processing as fast as they should, product is being held up, trucks aren't rolling. What happened? This was supposed to speed things up!

What you probably didn't know is that the moment you click the button to generate indexes in OMW, the first thing it does is drop all the existing indexes on that table. That might not be a big deal on a small table with a few indexes, but it's a huge problem on a large table like the F0911 which ships with 25 indexes. Since that table is used by most operations in E1, the loss of all the indexes causes every application and UBE to slow down. That table is typically so large it could take 24 hours to regenerate them all. Even worse, there's really no simple way to roll that back. You just have to let it continue until it's done.

Unfortunately, OMW does not allow you to regenerate individual indexes. It's an all-or-nothing proposition. This also makes testing possible indexes a very difficult task. Faced with this, CNC administrators schedule index generation after hours or on the weekend to reduce the performance hit.

There's a better way to get index changes applied to your system. It's significantly faster, it doesn't kill performance, and you only change what's necessary:

Create the indexes directly on the database.

Yes that's right: You open a SQL tool like SQL Developer and make the index changes directly on the database. In fact, you can create your database indexes without defining them in E1 at all. This can help if you want to test your changes before committing to anything.

You might wonder how E1 will know the indexes are there if you don't create them in OMW first. The truth is that E1 itself doesn't actually know or care what indexes are on the database. Behind the scenes E1 simply issues SQL statements to the database without any regard for indexing. Generally only developers and CNC admins need those specs inside E1 to facilitate their work. The transactions flowing out of E1 to the database don't look at those specs at all. When a transaction arrives, the database decides what index to use (if any) and executes the statement in the way it thinks is best.

That's not to say you shouldn't create indexes within E1 - you certainly should. You're going to need them for ESUs, upgrades, and E1 development, not to mention other CNCs that may not know about your new secret. By creating indexes manually on the database, you not only save huge amounts of time but you can test indexes quite easily. If you create an index and it doesn't do what you thought, you can easily remove it and try another one without having to go back through the development cycle.

Remember this new, faster way to get index changes into your system:
1. Determine what index you need.
2. Add it directly to the database.
3. If it works the way you want, add it inside E1.
4. Don't generate indexes with OMW!