the pros and the cons for 1 OW user = 1 DB user

[antoine_mpo]

Hi list,

So far on our OW platforms, we always created OW users using the same db user to connect to default data source.
It's quick, but as i spend more and more time dealing with oracle db performance issues, i'd be interested in knowing better which user is doing what from the db point of view.

I made a test with one user, and it quite easy to set up. But i was wondering if there were any possible issues with that, or extra load of admin work that i didn't think of.
I'd like to have your opinion based on your experience.
Or document reference, if it exists, on oracle support website, because i didn't find much about that topic.

Thanks for your help.

Cheers,

 

[Sebastian Sajaroff]

Hi,

At least on SQL Server, DB2/400 and DB2, it's not
so difficult to manage a DB user per JDE user strategy.

In fact, you create one DB user per JDE user, but
you manage your DB security by groups, not by individual
accounts.

That way, you won't be forced to type 500 GRANT or
DENY commands for each and every one of your 500 users.

 

[BOster]

We do this at our company, we implemented this when we are on Xe but have retained this strategy for our current TR release. As you pointed out, the biggest reason to do this (that I can see), is the ability to track performance issues at the database level. For example, we could detect blocking at the DB level and detect which user(s) were causing the blocking and use that information to find out what application they were in or what process they were running to try and determine what may have been causing the blocking. I am in development, not CNC, so there may be additional tools available now that were not available in Xe that reduce the need to be able to track issues at the DB level by user.

There is obviously the maintenance aspect that goes along with this strategy, but I don't believe it would be unmanageble. We have over 1000 named users and I don't believe it has really ever been an issue for our CNC group.

One thing to keep in mind, however, if you move to this strategy I don't believe Oracle really envisioned people running under this configuration. As such, their software is (or was), designed with the one DB user for a group of multiple OW users approach, especially with regards to connection pooling. When you move to this strategy you basically negate any benefits that OW's connection pooling implementation gives you since each user will now effectively have his own connection pool. In fact you may need to significantly LOWER the number of connections per pool. This was an issue for us when we moved from Xe to 9.0 until we figured out what the problem was and corrected it. This issue is something that can be managed/configured but should probably be investited further before you make the change.

Again I am in development and as such don't deal with these types of issues on a day to day basis like or CNC group does so it could be that the latest TR releases may have addressed the connection pooling issue.

 

[Tom_Davidson]

One issue I see is that when you go 1-1, the user will have access to the database via 'other' methods than JDE. For example ODBC, or Excel, or Access, anthing that allows you to connect to the DB.

If you use the proxy user, you can keep the password private.

That is the only major drawback I know of.

Tom

 

[Jotto]

Antoine,

I also moved our system from a single connection to a one-per-user DB proxy account. Our number one reason for this was to be able to track the user from the front end of OneWorld to the back end of the SQL Server. This also helped with our Sarbanes-Oxley and PCI compliance by limiting the ways to access that database, and limit who had the ability to access it directly.

This has worked very well for us as we have had multiple issues that were able to be resolved because we could track database activity by user. We did not see much performance change, so our Systems Configuration is able to handle the multiple connection pools.

For administration, I simply have all of the Database Proxy accounts have the same password, which is unknown to our users, and even our Help Desk staff. To create a new user, they just copy an existing user, and change the OneWorld information.

I then have a periodic job that runs a script which checks the P98OWSEC table to see if there are any new accounts, and creates database accounts accordingly. It adds them to the correct database role on the SQL Server and then updates the OneWorld user information with the new database account information.

Ultimately, there should be no direct technical issue that would arise from you doing this, you just need to understand the additional administrative cost, and be able to monitor for any performance issues -- as the others have mentioned before me.

Hope that helps.

 

[antoine_mpo]

Hi Sebastian,

Thanks for your answer.
Indeed, i'd use Oracle roles to deals with grant and stuff.
Unfortunately it's not the standard way JDE deals with oracle DB rights ! Each time you generate a table, it gives a grant all to PUBLIC, which means that any oracle user can do anything on tables (select, delete, drop table, ...), whatever restriction you'd try to apply to a db user ! It's a nonsense.

 

[antoine_mpo]

Hi Tom,

I don't agree with you. If i'm correct, creating a db user for each jde user doesn't mean that they can access the database outside JDE, as the db user/password is set by the jde administrator, in P98OWSEC, for the default data source, and it's not the password of the jde application user, which can be change by the user himself.
Or am i missing something ?

 

[antoine_mpo]

Hi BOster,

Thanks a lot for sharing your experience on that matter.
And i'm very interested in what you said about connection pool consideration.
When testing to use a dedicated oracle user with a particular OW user, i did notice several db connections with that db user, some from a jdenet_k process, and some from jdbc thin client (we use web client of OW).
Do you mean that when you create a db user per ow user, the jde processes create much more db connections because it open them for each db user instead of sharing some connection if it's opened with a unique db user ?

If so, how did you reduce the connection pool ?
I saw parameter that could match in jas.ini of websphere servers, but not in jde.ini of the enterprise servers.

Thanks for mentionning it, because if so, it's true that it could lead to a big mess. I need to do some search on that topic.

 

[antoine_mpo]

Hi John,

Thanks a lot for sharing your experience on that matter.

Cheers,

 

[Tom_Davidson]

I'm assuming your using the same user id for the user, could be a bad assumption. I'm not sure you really want to maintain what are effectivly separate user ID's that happen to be the same name. You will limit any advances that come along for SSO, LDAP, etc you may want to use.

Hence my comment. Technically you are correct, I personally wouldn't want to try to maintain the same ID in two different (but the same systems). Just one mans opinion.

Tom

 

[antoine_mpo]

Hi,

I'm starting to wonder if i could set up this configuration, because if it increase the number of Oracle db connections, we can't really afford it on the current production environment. Indeed, our Oracle database is on a 32-bit Windows server, and we already reach the system limits ...
When we reach over 520 sessions on the oracle server, we start to get jde errors because the system cannot open new connections (due to memory) ...
During high activity period, we're often close to 500 sessions, and in low activity we're between 430 and 480 sessions.
Don't you think we're doomed to keep our actual configuration of a unique db user ?