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.