Bill Rehm, Sr. Solution Consultant
JD Edwards is designed to insulate users and administrators from the hardware and database. This is part of the spirit of CNC - Configurable Network Computing. The CNC concept means that with only JD Edwards 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 in JD Edwards, 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.
Users created on Oracle and MSSQL databases have full permission on all JD Edwards tables These permissions takes precedence over any restrictions you might place on a particular DB user such as making them read-only.
As flexible as JD Edwards is, there are often good reasons to directly access the database including faster refreshes and SQL fixes do to address data issues. No matter what the justification is for providing direct access, database users can view and manipulate JD Edwards tables and data on the system.
There actually is something you can do to change it. It's called Oracle Public Shutdown (OPS), a 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.
OPS is not permanent. Each time a table is created or regenerated in OMW, users get 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. 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 evaluate your client's needs and work to secure your database.
For assistance with JD Edward, please email us at inquiries@GetGSI.com.