Identifying Users

Sorry for the newb question, but in an E1 environment with Wepshere and an Oracle Database, is there a way to identify which JDE user is running a particular SQL, when the JDE users are using a shared Oracle User to connect to the database?

You would need to create a unique Oracle database user for each JDE user. This is definitely more administrative overhead, but at least then you'd be able to see who is doing what in the database.

I'm not sure how this is done, but I've seen the name of the machine the user is using asociated with a SQL somehow. It may be in some runtime metrics and not recorded as such. However, knowing the name of the user's machine may help in identifying the user.

Thanks both, that's what I thought.

I don't think creating Oracle users is really an option due to the admin overhead, and I think we lose the machine name at the Websphere layer, i.e. it works with Fat client but not thin?

We're looking at the JDE App Pack in Oracle Cloud Control, and hoping there maybe some way of linking users to sql there, anyone have any experience of that?

If you do setup dedicate proxy (aka system) users for each JDE user just keep in mind that it does reduce the effectiveness of the JDBC connection pooling on the JAS server. User sessions will not be able to share connections from the connection pool. With a small user base of 100 users or less that is probably fine but it won't scale for hundreds or thousands of users.

I logged a SAR ages ago asking for the CLIENT_IDENTIFIER column to be populated whenever a connection is retrieved from the pool. This column shows up in V$SESSION and could be used to tie SQL to a specific JDE user. It would also allow for native Oracle DB auditing to be used instead of the trigger-based CFR JDE auditing. The identifier value even flows into the archive logs and can be read with log miner. CLIENT_IDENTIFIER is an Oracle DB only feature so at the time JD Edwards wasn't interested because they said it wouldn't follow their platform neutral strategy. I have hoped that now that Oracle owns the product they might start leveraging more native Oracle DB functionality. So far no action in that area ...

Justin, interesting you'd think they'd implement it to improve their Oracle based solution and provide a reason to use it over other-vendor based solutions! Have you thought about re-raising the SAR?