EnterpriseOne SQL Security

Permissions in SQL for E1 are granted thru the System User/Multiplexing User's (JDE usually) 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, Copy Table, etc.)

Because the PUBLIC role has database and object level permissions it is imperative that any newly created SQL Server login that you wish to have read-only access to a database be placed in the db_denydatawriter database role in addition to the db_datareader database role to explicitly override INSERT, UPDATE, DELETE permissions granted to all database users via their membership in the PUBLIC role.

Note: Due to the way E1 grants object permissions via PUBLIC, it is not technically necessary to place a database user in the db_datareader role. However, doing so will make permissions viewing easier.

It is also important that a newly created SQL Server login be explicitly denied the create table permissions in the database at the user level if table creation is not desired.


Two ways to deal with this:

Continue to use Public security and

1) remove SQL Server guest user from each database (SQL 2000) or revoke CONNECT permission (SQL 2005/SQL 2008) and
2) be sure to place ad-hoc (non-E1 app) users in the db_datareader and db_denydatawriter roles for each database
3) Deny CREATE TABLE to new SQL Login

or

Perform Oracle's MS SQL Server 2005 Public Shutdown for JD Edwards EnterpriseOne

I prefer the first method.