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!

The shocking truth: You can't create a read-only user on your E1 database!

Did you know that by default all users created on Oracle and MSSQL databases have full permission on all JDE tables? It's true. Not only that, this permission takes precedence over any restrictions you might place on a particular DB user such as making them read-only. This is hard-coded in to EnterpriseOne and Oracle has no plans to change this behavior.

Why is this? JDE is designed to insulate users and administrators from the hardware and database. This is part of the spirit of CNC - Configurable Network Computing. Yes, CNC actually means something other than "Person Who Gets Blamed For Everything". The CNC concept means that with only JDE tools a user can perform select, insert, update, delete, create, and drop operations on any table.

Because you don't have to know what database or operating system is being used, you don't need to have any specific database skills. Without ever leaving EnterpriseOne you can do full environment refreshes, table copies, add new indexes, and much more. The best part is that it is all controlled by JDE security. You never have to touch the database to do your day-to-day job.

As flexible as that is, there are often good reasons to directly access the database. One example is to do faster refreshes. In the middle ground are SQL fixes due to data issues. On the other end are bad reasons which (in my opinion) include providing read-only access and large exports of raw data. I'll talk about why those are bad in another post.

No matter what the justification is for providing direct access, the shocking truth is that every database user can view and manipulate all JDE tables and data on the system - and there is nothing you can to do change that.

I take that back. There actually is something you can do to change it. It's called Oracle Public Shutdown (OPS), a somewhat complicated procedure that removes these global permissions and allows administrators to have better security control at the database level. Once you've run OPS you can now create read-only users as well as any other levels of access you can think of.

Unfortunately for your administrative workload, OPS is not permanent. Every time a table is created or regenerated in OMW, every user gets full permissions reassigned to that object. Every ESU, ASU, and Update that contains tables will reset those permissions. An upgrade will reset many permissions. What this all means is that you have to come back to your database every so often and re-run OPS.

The next time someone asks for a database user, make sure there is a really good justification for their access. You'd be surprised how often they can do what they want inside E1. Plus, you don't want to give what is essentially DBA authority to just any user. Related tip: Make sure you have good backups.

If your client is concerned about the security of their database - especially if they have SOX or FDA requirements - they may need Oracle Public Shutdown. The GSI Database Team can help you evaulate your client's needs and work to secure your database.

Protecting your data from end-to-end

It’s pretty difficult to have a database without some protection for the data. You need a database user name and password to access the data at the very least. If you don’t want someone to see your data, you just don’t give them the credentials. That seems pretty simple, doesn’t it? Well, it is simple, but having a single user doesn’t allow for any flexibility in determining different levels of access.

Does everyone really need read/write access to every column in every table? Normally that answer is “no”. You’re probably going to need a read-only user, and maybe other users that are prevented from seeing sensitive data. It could get complicated setting up all those additional users and roles.

I’m not like the others

Maybe your installation is different and users don’t access the database directly. Their access is controlled by an application which handles all the security and permissions across the board. That application only needs a single user to connect to the data, and nobody but you knows the password. Well, you and the application administrator. That should be fine, right?

Even in a perfect world, there will be mistakes that can’t be corrected within an application. Will you, as the DBA, be performing all those corrections? What about analyzing the raw data to determine what corrections to make? Will you also be exporting raw data for business analysts to pore over? Chances are you won’t have the time or inclination to do all of that. You’re still going to need some additional database users with varying levels of authority.

Your data is still vulnerable

You’ve got users with specific permissions, you’ve got security in the application, and maybe you even have auditing enabled. The only people who can use the application or access the database are on the corporate network or VPN. You feel pretty safe right about now, don’t you?

What about operating system credentials? Who has access to the root or oracle account? What users have sudo rights? Anyone on the operating system can view or manipulate your data with OS superuser privileges. They don’t even have to access the database directly! Now imagine if someone outside your company – a hacker or corporate spy for example – finds an exploit that gives them access to the server operating system. Even if they can’t find a way to use SQL, they can get your data using OS commands as well.

Data encryption

Data is stored in the database in plain text. Despite all the user security you might have in place, the fact of the matter is that if someone gains access to your data files they’re going to be able to see the info. Even inside the company with application security you may be able to prevent people from seeing certain columns of data, but that data is still stored in the database in plain text. Unless you encrypt the data, your data is vulnerable.

The data sitting in a database is considered data at rest. This can be recent information or historical, archived data on a data warehouse. Data that is currently being manipulated or used in any way is data in use. Data moving across a network or other medium is data in transit. Data in in transit is usually encrypted by VPN or other active methods. Data in use is going to be in memory, and that is normally not encrypted. Data at rest is also generally not encrypted and depending on your database, you might not even have the capability to encrypt it at all.

Oracle Advanced Security

How do you protect your data in all its various states? To encrypt data at rest, you can use Oracle Advanced Security in Oracle 12c. Transparent Data Encryption (TDE) helps prevent attacks that attempt to bypass the database and read sensitive information from data files at the operating system level, from database backups, or from database exports. Oracle Advanced Security Data Redaction complements TDE by reducing the risk of unauthorized data exposure in applications, redacting sensitive data in query results before the data leaves the database.

What about data in use or in transit? For that you have to get serious. The Oracle Supercluster’s SPARC M7 processor protects, verifies, and encrypts data in use with built in, dedicated encryption accelerators. Oracle Solaris 11 includes cryptographic APIs which the ZFS uses to benefit from AES algorithm hardware acceleration on SPARC processors.

Data protection isn’t simple

Protecting your data isn’t as easy as having strong, secure passwords. Even with that your data is still unprotected and exposed. Whether that data is actively being used or if it’s sitting on an old, slow SAN somewhere, it’s vulnerable to a determined attacker. With Oracle 12c and Supercluster you can protect your data from end-to-end with little to no performance compromises. 

Don’t patch your database!

I imagine there was a lot of spilled brandy and lost monocles upon reading that title. Now that I have your attention, what I really meant was “Don’t patch your database without giving it careful thought.” I bet some of you are probably thinking, “Well of course that makes sense, why wouldn’t you do that?” That’s a really great question. It does make sense yet many people approach their patching plans without thinking through all the possible factors.

 “But wait,” you say. “I worked directly with Oracle to figure this out! They strongly recommended this patch. It has fixes that can help this problem.” Okay, that sounds great. Now tell me how applying this patch will affect the business?

A technically-oriented person would say that it fixes a problem that can only help the business. It’s a no-brainer to apply it. What they might not consider is that there’s often more than one fix in a patch. Sure you’ve got this one bit in there, but what about the rest of it? What other things does the patch touch? Are you sure that the improvements within are all beneficial (or at least benign) to the system?

An extension of that topic is “recommendation creep”. This is when you’re applying a patch with specific fixes, but there are strongly recommended security patches or other add-ons that can be applied at the same time. It might be very tempting to include these patches, but you have to ask yourself if they are truly going to add value right now. It doesn’t matter how “strongly recommended” a patch is – if it’s not adding value then it really is okay to leave it out.

All right, you’ve looked at the patches and determined that you’re not going to break anything. In fact, you took out a patch that doesn’t really matter right now. The next thing to look at is how the actual patch event will affect the business. When you are applying the patch, what is happening to the business?

I’ll use a real-world example for this. A company with performance issues decided to apply a database combo patch at the recommendation of a consulting company and the database vendor. The only problem was that applying the patch required 6 hours of downtime for their entire business. The company had 24/7 worldwide operations and could not find more than 4 contiguous hours even after shifting around numerous critical operations.

They asked for my advice on the patch, and I found that the patch they wanted to apply was a combo patch – three separate patches bundled together. In truth, only one of those three patches were required and it could be applied in a fashion that would result in zero downtime. In fact, they could still apply the entire combo patch in a slightly different order and reduce the total downtime to only one hour.

When you make the decision to patch the database, you’re not just fixing a problem. You could be introducing new problems as well, and not just with additional steps and fixes. Before you patch, ask yourself these questions:

• Does this patch actually fix a problem? Don’t apply patches just because you hope it’ll fix something.
• What else does this patch do? Don’t inadvertently apply something that changes something critical.
• Are there parts of this patch that aren’t really needed? It might be nice to add on that security patch, but don’t change too much at once.
• How does the patch process affect the business? Always try to reduce downtime even if it makes the process itself a little more complex.

Remember this: In the end, you are the one applying the patch. No matter how strongly someone recommends you do something, you are the decision maker. You really don’t have to apply a patch just because it’s on a list somewhere. Look at the patch, study it, test it, and know exactly how it’s going to affect your system and your business before you drop it in.

Oracle Standard Edition is changing in December

You probably already know that Oracle includes the Standard Edition (SE) of their database as part of JDE E1 licenses. Currently, Oracle SE allows customers to use four CPU sockets and unlimited cores. Oracle Real Application Clusters (RAC) is free with SE as long as you don’t have more than four sockets in the cluster. While SE does not have many of the advanced features that Enterprise Edition (EE) has, most JDE E1 clients find that it’s more than sufficient to power their enterprise.

This changes in December when the latest Oracle 12.1.0.2 patch is released. Oracle SE is being replaced by a new product called Standard Edition 2 (SE2), and with it comes many important differences. The biggest is that SE2 will be limited to two sockets and 16 CPU threads (cores). Oracle RAC is still available, but you cannot exceed the total number of sockets or cores. That means a two-node RAC on SE2 can have 1 CPU socket and 8 cores per node. Existing installations of 12c SE will go on free extended support until Dec 2016 (all versions of 11g go off support completely in January).

Despite this major change, using the included Oracle 12c SE2 for your JDE E1 database is still far cheaper than any other alternative for a standalone DB server. Many clients don’t use more 16 cores on their databases, so this will probably not affect the majority of JDE E1 installations.

That’s not to say that nobody uses more than 16 cores. In those cases, upgrading existing databases to 12.1.0.2 will require clients to change their configuration to match the new licensing requirements. If you’re not ready to downgrade your specs, another option is to remain on the current 12c Standard Edition indefinitely. Support may end in December 2016, but you can still keep using the database.

If you have too much activity to scale back the number of CPUs, consider splitting the database in two. E1 allows you to move any part of your data to any database and have it all be transparent to the application. Simply add a new Oracle SE2 database, then move some of the data over to that new database. You could, for example, put all the system databases (system, server map, object librarian, data dictionary) on the new database server and leave production data on the original machine.

The last Oracle option is the most expensive but is also the most flexible. Oracle Enterprise Edition (EE) allows unlimited sockets and cores, plus you have access to all the advanced features available for the Oracle database. If you’re already on EE then all this talk about changes to SE doesn’t concern you at all.

References

Oracle database edition comparison

https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109

Oracle SE2 product page

https://www.oracle.com/database/standard-edition-two/index.html

Oracle Lifetime Support Policy

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

When was the last time you practiced recovery of your production database?

Are you prepared to recover your database in the event of a disaster? Go ahead and pull up your disaster recovery plans and let’s go over them now. I’ll wait. Oh, you don’t have it written down? Hmmm.

You’d be very surprised how many companies don’t have a disaster plan at all, much less a plan that is written down in some way. Most that do have a plan are dependent on a single person to execute it. Not only that, the person in question has the entire plan in their head.

Here’s a scenario: Your DBA has always wanted to climb Mt. Everest, and they’re going to be gone for two weeks with no contact to society at all. Two days after they leave you get a disk failure on your primary storage. No problem, you think, we have redundancies. Your vendor swaps out the disk, but something is wrong: The data has magically disappeared from the redundant disks.

Sound far-fetched? Not really. Even if you’re hosted by Amazon AWS, you can suddenly lose all your data with no warning or explanation. What do you do now? It’s a disaster, time to recover.

The key part of a disaster recovery operation is a good backup. That’s a no-brainer. How often you back up and what kinds of backups is up to your business. How much data can your company lose? 24 hours? 2 hours? Ten seconds? That will drive your backup strategy.

Now that you have a backup, what is your Disaster Recovery plan? Don’t tell me it’s just “restore the backup”. What about third-party software? Cron jobs? Config entries? All those little bits and pieces you’ve cultivated over the years could be gone. The time to figure out what those were is not when the C-levels are breathing over your shoulder counting the money being lost while their systems are idle.

Practice your DR plan. The plan is no good if it can’t be executed successfully. Maybe you’ve written down a comprehensive plan, but step 17 is a reference to somebody’s blog that was taken down long ago. You’re definitely going to want to have it all local and all spelled out. You might want to go as far as to print it out and store it in a bank deposit box. If your disaster is that the building was destroyed by a tornado, the easiest part is going to be buying new hardware. The hard part is recreating your DR plan from scratch.

Don’t forget to back up your backup scripts and DR plans. It may not be that hard to recreate a backup plan, but for the poor schlep left behind while you’re climbing Everest - it could be impossible.

If you don’t have a Disaster Recovery plan right now, you can create it immediately. Open up a blank document, title it “Disaster Recovery Plan”, then type out the sentence “I do not have a disaster recovery plan.” Email that to your boss and file it where everyone can find it. At least they will know you are prepared.