JDE tables granted with update,delete,alter to public

Hi All,

My company is using JD EnterpriseOne 8.11 oracle database 10R1.
The problem is all the JDE tables granted with update,delete, alter to public, so that anyone have access to sqlplus are able to do updating on the JDE tables.
Oracle metalink suggested to remove public from all the tables, but this involve a major changes to the setup.

My concern is wan to create read-only id in sql.

Anyone got suggestion/recommendation? pls help.


Thanks and Regards
gogojde

[ QUOTE ]
Hi All,

My company is using JD EnterpriseOne 8.11 oracle database 10R1.
The problem is all the JDE tables granted with update,delete, alter to public, so that anyone have access to sqlplus are able to do updating on the JDE tables.
Oracle metalink suggested to remove public from all the tables, but this involve a major changes to the setup.

My concern is wan to create read-only id in sql.

Anyone got suggestion/recommendation? pls help.


Thanks and Regards
gogojde

[/ QUOTE ]


Here are some notes I made in the past regarding this issue.

1- The notes apply to SQL Server but you should be able to do the same in Oracle. I just can't be bothered to update my notes right now.

2- This may not be the best practice, some of the smarter people on here will correct any deficiencies in my notes.


=====

Permissions in SQL for E1 are granted thru the System User/Multiplexing User's (JDE) membership in the database role PUBLIC.

The PUBLIC database role has Create Table permissions.

The PUBLIC database role has SELECT, INSERT, UPDATE, DELETE permissions on all tables within the database.

The object owner (PRODDTA, PRODCTL, etc.) database role has explicitly granted object permissions to allow SELECT, INSERT, UPDATE, DELETE. This allows the object owner to perform certain actions through E1 (R98403, Generate Table in OMW, etc.)

Because the PUBLIC role has database and object level permissions it is imperative that any newly created SQL Server login that is granted access to a database be placed not only in the db_datareader database role but also in the db_denydatawriter database role.

It is also important that a created SQL Server login be explicitly denied the permissions in the database at the user level.

I have the same problem, could i remove some tables
from public and add this table to the jde_role or jde user??
without aplication problems?

or some one have any idea using oracle database?

Hi,

I´m having the same problem, my database is Oracle, and if i revoke grants for public, the jde doesn´t work.

Anyone has a solution for the PUBLIC role grants on ORACLE?

Thanks.