Auto Open PDF's in EnterpriseOne Web Client

There has always been an issue opening PDF files from the Work with Submitted Jobs list in the EnterpriseOne web client - the end user is prompted to determine what they wish to do with the file when, chance are very good that they want to open it.  The problem is related to how the PDF content is delivered to the browser by the E1 code and how the browser handles that content.

Full disclosure: I completely stole this issue from a JDEList thread and the solution from user ice_cube210 (Tonio Thomas).


Problem Presentation

The problem manifests as a prompt in the browser every time a user opens a PDF from Work with Submitted Jobs (WSJ) asking what they want to do with the file:

Internet Explorer 8

Firefox 6

This gets old really fast.  Users generally want to open the file.


Background

The root of the problem, as mentioned above, is how a PDF is delivered to the browser.  PDF's can be sent to a web browser as either a file/attachment or inline as defined by the "content-disposition" HTTP header.  When a web server sends a PDF to a browser the content-disposition header is set to either "attachment" or "inline" or possibly not set at all.  The value of the content-disposition header tells the browser how to handle the content as defined in the RFC's rfc2183-in-http and rfc2183.  If the header is content-disposition: attachment; the browser will enter the "Save as..." dialog.  Use of the content-disposition: inline; header or the absence of a content-disposition HTTP header will cause the browser to use default processing to handle the content.  From this point it is up to the browser to determine how to process the PDF.  For Internet Explorer 8 default processing means opening the PDF in the browser's embedded PDF viewer and for Firefox 6, a separate PDF viewer (although this behavior can be changed).


EnterpriseOne Behavior

By default, when a user selects "View PDF" in WSJ EnterpriseOne sends the PDF as a file, using the  content-disposition: attachment; header.  This causes the browser to enter the "Save as..." dialog as stipulated by rfc2183-in-http. The end user must then choose to either open or save the file.  Choosing Firefox's "Do this automatically for files like this from now on" seems to be forever broken so is of no help.

For the really geeky here's what the HTTP headers look like for the default E1 behavior:

HTTP/1.1 200 OK
Date: Wed, 21 Sep 2011 18:19:00 GMT
Server: IBM_HTTP_Server
Content-Disposition: attachment; filename =R0006P_XJDE0001_428652_PDF.pdf
Content-Length: 762280
Keep-Alive: timeout=10, max=99
Connection: Keep-Alive
Content-Type: application/pdf
Content-Language: en-US


Note the content-disposition header, with filename parameter.


Workaround/Solution

From Tools Release 8.98 on there has been an option to present PDF's in a manner that will automatically open in the user's browser.  The setting is the "UBEContentDisposition" parameter in the [OWWEB] section of the JAS.INI and can be easily changed in Server Manager in the Web Runtime configuration for the JAS server as described here.

At first I thought Oracle had used the content-disposition: inline; header but examination of the HTTP headers shows that once the setting is changed, the content-disposition header is simply removed.

HTTP/1.1 200 OK
Date: Wed, 21 Sep 2011 18:20:44 GMT
Server: IBM_HTTP_Server
Content-Length: 762280
Keep-Alive: timeout=10, max=88
Connection: Keep-Alive
Content-Type: application/pdf
Content-Language: en-US


I suppose that will work.  The RFC allows for no content-disposition header.   The absence of the header will cause the browser to use the default method of presentation and we get the desired behavior - automatically opening PDF's. I'd much rather see the content-disposition: inline; header and have Oracle also use the Disposition Parameter: 'Filename' to give us a real filename to use when saving the file from Acrobat Reader (see below).


Issues with Workaround

An issue with the fix may preclude some customers from using this workaround - when UBEContentDisposition is set to True (the default) and the PDF is sent as an attachment, the UBE name (R0006P_XJDE0001_429031_PDF.pdf) is sent to the browser/Acrobat Viewer and allows one to save the file with the original EnterpriseOne UBE name:

Original UBE Name

When using the workaround and setting UBEContentDisposition to false, a generic PDF name (com.jdedwards.jas.pdf) is passed in:

Generic Name

This causes a problem when one wishes to save the PDF using the original UBE name for reference.  You can always name the PDF whatever you like but it is darn convenient to have the report name passed in.  Whether the naming issue is important enough to keep you from changing how PDF's are handled in the browser is an internal organizational issue.  We should be able to pass in real UBE names using the content-disposition: inline; header with the 'Filename' parameter but the smart folks at Oracle probably know something I don't.  I've suggested it and we'll see.

This is a very handy setting to change.  Anything that makes end users' work easier and more efficient is worth doing.

EnterpriseOne Temporary Print Files on Enterprise Server

Your EnterpriseOne system may be creating hundreds of thousands of files on your JD Edwards Enterprise Server in a location that is not readily apparent and not cleaning them up...ever.

During a UBE run that involves a version that has Print Immediate turned on at the version level (introduced in TR 8.96) a 'temporary' print definition file (either PostScript or PCL depending on how you defined the associated printer) is created in the location specified in the netTemporaryDir= directive in the [JDENET] stanza in the Enterprise Server jde.ini.

The purpose of the file is to allow the Print Immediate printing of the generated PDF using the printer defined in P98616|W98616O - Work With Default Printers.  The problem is that there is no process for cleanup of the file after it has been utilized.  Deleting the job from the submitted jobs list removes the job record and the PDF but leaves the print definition file.  The R9861101X/R9861102 UBE's that purge submitted jobs doesn't remove the print definition files either.  What you end up with is a very large number of files that are not needed after the initial Print Immediate action.

Oracle's suggestion is to set KeepLogs=0 in the jde.ini but that has the effect of not keeping the UBE logs after the job completes, which is sometimes useful.  It has been suggested to development that either the 'temporary' print definition file be deleted after the file is used (preferred) or the files be cleaned up as a part of deleting a job from the submitted jobs list and/or a part of the submitted jobs purge UBE process.

Important Update (9/8/2011): Changing KeepLogs to 0 (In Server Manager for the Enterprise Server in question - Logging/Error and Debug Logging/Keep UBE Logs = Remove Logs Once Printed) and UBESaveLogfile to 1  (In Server Manager for the Enterprise Server in question - Batch Processing/Save Empty Debug Log = Keep Empty Debug Log) in the Enterprise Server jde.ini will cause EnterpriseOne to retain the UBE logs while removing the print definition file.  I'd still prefer that the software cleanup after itself instead of hacking the UBE logs code but this appears to solve the problem.  Now if only Oracle will make this the default setting.

In the meantime, feel free to go to C:\JDE_HOME\targets\Enterprise_Server_enterpriseservername\temp on your Enterprise Server and remove those files.

EnterpriseOne JAS and JDEROOT Log Files on Enterprise Server

It is well known that JD Edwards EnterpriseOne splatters logs and temporary files all over the place.  As the application has gotten more complex with added components such as Web servers, Java Application servers, Server Manager, etc. the sheer number of log files generated in wildly varying locations has notably increased and keeping track of and managing all these logs files is a chore.

Here's an example:

Go to your Enterprise Server and open the C:\JDEdwards\E900\DDP\system\bin32 directory, the one that is supposed to contain only the E1 binary files.

Scroll down to the 'J's" and find the hundreds or thousands of jas_nnnn_yyyymmdd.log files (ex: jas_4844_20101114.log).  Delete them.

Now find the jderoot_nnn_yyyymmdd.log files (ex: jderoot_316_20101114.log)  Delete them.

These files are vestigial from some long-forgotten process and are not needed nor useful.  Prize goes to the one who finds the largest number of jas and jderoot log files on their Enterprise Server.  My record is 14,634.

To ensure the log files no longer appear do the following:

Open the jdelog.properties file in the C:\JDEdwards\E900\DDP\system\classes directory on the Enterprise Server and comment the following sections:

Figure 1

Once this is done you may have to restart the E1 service to ensure the jdelog.properties file is read.

You could try to remove the log files in the jdelog.properties Logging section for the Enterprise Server in Server Manager but you'll get the error "The log configuration named 'E1LOG' is defined by the system and may not be removed." when you try to delete the E1LOG log file configuration.

Oracle has been made aware of the issue and they are going to remove this logging in future releases but until then it behooves you to delete the log files and prevent your system from creating them again.

So there's one set of log/temporary files gone.  More to follow.

EnterpriseOne 9.0 SQL Server Collation Issues

Starting in EnterpriseOne 9.0 the platform pack delivers pre-built databases that are simply attached and are set to a collation of Latin1_General_CI_AS_WS when created at Oracle.  This collation is different than the default SQL Server collation and different than the old (pre-9.0) collations.  Because of this, you will usually find a mix of collations on an E1 install, particularly upgrades.  The collation for E1 databases will likely be different than the collation for tempdb and master.  This can cause problems in an EnterpriseOne system and could result in "Cannot resolve collation conflict" or "Cannot resolve the collation conflict" errors appearing in logs during upgrades or JOIN operations.

You can determine the collation of your system's databases with this code:

--SQL Script begin
SELECT name, collation_name FROM sys.databases
--SQL Script end

If you are on version 9.0 or above you will likely have different collations for your E1 databases and the SQL system databases.  It is possible that you may see different collations between E1 databases if they are upgraded databases.


Background

The reason for E1 databases being delivered with odd collations was a width sensitivity issue for Double-Byte languages.  The solution was to create the EnterpriseOne databases with a width-sensitive collation - Latin1_General_CI_AS_WS.  This solved the width sensitivity problem but  has caused a large amount of grief for customers since it does not match SQL Server's default collation of SQL_Latin1_General_CP1_CI_AS on US English Windows servers.  (Heck, Latin1_General_CI_AS_WS doesn't match any language's default collation, guaranteeing collation conflicts.)  Oracle's recommendation is to install SQL Server with a non-default collation that will match the incoming E1 databases which keep their collation when attached.  Installing SQL Server with a collation that matches the incoming E1 databases will cause databases created on that SQL Server (master, tempdb, etc.) to take the SQL Server collation that will then match the E1-delivered collation and eliminate collation conflicts with JOINS.

To determine your SQL Server collation:

--SQL Script begin
SELECT SERVERPROPERTY('Collation') AS 'Collation'
--SQL Script end


Possible Solutions

If one fails to install SQL Server with the non-default collation (which is highly likely) Oracle recommends changing the collation of the existing databases.Changing a database's collation is no simple matter however and should be well thought out and planned.  Oracle recommends using R98403E but there are several methods and scripts online that can help.

Here is a very informative page that includes links to scripts that can help you change database collations if you choose to do so: http://www.sqlserverclub.com/articles/understanding-sql-server-collation-sequences.aspx

Here is an MSDN blog telling you not to: http://blogs.msdn.com/b/qingsongyao/archive/2011/04/04/do-not-alter-database-collation-in-your-server.aspx


Workarounds

If you are seeing errors in jde logs indicating a collation conflict you may well have to change collation.  If you are having problems with your own query containing a JOIN statement that joins databases with a different collations, you may wish to write the query and specify the collation. Ex: JOIN JDE900.OL900.F9860 on (sys.objects.name = JDE900.OL900.F9860.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS).

Here's an example where I utilize this method: http://jeffstevenson.karamazovgroup.com/2010/07/how-large-are-my-tables.html.


I find that the collation issue in E1 to be a general pain but apparently it is necessary to deliver databases that handle the width sensitivity issue.  I personally think the platform pack code could be modified to determine if the double-byte problem is present before splatting databases onto a server that create collation conflicts.  I welcome any feedback on this issue.

More Information

Here's Oracle's explanation why:  https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1267442.1

Another informative Oracle article that also discusses what happens to upgrade databases' collation:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1271189.1

To get a list of all SQL Server collations (very useful):

--SQL Script begin
SELECT * FROM fn_helpcollations()
--SQL Script end

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.

Restarting the EnterpriseOne Queue Kernel

There is a way to restart just the UBE queue in EnterpriseOne. If you are faced with a situation where submitted jobs are not processing and the jobs are sitting in a waiting status you can get the queue restarted without bringing down the entire E1 system, allowing interactive users to continue their work.

In older (prior to 8.9) releases the queue was a separate Windows service called JDE Update 4 B733 Queue or something similar. This service could be stopped and started independently of what was then called JDE Update 4 B733 Network or the "Network" service. This made it easy to restart the queues without bothering the main service. In the 8.9 release however, the queue service was redesigned and turned into a kernel under the main E1 service, greatly complicating the targeted restart of the queues but not making it impossible. Here's how:


Go to the JDEdwards\E812\DDP\log directory and search for files containing the text QUEUE KERNEL. This will give you the queue kernel PID in the file name (i.e. jde_9188.log). You will (should) have only one queue kernel. Go to Windows Task Manager and View/Select Columns and add the PID column. You can kill that PID and the next time a job is submitted the Queue kernel will be restarted.



There are also ways to move jobs from one queue to another but that's a post for another day.

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

UBE Introspection & Dynamic UBE Logging in EnterpriseOne

Troubleshooting a UBE on the enterprise server in E1 has always been difficult. Even more difficult when the users are on the web client.  Debugging without causing some sort of interruption for other web users was nearly impossible.  Gathering certain information about how a job was submitted, such as processing options and data selection was also difficult and determining whether a long-running UBE was actively processing was a hit-or-miss proposition.

In Tools Release 8.98 things got easier with UBE Introspection, Dynamic UBE Logging and UBE Audit Data or what Oracle calls collectively "Utilities for Batch Processing". Now CNC administrators (and anyone else not secured out of P986114A) have the ability to monitor a processing UBE, turn debugging on and off at will and to perform what Denver calls Introspection - "The ability to display statistics regarding the state of a batch job while it is running". 



Dynamic UBE Logging

The benefits of Dynamic UBE Logging are obvious and will be welcomed by anyone who has ever tried to troubleshoot a UBE running on the enterprise server, particularly in production.  We now have the ability to begin debugging a UBE after submission, turn debugging off and on as needed and view the logs from one location.

UBE Introspection
 
The UBE Introspection feature will be useful for developers/report writers as the information it returns gives real-time insight into what the UBE is doing with regard to section, events, business function and line/page number.  Using this tool, one can tell if the UBE is processing and if it's not, where it is hanging.  A CNC administrator could conceivably use introspection to help determine whether a job is hung or still actively processing and whether to kill it or let it run.

UBE Audit Data

The Audit Data portion of  the Utilities for Batch Processing seems to be the least useful item of the three.  The primary benefit appears to be the revelation of  the UBE run time and the stop time.  The start time could already  be found in when one double-clicks a WSJ record (P986110B|W986110BC - Job Maintenance) but now we get the stop time (and the run time) and not just the last activity time.  Of interest however, may be the additional audit data that can be viewed for a UBE  - processing option values, data selection, data sequencing, report interconnect information and the SQL statement as the UBE was submitted.  For all audit data, the UBE need not be actively processing as it must be in order to view introspection data.  And remember - the additional audit data (po's, data selection, SQL Statement, etc.) is from the initial submission of the job.

To clarify two points: the audit data is available even after the UBE completes and the additional audit data contains information about the UBE as it was submitted.


Enabling

The new Utilities for Batch Processing functionality requires Tools Release 8.98, a minimum application release level of 8.11 SP1 and an ESU corresponding to your apps release level.

As of this article, the ESU numbers are:

• JK17733 for 8.12
  
• JL11723  for 9.0
• JJ16940 for 8.11 SP1
  

The following objects are included in the ESU's to enable the new batch processing functionality:

• B986114 BatchUtilitiesFunctions
• D986114A TurnOnOffLogging
• D986114B TurnOnOffIntrospection
• D986114C RetrieveIntrospectionData
• D986114D Determine If Auditing Service Pack is Present
• F986114 Auditing Information Data Repository
• F986114A Audit Repository Detail
• N986114A Determine If Auditing Service Pack is Present
• P986110B Job Control Master
• P986114A Submitted Job Execution Detail
• V986114A Auditing Information Data Repository
• V986114Z Server Job Audit Repository Detail

Special Instructions  must be executed to complete the enabling of the functionality.  Briefly, the Special Instructions require that you:

• Generate F986114 and F986114A in all server maps
• Create OCM mappings for F986114 and F986114A similar to those existing in System and Server Map(s) for F986110 (according to Denver, this item can be safely ignored)

  
Utilizing

Using the enhanced Batch Utilities couldn't be easier - in the Submitted Jobs form (P986110B\W986110BA), select a processing job and press the Execution Detail row exit button.

Once in the Submitted Job Execution Detail form, you can turn logging and introspection on and off, easily view the logs, and view Audit Detail by selecting the appropriate tab.

The commands in the web client are in the equivalent locations.  I'll assume that if you are smart enough to get this far, you can figure the web client out yourself.


Maintaining

Oracle has done us a great favor by not forcing us to add yet another item to our purge and maintain lists when dealing with Batch Utilities.  The entire functionality is contained within the aforementioned F986114/F986114A tables and those tables are purged when one runs the UBE to purge the Submitted Jobs (R9861101).

I would recommend keeping an eye on the PrintQueue directory as the debug logs may fill this location faster than normal.

Also, two additional IPC resources are needed per UBE so adjust your enterprise server JDE.INI accordingly.


Summary

In Tools Release 8.98 Denver has provided us with another gadget for our troubleshooting toolbox.  We should be thankful that something we have been requesting for a long time has finally been given to us - the ability to gain insight into running UBE's and the ability to debug UBE's without interrupting other users.

EnterpriseOne User Specific Dynamic Logging with WebSphere Network Deployment

Denver's Server Manager product is the new(er) method for managing all aspects of EnterpriseOne servers - monitoring, configuration, tuning and logging, etc. CNC administrators can now modify settings, see what's happening on their system and do logging all in one interface . In particular the logging enhancements are worthy of mention. I will show that while Server Manager is a great tool, Oracle still has a ways to go for full integration with WebSphere in general and WebSphere Network Deployment specifically.

A new function for user specific, dynamic logging allows the administrator to:

- Log an individual JAS session without impacting other users.

- Begin and end this logging without having to stop and start the entire JAS instance.

The concept is great and I applaud the developers for this functionality. However, as with some other functions in Server Manager there are issues when one is utilizing WebSphere Network Deployment.

Network Deployment uses a centralized Deployment Manager node that controls a cell into which individual remote WebSphere nodes are federated. Any changes to configuration files are controlled by the Deployment Manager and must be propagated to the remote nodes. This appears to be where the problem starts with user specific dynamic logging.

Let's take a look at the steps needed to reproduce the situation and what we can do to work around the issue until Oracle fixes it:


Enable user specific dynamic logging by selecting Create New User Specific Log Configuration, entering the user name for the user for which you wish to enable logging.
















Modify for Verbose logging and Threads and apply.



This action will write the logging information to the jdelog.properties file in the target's config directory but will not write it to the actual jdelog.properties file on the WebSphere node.





User specific logging is not yet actually occurring even though no error message in Server Manager will tell you so and, according to the documentation, the logging should begin immediately.

What's happening is that the jdelog.properties file on the remote node is not getting updated and therefore the node has no information on the user specific logging.

We must use the synchronize function in Server Manager to propagate the changes and make user specific logging work in a Network Deployment environment since, among other things, the Synchronize Node command will copy the configuration files (jdelog.properties in this case) to the remote node(s).

If you select the HTML managed instance for which you set up the logging you will be prompted to synchronize the configuration.



This is your first indication that the local and the remote configuration files are not in agreement.

Go ahead and synchronize the configuration but be warned that as of right now, selecting the Synchronize Configuration button will expire all users sessions for that JAS instance without warning from Server Manager. (Note: Apparently fixed in TR 9.1.  Synchronize Configuration no longer restarts the instance unexpectedly but one must not manually restart the instance to see the changes.)

After synchronization completes you can see that the jdelog.properties files on the remote node now contains the individual logging settings.




Oracle has been made aware of the issue but I do not foresee a quick fix to the issue. In the meantime, you can manually synchronize using the button but remember that you will kick your users off if you do so. Oracle has been made aware of this issue was well. It's not quite as "dynamic" as intended but will allow you to do user specific logging.

Rebuild SQL Indexes in E1 Package Tables

With the change to table-based, XML-format metadata (formerly specs) in EnterpriseOne, first-use dynamic e-generation became a performance issue, impacting end users after a package deployment and affecting their perception of system performance. This article describes an easy method of lessening this impact.

A very short primer on auto package discovery and dynamic egeneration as it applies to the package tables:

Since 8.12/8.96, when a package is built, tables are created in the build environment's Central Objects database containing metadata for the objects in the package. The tables, named CentralObjectsTablePackageName (Ex: F98762BPDOCF001) are used by both the Enterprise servers and Web servers, guaranteeing consistency across the system.

The Enterprise servers access the package tables for objects as they are needed, bringing them across the wire to a local cache in the filesystem. The Java servers access the same package tables, bringing the objects across the wire, serializing them into the Serialized Objects tables and also placing them in cache.

Since the Enterprise and Java server now access the object metadata from the database server instead of local TAM files in the case of the Enterprise server and Serialized Objects (Java servers), there is now an additional burden associated with retrieving object metadata, an increase in the network load between the Enterprise/Java servers and the database server and an increase in the load on the database server.

A read of records in the package tables can be optimized by ensuring that the indexes are properly defragmented and the statistics are up-to-date. In SQL Server the index rebuild process does both.

Since we now have the XML metadata in tables, and we know that the dynamic e-generation is going to be accessing these tables heavily in the period immediately following a package deployment, it behooves us to rebuild the indexes on the package tables.

Execute the SQL2005/SQL2008 transact sql code below after the build completes but prior to deploying the package.


--SQL Script Begin
-- Enter appropriate variables in the SET statements for:
-- @DatabaseName
-- @SchemaOwner
-- @PackageName

declare @DatabaseName varchar (50)
declare @SchemaOwner varchar (50)
declare @PackageName varchar(50)
declare @SQL varchar(8000)

SET @DatabaseName = 'JDE_PD812'
SET @SchemaOwner = 'PD812'
SET @PackageName = 'BPDOCF001'

SET @SQL =
'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98762' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98720' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98711' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98713' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98712' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98710' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98743' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98751' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98750' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98740' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98741' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98306' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98761' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98760' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98745' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98753' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98752' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98770' + @PackageName + ' REBUILD ' + CHAR(13)

EXEC (@SQL)
--SQL Script End

Rebuilding the indexes of the package tables helps lessen the burden on the database server during the dynamic (or manual) e-generation process and improves object first-use performance.

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.

EnterpriseOne CNC Training

From the mailbag came the following question that I thought I would answer for everyone:

"Hi Jeff, what do you recommend for Oracle CNC training?"


My answer:

Depends on who it is for. A new CNC guy should have his training tailored and more importantly, timed to coincide with what they are doing on the system.

Common Foundation will provide the CNC trainee with the basics of E1 and should be taken just prior to the start of the implementation.

During an implementation the CNC person should be taking the CNC System Admin class while doing the tasks necessary to configure the system.

The Security Principles class should be taken about mid-way through the implementation as the security design is about to occur.

Late in the implementation, either near or just after go-live the CNC admin should attend the OMW/Change Management course.

System Administration Troubleshooting should be taken after the CNC person has a bit of experience.


The key is to align the classes so that the knowledge gained in the class is relevant and used right away, before the information gets stale and is forgotten. It also helps to have the correct knowledge ready for the events taking place during the implementation.

Simply match the learning with the doing. If this is not possible try to match the two as closely as you can. If you are asking this question post go-live, which is likely, just start hacking away at the courses, being careful not to take the next class before the information is learned, absorbed, practiced and solidified.


A listing of all Oracle courses can be found here.

Regards,
Jeff

JD Edwards "Julian Date"

What we (you, me, everyone in the JD Edwards universe) have always referred to as "Julian Date" is actual an Ordinal Date.

http://en.wikipedia.org/wiki/Ordinal_date


It is not, however, an ISO 8601 compliant Ordinal Date as ISO 8601 requires the use of 4 digit years.

http://en.wikipedia.org/wiki/ISO_8601#Ordinal_dates


The Julian Date is actually "...the interval of time in days and fractions of a day, since January 1, 4713 BC Greenwich noon, Julian proleptic calendar."

http://en.wikipedia.org/wiki/Julian_date


Do you suppose that 2454992.14583 ago, some guy was standing there with a sundial and said "Now!" to start the whole thing ticking?



Now you know...

Quick SQL Table Backup

Occasionally one needs to perform an action in either EnterpriseOne or SQL Server that places the data in one or more tables at risk. Examples include an index or (obviously) a table generation in OMW, a direct update to the data using a query, the first run of a custom UBE that updates data, etc. Best practices dictate that you should get a backup of the data.

Rather than using the time consuming process of backing up the entire database, and in the absence of table-specific backup/restore tools, one can quickly and easily make a copy of the table using standard SQL T-SQL code.

The command is SELECT INTO, or actually a combination of the SELECT command and the INTO clause. The basic statement looks something like


SELECT *
INTO new_table_name
FROM old_tablename

or

SELECT *
INTO JDE_PRODUCTION.PRODDTA.F0101_BAK
FROM JDE_PRODUCTION.PRODDTA.F0101


While this simple piece of code is sufficient to perform the copy, several problems arise. First, the code above requires you to correctly specify both the source and target table. Also, the SELECT INTO method will create a new table but it will not overwrite a table with the existing name. So, unless you feel like checking for the existence of a table named "JDE_PRODUCTION.PRODDTA.F0101_BAK" every time you perform this action I would suggest using the code below.

In this version we get a uniquely named table as long as you don't run it twice within a minute. The name will consist of the original table name plus characters representing the year, month, day, hour and minute. To do this, we grab the current date and time using GETDATE, convert it to text, replace characters like spaces, commas, etc. that we do not want in the table name, build this into a string, then use EXEC (@SQL) to run the query.

The code looks like this:

--SQL Script begin
DECLARE @Tablename NVARCHAR(500)
DECLARE @BuildStr NVARCHAR(500)
DECLARE @SQL NVARCHAR(500)
SET @Tablename = 'JDE_PRODUCTION.PRODDTA.F0101'
SET @BuildStr = CONVERT(NVARCHAR(16),GETDATE(),120)
SET @BuildStr = REPLACE(REPLACE(REPLACE(REPLACE(@BuildStr,'
',''),':',''),'-',''),' ','')
SET @SQL = 'select * into '+@Tablename+'_'+@BuildStr+' from '+@Tablename
SELECT @SQL
--Remove dashes on the line below to execute
--EXEC (@SQL)
--SQL Script end

and generates a query that looks like this:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F01012


In the example above we have specified the three part name "JDE_PRODUCTION.PRODDTA.F0101" in the @Tablename variable. You would change this variable to specify your table. Figured I'd mention that 'cause you just never know.

As constructed above, the query runs SELECT @SQL and will simply return the query string we built. It will not execute the copy. Once you are satisfied that the query string built is correct, remove the dashes from in front of EXEC (@SQL) to run the table copy.

You will see something like this when properly executed:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F0101

(1 row(s) affected)

(65436 row(s) affected)


Once you have completed the quick table copy you can now continue with the action that placed the data in danger knowing that you have a copy of the data.

Some caveats apply: the copy only brought over the data. No indexes exist on the backup copy of the table. Also, you now have an easily forgotten extra copy of what might be a very large table. Clean it up as a part of your scheduled maintenance, set a reminder to delete it later or use this method to automatically identify SQL Table Backups for removal.


Related postings:

Quick SQL Table Restore
http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html