Summary
Some Showcase customers use Inclusive Row Security for JD Edwards EnterpriseOne. Since most customers opt to use Exclusive Row Security, the alternative, especially how it pertains to Showcase, is not as well understood. We need to know what to expect from Showcase and how JD Edwards settings affect what data end users will see when they run queries.
Additional Comments
Showcase does a lot of things under the covers to support JD Edwards. Some of those things involve the Dynamic Connector API and others involve simple SQL queries against JDE. Thanks to improvements we've made to the debug logging in Showcase 9.4.0.0 and recent patches, you can see most of this happen in the Showcase.log. It's not easy to read, but it's there.
In a hypothetical example, we can see in the log that user JDEUSER logged in and was recognized as a JDE user. You can see which Environment (JPY920 in our hypothetical example) and Role (hypothetically JDEROLE). We can see that with this log entry:
JDBC DEBUG JDEUSER RMI TCP Connection(3)-172.16.101.35 "03/05/2018 09:56:04.991 AM" SCRemoteStatementImpl.execute: SET LIBRARY_LIST=JPY920~JDEROLE
Further down the log, we see Showcase querying the F00950 file based on that selection:
SELECT FSUSER, FSOBNM, FSDTAI, FSFRDV, FSTHDV, FSVWYN,
CASE WHEN FSUSER = 'JDEUSER' THEN 1 WHEN FSUSER = 'JDEROLE'
THEN 2 WHEN FSUSER = '*PUBLIC' THEN 3 ELSE 0 END AS FSUSER_ORD
FROM E1920SY/F00950
WHERE (FSSETY ='4') AND ((FSUSER = ?) OR (FSUSER = ?) OR (FSUSER IN ('JDEROLE')))
ORDER BY FSUSER_ORD, FSVWYN
After that, the majority of the log consists of Showcase checking Data Dictionary for each field in the file being queried. Near the bottom, we see your query being submitted to the server to run. The server goes through several transformation steps in order to prepare that SQL to be submitted to the IBM Query Optimizer to run.
The final version of the SQL is here:
JDBC DEBUG JDEUSER RMI TCP Connection(6)-172.16.101.35 "03/05/2018 09:57:01.374 AM"
SCRemotePreparedStatementImpl.init: Customized SQL is
"SELECT fields FROM file WHERE some condition ORDER BY fields"
In the super simple example above, the WHERE clause may not look like what's in the query, which leads one to wonder why Showcase changed it. Normally, JDE row security rules would be applied to the SQL, and you'd see those restrictions and conditions in the WHERE clause. If those rules are applied, the WHERE clause will include them.
This is where we have to go back to the JDE documentation, and there are several phrases that stand out:
- Exclusive and Inclusive are mutually exclusive - they can't exist at the same time
- Exclusive: Row security is applied for the range of values that have N in the appropriate Add/Change/Delete/View action
- Inclusive: Row security is applied for the range of values that have Y in the appropriate Add/Change/Delete/View action
For the purposes of querying data, the appropriate action is "View". Thus, if Inclusive Security is enabled, then Showcase will ignore rules that have a View action of "N".
In the above example, Showcase is retrieving row security for *PUBLIC, JDEROLE and JDEUSER, but only when the View action is "Y" (because Inclusive security has been hypothetically enabled).
Row security rules are stored in the F00950 file, and the security type is "4". If it's not possible to see the JDE Security Workbench (normally, access would be limited to that tool, and the customer may not have access to it), you can query the F00950 file and search for a security type of 4 to find row security rules.
A typical example of a row security rule:
*PUBLIC – file F060116 – field MCU - values 0 to all 9's - View: N
JDEUSER - file F0101 - field MCU - values 0 to all 9's - View: Y
If our hypothetical user JDEUSER tries to query the F060116 file while Inclusive Row Security is enabled, then the above rule will be ignored and the user will be able to see all records in the file. This is because a View action of N means it is for Exclusive security and would not be valid in this case. *PUBLIC applies to all users, unless a user is specifically listed.
If JDEUSER tries to query the F0101 file, they will only be able to see rows with a numeric value (the MCU field is alphanumeric, so any character values will be blocked).
Here is a link to one version of the JD Edwards Security Administration Guide. Up to and including JDE E1 9.2, all versions of the manual are identical when it comes to row security administration, but this one happens to be available for download without an Oracle password:
https://docs.oracle.com/cd/E17984_01/doc.898/e14717.pdf
The section on Row Security includes examples and this is something that can be extremely useful to review.
