JDE database users in Oracle

peterfourie

Active Member
Hi All,

We recently migrated from DB2 UDB on Windows to Oracle on Windows. The
migration all went went well and everything worked great.

In order to get the Oracle database ready, we ran the standard JDE scripts
to create the database etc, and then copied the data over from DB2 using
R98403.

Anyway, all the users created in Oracle (i.e. Proddta, testdta etc etc.) are
members of PUBLIC and have full db rights (i.e. inseert, update, delete
anywhere in the database). Is this normal, or should a use like Proddta be
restyricted to only the tables within it and not have full rightsa to say
TESTDTA? Are there any best practices oif this should be changed?

Also, we need to change the passwords for proddta, testdta etc. Is this doen
at the Oracle database level and then JDE will just pick it up (e.g. when I
need to gerenrate a table, it will know that I need the new password)? If
not, where can I find the app in JDE to do this?

Any help would be greatly appreciated.

XE SP 19 Update 6
Intel NT, Oracle 9.0i

Peter J Fourie
 
In my opinion,, JDe/psft user id ( your proxy user) needs to have access to public.... Proddta needs to be the dbo since it own the table......THis needing to be in public role might need to be looked at...

You can change the database password for Proddta... Only time it use this password is when you are generating the table and it will ask for the db password and you can supply it... In additon, these passwrod are used during table conversion too.... for that, there is change you make in the jde.ini

This is all I can think of this late friday night


I hope this helps
 
Peter--

Would you be willing to share your migration strategy with me? I will be putting together the initial analysis for this same type of migration--do you have docs on any of the following topics?
• Project Scope and Definition:
• Analyze possible customizations and platform differences
• Develop infrastructure plan
• Design the physical layout of the new Oracle database(s)
• Capacity planning for the number of servers (CPUs) for the new environment
• Develop a strategy for system backups and recovery
• Provide benchmark testing results from new hardware configuration
• Develop Hardware Design Recommendations for Network, Server, and Storage where appropriate as based on the IT Management Objectives
• Develop Migration Path from AS400 to Oracle Windows that is best suited for ERP Packages

any thoughts or documents that you can share with me would be very helpful and I would appreciate them greatly!

I would even buy you lunch!

David Schoenberger
 
The default approach by JDE is pretty poor but there are a number of traps available if you change things. By default each schema owner is created with a password equal to their userid. These should be changed in general, you need the password when creating a table or index but otherwise they are unused EXCEPT if you run R98403 to copy (for example) production to your test environment when it explicitly assumes the password matches the userid and thus fails.

Every time you generate a table (and I believe an index) it regrants full access to public. There were notes in the Knowledge Garden about improving security (lots of luck on finding them now) but in essence since by default everybody connects as a single user (JDE) you can revoke all from public and grant all to JDE.

Next step would be to create separate system users for different roles at the oracle level and grant them the appropriate rights (prod or test schemas for example) and then associate those system users with the JDE userids.

That would start to get you somewhere and would also make any auditing you run at the oracle level meaningful.
 
Back
Top