figuerres said:spivonious said:*snip*you can run it on one or as many as you need.
that also has an effect when you need to scale up.
if you need to handle more users with database connections the sql server has to alloc more ram for each connection - try tripling the number of user connections and watch the server choke when it gets ram-hungry.
put if the connections are done by a service layer you can have say 3 servers each making say 10 connections each and handle say 200 users per server (or more)
so that 1 user != 1 sql connection.
when small you can have one server with both tiers.
if you use DNS to connect the client to the server then you can add mid-servers and let DNS sort them out to any number of mid-servers.
Database roles wise you should have a database Schema that maps what tables / views / procs etc... are availibale.
you then put the users login on that Schema
I have not done Oracle for a long time so i can't say eactly how to do that....
SET ROLE ALL sounds like they are getting full rights to the database ?? if so then thats not good at all.
*if* that's what it does.
do you have an actual DBA there ? someone with certified ORACLE DBA training ?
Oracle splits user privileges into two groups, default and session. When a user logs on they automatically get the default privileges. Running set role all gives them session privileges. There's no way for them to get privileges that aren't assigned to them so it's a fairly good system.
Anyway, I didn't set it up, and it's been that way for 10-15 years now, so I doubt they'll want to change it 
Maddus - just give them a login? The same could be said for pretty much any LOB app. Teach the users some SQL and fire the developers.
