>We have a little debate running here and I'm looking for opinions and experience.
>
>We have a Human Resource (H/R) system in VFP, going against an SQL server database. Let's say there are 20 users, but I could see eventually permetting each employee to see his own file over an Intranet -- thus several hundred users that way.
>
>In our application we already have sophisticated security... which users can use which modules, who can update which fields on which records, etc. In addition there is some automatation, and sometimes a user's action might cause a change to a field which he cannot see directly. Also, a user might be able to see the one field on one record, but not the same field on a different record. For example, he sees the salary of his employees but not his boss's salary, yet he still can access his boss's record for other things (e.g. home phone number). The H/R department manages their users' security.
>
>I want our application always to access SQL server through the same userid and password. This user-ID is reserverd for the application, and it has full power. The user accesses the application with his own application-defined password, that the application controls. It seems to me that this fits the 3-tier concept. The UI access the BIZ tier, and the BIZ tier access the BACK-END. But some DBAs tell me that the database should control all access, and all user passwords should be defined in the back end. This doesn't make sense to me, and seems to be trying to grab control from my user department.
>
>Am I wrong? What do you all think? What is the norm?
>
>Thanks for all your input. --- Tom

It depends on who you ask. The security built into your app is one option. A DBA will tell you that the backend should control security. Some security is needed in the backend of course as in the single "application" user you are thinking of implementing, but not necessarily for every user. SQL Server 7 and above has the concept of "application roles" which is basically what you describe.
One argument for the single userid/password hitting the server is that it can efficiently use connection pooling available in ODBC and OLEDB which will result in improved performance. This requires that the security be handled by something else.
This other option is hosting your biz tier objects in MTS (or COM+ services in W2K), which will allow you to define users and roles for your application.
With this last scenario you can make your DBAs / Network admins happy by letting them administer the security in the MTS explorer.