Can you made secure, multi-user, client-server application with multiple clients (1-5) directly connecting to database? Or you have to make custom server application and connect them via WCF or something similar?
Right now I have connection directly to DB but didn't implemented authorization yet. Using SQL Server Authentication authentication is good idea?
Problem is that I have to implement user management in application and every user action (adding record/modifying record) must be logged to db. If I use sql users, people could bypass logging mechanism in application by connecting to database directly with their credentials.
WCF seems like good solution but it requires more time to develop and change application architecture (I took that into account when was developing application, but there is still a lot to change). And I don't know WCF much.
It will be internal (not connected to internet) application but it will contain sensitive data.
Edit: and users are not trusted.
Best Answer
It all depends on the requirements of your system. If you have 1-5 users who will only use the application internally and there's a Windows domain, yes, a physically 2-tier application is a fine design. Logically you'd want to code for n-tier though, so that if those requirements do change you can more easily separate out the physical tiers. You could use AD Group membership, and connect to the database using Integrated Security. The map the AD group to a DB user, and grant permissions in your DB based on that role/group.
I had built such a system, and it worked well. Until half of the users had their offices moved to the location in another town. They were still logically part of the same network where the DB was, but the VPN tunnel was over a very slow T1 line. The app was unusable from their new location. Fortunately I had designed my n-tier application using the Csla.Net framework and was able to make some config file changes, setup an app server (IIS server), and my problem was solved. That made the network between the client application less chatty, as the business objects serialized themselves to the app server, did the chatty db work (and the DB and IIS servers were on the same network switch), then serialized back.
Of course you can do the same with WCF; you'd just have to build it in now and not worry about it, but there's more upfront cost to building the WCF service. However, you'd be prepared if the requirements change and the chattiness of the DB code becomes an issue. You can also use views and other DB objects as needed to help secure, although I've found SPs which are just thin wrappers around insert/update/delete statements to be more maintenance than they are worth.