graham_jones
Active Member
Hi,
Does anyone know if there is a downside to having just a single Oracle System User ID for 1,500+ users?
Does anyone know if there is a downside to having just a single Oracle System User ID for 1,500+ users?
cmanderson
VIP Member
Sarbanes-Oxley (Seg. of Duties plus basic system security principles) and management (monitoring who is doing what from a database perspective), these are just two things which come to mind...
graham_jones
Active Member
Charles,
Thank you for responding with those points.
As a UK company we have not being affected by Sarbanes-Oxley. In our annual audit the subject of the system user has never come up. Now that I've said that however ...
Using Oracle DBA Studio I am able to see DB users listed by their Oracle ID, OS User & Process ID and the client/server they are signed on at.
Thank you for responding with those points.
As a UK company we have not being affected by Sarbanes-Oxley. In our annual audit the subject of the system user has never come up. Now that I've said that however ...
Using Oracle DBA Studio I am able to see DB users listed by their Oracle ID, OS User & Process ID and the client/server they are signed on at.
cmanderson
VIP Member
Excellent point....I usually don't make a habit of looking at the posters country of origin (perhaps it helps me to be less biased) but in this case it didn't make a lot of sense, did it?
As for the single Oracle id, I suppose some of it comes down to your confidence level within the application. Perhaps you place 100% trust in the JDE security workbench, in which case it is fine. Still, I have a bit more comfort with some hard coded security at the database level, e.g. sensitive employee information such as salary (and for American users their Social Security numbers) can be locked down to only those users who need it, regardless of the security applied in the F00950.
As for the single Oracle id, I suppose some of it comes down to your confidence level within the application. Perhaps you place 100% trust in the JDE security workbench, in which case it is fine. Still, I have a bit more comfort with some hard coded security at the database level, e.g. sensitive employee information such as salary (and for American users their Social Security numbers) can be locked down to only those users who need it, regardless of the security applied in the F00950.
Sebastian Sajaroff
Legendary Poster
Hi,
Downsides :
Hard to identify who's running what.
Security, all of users have the same profile.
Upsides :
Just one DB user to manage.
In a perfect world, I'd prefer to have one DB user per
OneWorld user but it can quickly turn into an
administrative nightmare.
There's a trade-off solution : one DB user per OneWorld
group of users...
Regards,
Downsides :
Hard to identify who's running what.
Security, all of users have the same profile.
Upsides :
Just one DB user to manage.
In a perfect world, I'd prefer to have one DB user per
OneWorld user but it can quickly turn into an
administrative nightmare.
There's a trade-off solution : one DB user per OneWorld
group of users...
Regards,
altquark
Legendary Poster
I don't believe that SOX compliance has an issue with an administrative system user such as "jde" on OneWorlds databases. I know they don't have issues with "administrator" on all NT systems, nor do they have issues with "sa" on all microsoft systems around the world - so I'm positive they don't have issues with OneWorld's "jde" user.
As for who KNOWS the JDE password - thats another matter entirely. It should be changed relatively often, it should be restricted to as few members of the administration team possible.
The way to comply with SoX is to provide justification for use of specific processes, and to document those processes. I would say that any fully documented method of resetting the "jde" user password on the database system would indeed be compliant, and ensuring, if possible, that no-one directly uses the "jde" user id/password to connect to the database interactively would also ensure compliance.
You need to talk to your auditors - but having to manage "groups" of users with a list of database user ID's that need to be reset on a regular basis would end up being a much larger nightmare - and of course, the same number of people would know all these user ID's and passwords (the administrators !) - so it really would be very redundant.
As for auditing on the server - if I had "APUSER" as a database user - and there are 50 AP users connected via this database user - how would I be able to track issues with that user any better than the "jde" user. Only by having a complete nightmare of one E-One user to one DB user would you be able to provide proper auditing - and thats not realistic.
As for who KNOWS the JDE password - thats another matter entirely. It should be changed relatively often, it should be restricted to as few members of the administration team possible.
The way to comply with SoX is to provide justification for use of specific processes, and to document those processes. I would say that any fully documented method of resetting the "jde" user password on the database system would indeed be compliant, and ensuring, if possible, that no-one directly uses the "jde" user id/password to connect to the database interactively would also ensure compliance.
You need to talk to your auditors - but having to manage "groups" of users with a list of database user ID's that need to be reset on a regular basis would end up being a much larger nightmare - and of course, the same number of people would know all these user ID's and passwords (the administrators !) - so it really would be very redundant.
As for auditing on the server - if I had "APUSER" as a database user - and there are 50 AP users connected via this database user - how would I be able to track issues with that user any better than the "jde" user. Only by having a complete nightmare of one E-One user to one DB user would you be able to provide proper auditing - and thats not realistic.
graham_jones
Active Member
We started off on the 1 to 1 route but when we rolled out the expenses module we went gained an extra 1,000+ users. We also tried the group user idea but we found over time people switched roles and we ended up with some with just 1 user in them.
cmanderson
VIP Member
I'm coming from an organization with over 7,500 unique users in production. The way we handle our security is by assigning the users one of five system users (we have 11 db users in the F98OWPU, 2 of which are for QA purposes only, DEV is a separate installation with its own system tables.)
The five system users are either production user, production HR user, production report writer, production HR report writer and auditor (read-only in every important way).
The other users are dedicated to Point Solutions (it really does make it a bit easier to filter using unique DB users in tools like Oracle Enterprise Manager), JDE and a copy of JDE with somewhat reduced privileges, JDEUSER.
The five system users are either production user, production HR user, production report writer, production HR report writer and auditor (read-only in every important way).
The other users are dedicated to Point Solutions (it really does make it a bit easier to filter using unique DB users in tools like Oracle Enterprise Manager), JDE and a copy of JDE with somewhat reduced privileges, JDEUSER.
graham_jones
Active Member
Charles, Sebastian & Jon,
As always guys, many thanks for the feedback. All points noted and very much appreciated that you took the time.
As always guys, many thanks for the feedback. All points noted and very much appreciated that you took the time.
wainwrr
Well Known Member
1. Security - a single oracle user for all jde users means you don't have any control at the oracle level but only at the jde level. So you surrender that control and unless you change the JDE GRANT ALL TO PUBLIC approach you pretty much surrender security too.
2. Tracing - ENterprise manager console etc. will show you connections by that username. You get additional info if you have a fat client connection but NOT if you have web clients (which we do)
3. Audit - you may not be SOX bound (we are - UK company with american parent) but I expect UK auditors are going to start to apply SOX like tests to UK only sites soon. (They may be driven to just to cover their american interests).
4. Audit in another sense. If you apply auditing at oracle level or try to recover things via logminer it'll be easier if you don't have everything logged under one userid.
Ideally I go for one oracle userid per role (or group of roles if you have many, many roles) and control the grants "appropriately". It's far too easy to get a full oracle client/MS-accss/excel and just tap in unless you do something sensible to protect the database. after all, how hard is it to type connect jde/jde@jde ?
2. Tracing - ENterprise manager console etc. will show you connections by that username. You get additional info if you have a fat client connection but NOT if you have web clients (which we do)
3. Audit - you may not be SOX bound (we are - UK company with american parent) but I expect UK auditors are going to start to apply SOX like tests to UK only sites soon. (They may be driven to just to cover their american interests).
4. Audit in another sense. If you apply auditing at oracle level or try to recover things via logminer it'll be easier if you don't have everything logged under one userid.
Ideally I go for one oracle userid per role (or group of roles if you have many, many roles) and control the grants "appropriately". It's far too easy to get a full oracle client/MS-accss/excel and just tap in unless you do something sensible to protect the database. after all, how hard is it to type connect jde/jde@jde ?
