Default SQL Permissions in EnterpriseOne Part 3

As mentioned in parts 1 and 2 of Default SQL Permissions in EnterpriseOne the permissions granted in EnterpriseOne databases during the install leave quite a security exposure.  Those articles covered in depth the reasons for this and ways to address the problem.  This article will detail two more items of concern: database level permissions set by the install and Guest user access from a dangerous set of permissions associated with the Public role.


Create Table

During the install the file ce_InstallSQLDatabase.BAT is executed and grants CREATE TABLE to PUBLIC:

osql -U%SYSADMIN_USER% -P%SYSADMIN_PSSWD% -S %JDE_SRV% -w 500 -n -Q "Grant CREATE TABLE to public" -d PS_%UENV% -b

The EnterpriseOne install does this to enable the generation of tables through OMW.

While I don't think this is as bad as users having write access to your data there are still ramifications.  Any user who has been granted access to the EnterpriseOne database can create a table and possibly completely fill the filesystem, effectively performing a denial-of-service attack by consuming all disk space on the volume.

To address this, one should REVOKE the CREATE TABLE permission at the database level from the PUBLIC role.  To enable table generation from OMW GRANT the CREATE TABLE permission to the appropriate users for the schema name (TESTDTA and TESTCTL in JDE_DEVELOPMENT, PRODDTA and PRODCTL in JDE_PRODUCTION, etc.).  Alternately, you could create a database role in each database, place the appropriate users (CRPDTA, CRPCTL, TESTDTA, etc.) in the role and grant CREATE TABLE to the role.  CREATE TABLE permissions already exist for the JDE database user in each E1 database so installs and upgrades should not be an issue.


Guest User

The Guest user in EnterpriseOne databases is more insidious, especially when combined with the fact that Oracle grants permissions to the PUBLIC role in all EnterpriseOne databases. 

The guest user account allows a login without a user account to access a database.  It is essentially a user account within the database that logins can use when they are not granted explicit access to a database.  The Guest account is dangerous for that ability and most dBA's remove the account even without the additional permissions it gains when present in an EnterpriseOne database.

If you recall, our earlier articles on E1 SQL Permissions documented how every user in a database inherits full permissions to all tables by virtue of being a member of the PUBLIC role that the install grants those rights to.  Well guess what role the Guest user is a member of?  Yep, PUBLIC.  Therefore any login on a SQL Server that is not granted access to an EnterpriseOne database (and thus uses Guest to connect to the database) still has full, complete rights to every table in that database.  Here's how (test this if you like):

A login is created on a SQL Server and is not granted access to the EnterpriseOne databases.  Since, by definition a login without a user account in a database will use the Guest account, when our login accesses an E1 database they will be doing so using the Guest account.  Since full permissions to all tables is granted to PUBLIC, and since Guest is a member of PUBLIC our login will have full and complete permissions on every single table.  Try it out - create a login on your SQL Server and do not grant it access to any databases.  Then login as that user and run a SELECT statement against one of your EnterpriseOne tables.....or an UPDATE, INSERT or DELETE statement if you like.

It is highly recommended that you take the following steps even if you followed the earlier suggestion to remove permissions from PUBLIC:  If you are on SQL 2000 remove the Guest user from each database.  If you are using SQL2005 or later REVOKE the Guest user's CONNECT permission.  Starting in SQL 2005 the guest user cannot be dropped, but guest user can be disabled by revoking its CONNECT permission.

You can check Guest's CONNECT permission in each database with this code.

--SQL Script begin
declare @cmd1 varchar(500)
set @cmd1 = 'PRINT ''?''; USE [?];
IF (SELECT state_desc FROM sys.database_permissions where type = ''CO'' and grantee_principal_id = user_id( ''guest'' ))
= ''GRANT'' SELECT DB_NAME() as ''Database'', permission_name, state_desc FROM sys.database_permissions
where grantee_principal_id = user_id( ''guest'' )'
exec sp_MSforeachdb @command1=@cmd1
--SQL Script end

If you see CONNECT and GRANT in the results, you are exposed and should take the steps detailed above to address the issue.


Conclusions

The default EnterpriseOne install creates some pretty dangerous security exposures: 

• Well known default login passwords
• Complete permissions granted on tables by default to all users in an E1 database
• CREATE TABLE permissions granted at the database level
• Guest user with complete permissions on E1 databases

Oracle (and JD Edwards before them) attempt to evade responsibility by claiming, somewhat disingenuously, that the database configuration is a customer responsibility and not theirs.  However, given the complexity of the product and the fact that the sale force pushes the whole "Running E1 on SQL Server is a no maintenance, no DBA needed affair", Oracle is duty-bound to ensure that their customers are at least aware of the dangers.  I'd prefer they fix the problem but absent that, they should highlight in the documentation the exact steps recommended to address the issues.

Thanks for sticking with me though this lengthy series.