Oracle PUBLIC shutdown for JDE E1

James Monroe

Active Member
We are on JDE E1 9.0, tools 9.1.4.7.

During a data masking project started upon by our IT, we were testing the third party data masking solution and it ran into an issue with the fact that all Oracle DB objects were set to give all privileges to PUBLIC. This is prevent the data masking solution from working.

Opening a ticket with Oracle, they confirmed that prior to 9.2, all database objects are shipped with all privileges assigned to PUBLIC. As of 9.2 this has been changed, but it presents an issue with our 9.0 and data masking.

Oracle/JDE support provided a document 'Oracle Public Shutdown for JD Edwards EnterpriseOne'. This document and the accompanying scripts represent an approach that can be used to move from PUBLIC to where you are giving specific privileges. And the document does say that this document describes an 'approach' that can be used and implies that you may need to review it for your particular environment.

Has anyone had to do this type of thing, moving away from the delivered PUBLIC security. Any experiences to share as to difficulty or things to look for?

I am a long time JDE developer, not a CNC consultant but can build/deploy packages. That is about the extent of my CNC knowledge. Does this solution of moving away from PUBLIC oracle security setup seem pretty straighforward? or is this something that you might want a more experienced CNC person on consultant.


View attachment Oracle Public Shutdown for JDEdwards EnterpriseOne.pdfView attachment Oracle Public Shutdown for JDEdwards EnterpriseOne.pdf
 
James,

Our DBAs created a database role for JDE use which pretty much gave full access to the JDE Objects, and removed access from PUBLIC. This was done years ago. We have upgrade several times since and the DBAs implemented the same thing in the new database each time. Originally, the DBAs went through the JDE Public Shutdown document and I think they may made some minor adjustments to it.
 
Thanks for your notes Peter. One thing did just occur to me though. I believe our current system shares security tables amongst DEV, QA, and PROD. System security tables such as F98OWSEC are in SY900 and that is used by all environments.

Does that mean that these changes would have to be made to essentially to our live system and we would have some downtime, unless we were to split the security by environment. I would think something like this you would want to test in a DEV environment before doing it in production. But this would be a first for me.
 
"Has anyone had to do this type of thing, moving away from the delivered PUBLIC security."
I can't imagine not doing this.
As delivered since JDE time began (until 9.2) JDE has been delivered with no data security.
Your kimono has been open and pants pulled down since whenever you installed.
Your IT department should have addressed this long ago.

We developed a PL/SQL Script that, for all JDE DB Schemas, removes Strips PUBLIC privileges and Grants Privileges to a JDE Role that only specific DB Accounts are a member of. Sample Output of script looks something like this:
REVOKE ALL PRIVILEGES ON proddta.f0101 FROM PUBLIC;
GRANT ALL PRIVILEGES ON proddta.f0101 TO jde_role;

For any data that needs to be exposed external to JDE (3rd party BI tools, external interfaces, etc.) we developed DB Views and granted access on a as needed basis only.

Its the old security model of "Deny All, Grant back only if needed".
 
Thanks for the reply Larry. That makes sense. I'm the only JDE person here and I'm the one and only JDE apps developer here with enough CNC knowledge to be dangerous, so I am proceeding slowly and deliberately on this. We have no CNC staff. We have DBAs but they are all PeopleSoft DBAs, with no JDE knowledge. Thanks again.
 
Back
Top