Default SQL Permissions in EnterpriseOne Part 1

So you've gotten EnterpriseOne installed or have had it installed for a long time and you've finally changed the default E1 SQL user (CRPDTA, DD812, PRODCTL, etc.) passwords in SQL Server.  If you haven't at least done that you should stop right here and go change those passwords using the article E1: DB: How to Change the Database Object Owner Passwords for EnterpriseOne Databases [ID 629822.1].  If you don't do this, most everyone in the JD Edwards ecosystem knows your passwords.

After this you think you're locked down pretty good from a SQL perspective so you start adding ad-hoc SQL logins - developers that need to look at data, the IT manager who insists he needs to use SQL tools, the accountant who really, really needs a SQL login to use Access to link tables.  Standard practice would be to create the login, grant the login access to the desired database and place them in that database's db_datareader role, granting them read-only rights to the database.  These actions normally produce the desired read-only user in the database. In EnterpriseOne databases, however, it's not that simple.


SQL Permissions

I'm not going to get into a treatise on SQL security concepts but I will briefly cover the topic as it is relevant to our problem:  Permissions in SQL Server are granted, accumulate, are inherited and are overridden by denial.  A database user will accumulate the permissions from server role memberships, database role memberships and permissions explicitly granted to the user.  A DENY at any level overrides any GRANTS.  These permissions can be at the server, database or object level.  The illustration here shows the basics of SQL Server permissions.

Let's move on to a couple of scenarios to illustrate the main point of this series of articles.


Scenarios

A SQL login is granted access to a database and is automatically placed in that database's PUBLIC role.  The user cannot be removed from the PUBLIC role.  The user is then also placed in that database's db_datareader role, which allows them the permissions granted to db_datareader (Run a SELECT statement against any table or view in the database.)  Their effective permissions at this point are a sum of those granted by virtue of their membership in PUBLIC (None) plus those received from membership in db_datareader (SELECT) plus any granted to the user explicitly (None).  This scenario would produce a database user capable of executing SELECT statements on any table in the database.

Another scenario: A SQL login is granted access to an EnterpriseOne database and is automatically placed in the database's PUBLIC role.  The user cannot be removed from the PUBLIC role.  The user is then also placed in that database's db_datareader role, which allows them the permissions granted to db_datareader (Run a SELECT statement against any table or view in the database.)   In this database however, the PUBLIC role has been granted SELECT, INSERT, UPDATE, DELETE, REFERENCES on every table in the database.  The user's effective permissions  are a sum of those granted by virtue of their membership in PUBLIC (SELECT, INSERT, UPDATE, DELETE, REFERENCES) plus those received from membership in db_datareader (SELECT) plus any granted to the user explicitly (None).   In this scenario, the login that you just granted access to JDE_PRODUCTION, in what you thought was a read-only role, in fact has permissions to also INSERT, UPDATE and DELETE data in all tables in the database.  Not exactly what you had in mind is it?

If the database's PUBLIC role has been granted permissions (as it has in EnterpriseOne databases) then the user will inherit those permissions, in addition to any permissions that may have been explicitly granted to the user, either at the database or object level, or by virtue of the user's membership in another database role such as db_datareader.


Background

Since Oracle has no way of knowing exactly which SQL login is going to be used as your system/proxy user, the EnterpriseOne install cannot easily determine to whom the required permissions should be assigned.  Oracle (and JD Edwards before them) takes the easy way out and assigns the permission to the SQL database role PUBLIC.  The dirty deed is done in the DB_SQLSRVR_INSTALL.sql script:

select @objNew = @szNewOwner + '.' + @tablename
select @szGrantStr = 'GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON ' + @objNew + ' TO public'
exec sp_executesql @szGrantStr

You can clearly see where the code grants REFERENCES, SELECT, INSERT, DELETE, UPDATE on each table to PUBLIC. The result is that anyone who is a member of a database's PUBLIC role (everybody who has access to that database) inherits those permissions.  Unless the dBA explicitly denies the undesired permissions, you have a huge security hole.


In part 2 of the series I'll demonstrate the permissions problem and offer several suggestions on how to  deal with the issue and secure your EnterpriseOne databases.