SQL Server - Members of db_datareader not in db_denydatawriter

In an earlier series of articles on EnterpriseOne SQL Server default security we discussed the issue of Oracle granting permissions to PUBLIC that created a security exposure when placing users in the SQL Server db_datareader database role.  I advised you to place any user of an E1 database that is in the db_datareader role in the db_denydatawriter role also.  However, I didn't leave you with any quick way to determine what users are in the db_datareader role.  The script below quickly identifies users that are members of db_datareader but not also members of db_denydatawriter.  The script will only return results from databases where this situation actually occurs.

--SQL Script begin
EXEC sp_MSforeachdb 'USE [?]; IF
(SELECT COUNT (*) from (SELECT sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16390
EXCEPT
SELECT sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16393) as a) > 0
SELECT DB_NAME() as ''Database'', sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16390
EXCEPT
SELECT DB_NAME() as ''Database'', sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16393'
--SQL Script end

Place the users identified by the script in the db_denydatawriter role to address the security exposure.

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.

Default SQL Permissions in EnterpriseOne Part 2

In Default SQL Permissions in EnterpriseOne Part 1 we discussed the problem with granting ad-hoc SQL logins access to EnterpriseOne databases and how doing so gave them rights to modify data. In part 2 we are going to demonstrate this effect in hopes of creating a better understanding of exactly what is going on. Afterward I will offer some suggestions to work around and/or remedy the situation.


Demonstration

First, create a SQL Server login named Darryl, grant it access to a database (I will use SpotlightPlaybackDatabase but feel free to use one of your own) but do not place it in any server or database roles. Once this is done we will query that login's permissions.































The SQL Server system function sys.fn_my_permissions "Returns a list of the permissions effectively granted to the principal on a securable." Since simply calling sys.fn_my_permissions returns the calling user's (the one executing the command) permissions we will combine the function with the EXECUTE AS clause to determine permissions for our newly created user.

The script will look something like this:

--SQL Script begin
use databasename
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('schemaname.tablename', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

Where databasename is the database to be tested and schemaname.tablename is a random table object you choose in the database.

Let's see what our new user's permissions are at the object level in the non-E1 database with our user as a member of only PUBLIC.

--SQL Script begin
use SpotlightPlaybackDatabase
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('dbo.spotlight_playback_alarms', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

We get the following:

entity_name	permission_name

At the object level, our user has no permissions. None have been granted to the user and none have been granted via the only role that they are a member of - PUBLIC.

Now let's add the db_datareader role to our user in the SpotlightPlaybackDatabase.

And run our script to determine permissions.

--SQL Script begin
use SpotlightPlaybackDatabase
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('dbo.spotlight_playback_alarms', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

This returns the following:

entity_name	permission_name
dbo.spotlight_playback_alarms	SELECT

You can see that the user has SELECT rights, and only SELECT rights, to the table, and in fact all other tables in the database. This is exactly what we want to see for a read-only user in a database. Let's see what it looks like for an EnterpriseOne database.

First we add the user to the JDE_CRP database and assign it no roles. It will be a member of the database's PUBLIC role by default.

Now run our script to determine permissions.

--SQL Script begin
use JDE_CRP
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('CRPDTA.F0101', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

Here's the permissions for our user who has simply been given access to the database:

entity_name	permission_name
CRPDTA.F0101	SELECT
CRPDTA.F0101	UPDATE
CRPDTA.F0101	REFERENCES
CRPDTA.F0101	INSERT
CRPDTA.F0101	DELETE

The user, who has not been added to any roles (except PUBLIC by default), has SELECT, UPDATE, INSERT.....wait, what? UPDATE, INSERT, DELETE? Yep, a user added to an EnterpriseOne database has those permissions without them being explicitly given to the user. This happens because, as mentioned in part 1, the install script DB_SQLSRVR_INSTALL.sql grants those permissions to the PUBLIC role in EnterpriseOne databases and all users in a database inherit permissions of any and all roles they are members of.

Placing our user in the db_datareader gives no additional permissions since they already have the permissions granted by that role. It is only when we start denying rights that we address the security hole.

Let's place our user in the db_denydatawriter role. Microsoft says "Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database." This sounds exactly like what we want for our ad-hoc SQL users.

We'll run our script again:

--SQL Script begin
use JDE_CRP
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('CRPDTA.F0101', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

The results look more like what we want for our read-only users:

entity_name	permission_name
CRPDTA.F0101	SELECT
CRPDTA.F0101	REFERENCES

It took explicitly denying 'write' rights to our user in the EnterpriseOne database to override the permissions granted to the PUBLIC database role and inherited by our user. Having to do so is a bit out of the norm but knowing this fact will allow you to deal with the problem.

Update (11/1/2010): I have created a script to help identify users that are members of db_datareader but not db_denydatawriter. You can find it here: http://jeffstevenson.karamazovgroup.com/2010/11/sql-server-members-of-dbdatareader-not.html.


Workarounds and Suggestions

We can clearly see that following standard SQL user provisioning procedures for granting read-only access leaves us with a security exposure in EnterpriseOne databases due to a non-standard method of granting permissions in the database by the E1 install scripts. Now that we are aware of this we can address it is several ways.

The methods to deal with the problem fall into two categories: Continuing with PUBLIC permissions as the install configures and overriding them or removing the PUBLIC permissions and granting them only to the JD Edwards system/proxy user(s).

If you choose to continue with the PUBLIC role permissions as they are set by the install it is imperative that you override the INSERT, UPDATE and DELETE permissions for any user granted access to an E1 database. Do this by placing them in the db_denydatawriter database role. It is not technically necessary to place the database user in the db_datareader role but doing so will make permissions easier to interpret. I want to note that this method is not perfect since the dBA has to be sure to take this extra step for every new user in an EnterpriseOne database. It may be a good idea to write a SQL Agent job that periodically checks for users in E1 databases that are not in db_denydatawriter and sends an email alert when the condition is found.

The other choice is to remove the permissions assigned to PUBLIC and assign them instead to a role in which you will place the EnterpriseOne system/proxy user(s). Removing the permissions from the PUBLIC role will mean that logins added to the database, and thus members of PUBLIC, will not have permissions beyond what is expected from that role. Once the permissions are granted to the newly created database role, those same permissions can be revoked for the PUBLIC role.

Oracle has a paper detailing the steps to accomplish this: MS SQL Server 2005 Public Shutdown for JD Edwards EnterpriseOne. If you choose this method it is highly recommended you follow Oracle's instructions. As with our other method, there are minor issues: any table generated in OMW will be created in the database with those pesky full permissions for the PUBLIC role. You will need to remember to revoke those permissions if you generate a table using OMW.

So we have two workarounds, each with their own issues that add to the already heavy administrative burden of managing a JD Edwards E1 system. I'd like to offer some permanent fixes. They involve some code change and should be addressed by Oracle.

1- Have the installation ask for the SQL login(s) that is/are to be used as the system/proxy user(s) and assign the appropriate permissions only to that/those logins. Alternately, have the install create a database role and assign the necessary permissions to the role. If the system/proxy user is changed or one is added E1 should simply copy those permissions to that SQL login or role.

2- If permissions have been removed from PUBLIC have OMW not grant them on table generation.

I welcome any other suggestions. Feel free to leave them in the comments.

Bottom line: Unless you take additional steps, any user for which you do not explicitly deny write and delete permissions will have those rights in EnterpriseOne databases. Be careful out there.


In part 3 of the Default SQL Permissions in EnterpriseOne series we'll discuss database level permissions granted during the install and also the implications of having the GUEST user present in an EnterpriseOne database.

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.

Bypassing EnterpriseOne Menu-Based Security in the Web Client

A certain philosophy exists within the JD Edwards ecosphere where some believe that instead of doing the hard work to properly secure an EnterpriseOne system they can instead configure the menu in such as way as to "hide" applications from users.  This is perhaps based on a couple of faulty premises - that menu configuration is easier than security configuration and that users will not be able to access applications that are not present on their menu.

This concept is commonly called Security by Obscurity and is in violation of Kerckhoffs' Principle, which holds that a system should be secure because of its design, not because the design is unknown to an adversary.

I can state without a doubt that a properly executed security design is not only easier to configure but actually makes future changes easier; the system is more ductile with a good security design.  A role-based menu structure without underlying security actually becomes more difficult to administer as business processes change and subsequent modifications are needed to the menus.  The end result is often either a menu redesign or a proper security implementation being done.  The arguments for Security by Obscurity fail while the shortcomings remain.

This article will demonstrate how to bypass what was once termed "Menu-Based Security" in the E1 web client using one method that is readily available to E1 end users.


This method of bypassing a menu obfuscation security model uses the Send Shortcut method built-in to the E1 web client.

In this scenario there are two users - one with Address Book Revisions (P01012) on their menu and one without Address Book Revisions on their menu.  This would supposedly keep the second user from executing P01012 by giving them nowhere to click to execute the application.  A key point in this scenario is that both users have underlying application security permission to execute P01012.

To begin the bypass, the supposed "privileged" user opens the application (P01012 in this case) and selects Tools/Send Shortcut.


















This will bring up the Send Shortcut E1 application and allow the "privileged" user to specify to whom they wish to send the shortcut, in this case a supposed (by virtue of the lack of a menu item) "non-privileged" user, CNCTEST.


















The "non-privileged" user, CNCTEST logs onto their web client and opens Work With Work Center, finds the E1 message in their Personal In Basket and chooses the message content in the dropdown on the right.

Doing this reveals a link to Work With Addresses attached to the message.

Clicking this links opens the Work With Addresses application in the web client, despite there being no menu item for Address Book on the "non-privileged" user's menu.

















While arguments can be made to support the design and implementation of an EnterpriseOne system that relies on Menu Filtering to "secure" the business, I doubt that the lack of security inherent in this method can compensate for the possible gains.  A well-planned combination of all types of security plus a menu design based on ease of use, having all the applications each user needs and only the applications a user needs in a clutter-free, intuitive layout will provide the security and usability that you desire.

Update (4/2/2010): A related post "Securing/filtering Menu Task Views" can be found over on Boris Goldenberg's site.

Data Selection Security in EnterpriseOne

One of newest types of security for JD Edwards EnterpriseOne is Data Selection security.  Using Data Selection security CNC administrators can secure users from modifying, adding, deleting, and viewing the data selection for batch applications or specific versions of batch applications.

Data Selection security was made available with Tools Release 8.98 Update 1 (8.98.1.0) and has a minimum application release level requirement of 8.12.  The functionality also requires that Tools Baseline ESU JK17733 or newer be applied.

Data Selection is already disallowed for versions secured with an "old style" version security value of 1 where the Last Modified User is the only one who can change the version. Typically, the XJDE and ZJDE versions are delivered with this security value.  However, for custom versions that are not secured in this manner the new Data Selection security can be used to gain fine-grained control over the actions that are to be allowed for Data Selection.

Some important points to keep in mind when considering Data Selection Security:

• Data Selection security applies to data selection during submission of a batch application or report.
• Data selection security is enforced only for end users submitting batch applications or reports from a web client.

Enabling
 
Data Selection security is enabled when the application release is at 8.12 or higher, Tools Release 8.98.1.0 has been installed and Tools Baseline ESU JK17733 or newer has been applied.  Once Data Selection security is enabled you will see a new Hyper Exit button in Work With User/Role Security (P00950).

If you do not see the button, chances are that you have not met one or more of the requirements mentioned above.  The form used for Data Selection security is the same one formerly used solely for Processing Option Security.



Setup and Utilization

There are four different Data Selection security options - Prompt for Data Selection, Full Access Data Selection, Modify Data Selection and Add Data Selection.

Prompt for Data Selection is the most restrictive, disallowing the user from even seeing the Data Selection. 

Full Access Data Selection prevents a user from deleting existing Data Selection rows.

Modify Data Selection prevents expanding or changing existing criteria. 

Add Data Selection prevents a user from adding new Data Selection criteria.

  
 Prompt for Data Selection

When only the Prompt for Data Selection option is selected the user will still be able to select the "Data Selection" check box but will receive the following error:

Full Access Data Selection

The next most restrictive option is Full Access Data Selection.  This option prevents a user from having a full set of the editing capabilities on the data selection screen.


When only the Full Access Data Selection option is selected the user will be able to modify values for existing data selection rows and add data selection rows with AND operator but not OR operator.  The user will not be able to delete existing rows.

Enabling the Full Access Data Selection option allows the use of two more options that can be used to further restrict Data Selection - Modify Data Selection and Add Data Selection.  The Full Access Data Selection, Modify Data Selection and Add Data Selection options can be used in any combination to provide the desired level of Data Selection security.

 Modify Data Selection

When the Full Access Data Selection and Modify Data Selection options are selected the user will not be able to modify values for existing data selection rows but will be able to add data selection rows with AND operator but not OR operator.  The user will not be able to delete existing rows.

 Add Data Selection

When the Full Access Data Selection and Add Data Selection options are selected the user will be able to modify values for existing data selection rows but will not be able to add data selection rows. The user will not be able to delete existing rows.

 Modify Data Selection plus Add Data Selection

When the Full Access Data Selection, Modify Data Selection and Add Data Selection options are selected the user will not be able to modify values for existing data selection rows and will not be able to add data selection rows. The user will not be able to delete existing rows.  This is essentially a read-only configuration for Data Selection.

Options Summary

Prompt for Data Selection

•     Cannot see or change data selection

Full Access Data Selection

•     Can modify values for existing data selection rows
•     Can add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows

Full Access Data Selection + Modify Data Selection

•     Cannot modify values for existing data selection rows
•     Can add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows

Full Access Data Selection + Add Data Selection

•     Can modify values for existing data selection rows
•     Cannot add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows

Full Access Data Selection + Modify Data Selection + Add Data Selection

•     Cannot modify values for existing data selection rows
•     Cannot add data selection rows with AND operator but not OR operator
•     Cannot delete existing rows
•     Read-only

Summary

Data Selection security is another security type to be used by CNC administrators or consultants to lock down batch versions data selection during submission in the EnterpriseOne web client.  It should be implemented as a part of a larger effort to secure batch processing and in such a manner as to maintain consistency with your organization's security practices and methods.

More information can be found in Oracle Document ID # 814174.1 JD Edwards EnterpriseOne Tools 8.98 Update 1 Batch Application Data Selection Security

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.