spivonious said:Maddus Mattus said:*snip*
"You need to prevent your users from being able to connect to the database without the application."
Why? They can't do anything directly in the DB that they couldn't do from the app.
figuerres - one of the developers that is no longer here setup a "OneClick"-esque updating system with our main VB6 client, so updates are downloaded and installed automatically. Pretty neat actually. I definitely see the advantages of a 3+-tier architecture, but for our needs the 2-tier is working fine. All that adding another tier would do is add another server, unless I'm missing something. The app.config would make sense too, but the client has a server choice. End users don't have a logon to the development server, and the production server is chosen by default. Not the prettiest solution, but it works.
Anyway, I have no power to change these things, so back to the topic at hand!
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 ?