3

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.

Improve this question
1
  • 1
    ERP software has been written using this model for eons.
    – whatsisname
    May 29, 2017 at 14:13

2 Answers 2

Reset to default
2

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.

Improve this answer
6
  • It's very unlikely that system will be used outside of one building. There is no Windows domain. I have concern about logging requirement, how to stop bad actors connecting directly to DB and adding record as someone else (insert other ID). Using only stored procedures for modifying data? It will be pain to implement.
    – Szel
    May 29, 2017 at 13:41
  • @Szel I believe SQL connections are encrypted, or can be configured to be encrypted, so you could use SQL users/roles. The DB shouldn't be exposed to the internet at large anyway (nor should any of your users workstations), so SQL auth vs. Windows doesn't change too much in this regard. Since you're not using Windows auth, you'll have to build your own usually by creating your own IPrincipal/IIdentity implementations. Your application would do the logging. I wouldn't use SPs except in specific cases where performance would be an issue.
    – Andy
    May 29, 2017 at 13:51
  • 1
    @Szel But you need to understand what you're trying to protect against; are you really going to consider even internal, behind the company firewall the same level of threat as an internet client? if so that can change things; app appserver/wcf service could act as a barrier if you only allow SQL connections from the app server, not from just any computer. If you need to go that far is something you'll need to work out for yourself though. As I said, that route will increase the cost of the system to build, but if you really need to lock things down, then that's what you need to do.
    – Andy
    May 29, 2017 at 13:54
  • Users are not trusted, they have permission to add and modify things but I want to avoid impersonate other users (like when inserted row has UserId column and they put there other user id) or changing modification history.
    – Szel
    May 29, 2017 at 14:12
  • @szel - I haven't checked if this is doable with SQL server's permissions model, but you may be able to funnel all updates through stored procedures that verify your security constraints and deny requests to make inappropriate changes. (I.e. if you can grant the user permission to run the stored procedure and then grant the stored procedure permission to update the data, you can make it work ... If your DBMS doesn't support that, then you'll need to find another way to keep your changes consistent, eg logging and aggressively reverting unauthorized changes)
    – Jules
    May 29, 2017 at 15:56
0

There is no clear answer to this question without knowing more about your application.

There is almost no difference concerning the user management.

The much more important point is: do you need long term transactions?
If so a 2 tier architecture is easier to build. Otherwise most frameworks out there support 3 tier architecture that allows for the business logic at a central point but outside the database.

"I have concern about logging requirement, how to stop bad actors connecting directly to DB and adding record as someone else (insert other ID)."

The solution to that lies in good database design: do not let your users directly access the tables in your database.

Provide access via views. When the user runs DML against them you can change, add or remove fields as your security model requires. Eg. you can replace the "userid" the user passed with its DML by the UserId taken from the real database session.

Improve this answer
2
  • I don't need long term transactions.
    – Szel
    May 29, 2017 at 13:45
  • Posted commend instead of new-line... It's ok to create users directly in DB and using them as application users? I don't need long term transactions. It mostly will be CRUD but system must be very safe and users are not trusted. From other comment - "I have concern about logging requirement, how to stop bad actors connecting directly to DB and adding record as someone else (insert other ID)."
    – Szel
    May 29, 2017 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.