I'm afraid adding a Web Service layer is probably the correct solution to your problem.
Separating the client from the underlying database implementation will probably help you in the long run too.
Adding a web service layer doesn't necessarily have to hurt performance...
Indeed, with an appropriate API, a web service can actually improve performance, by batching together multiple database queries within the data center LAN, rather than requiring multiple round trips over the WAN.
And of course a web service layer can often be scaled horizontally, and add appropriate caching to your database queries, perhaps even a change notification mechanism.
A server layer adds security that you cannot possibly ensure with apps running on a remote client. Anything that runs on a client can be "hacked" and should not really be considered in any way trusted. You should only really put presentation logic in the client, and host anything important on hardware you have complete control of.
I don't know about your apps, but my web apps are naturally split into several layers, with the presentation code separated from the persistence layer by at least one level of business logic that keeps the two apart. I find this makes it much easier to reason about my app, and so much faster to add or modify functionality. If the layers are separated anyway, it is relatively easy to keep the presentation layer in the client, and the rest on a server under my control.
So while you can solve your problems without introducing a "web service" layer, by the time you have written all the stored procedures (or equivalent) necessary to fill in the holes in the standard database security implementation, you would probably be better off writing a server-side application that you can write proper unit tests for.
If you only need to cater for such a small amount of users, you could also consider delegating credential storing to some other service, such as Google
or Facebook
. This is usually preferred since the user needs to remember one less set of credentials and you have one less thing to worry about.
That being said, this could be problematic if you want to run your application offline (intranet). In that case, I would go to an SQLLite approach. This would also allow your application to scale up to a certain extent, and if you would need to move to a more robust SQL server, you would already have the data in the format required, so the migration would not be that much of a headache.
Lastly, when it comes to storing passwords, if you really must do it yourself, I would recommend using salted hashes to store your database. You should find plenty of examples online to do it with your technology of choice.
Best Answer
I just went though this myself. Assuming you're using SQL Server, I actually created user accounts on the database server for every user (you can do this from t-sql). I have one general user account that I can use to first verify that I can make a connection, and select from a
Version
table that makes sure the version of the software running matches the database schema version.Users who have the ability to add/remove users in the application also need to have rights to administrate users on the database server. This is actually a problem since the users are defined at the database server level, not at the database level. That means someone with the ability to add/remove database server users could potentially create a new user that could access another database. For this reason, I suggest running a separate instance of the database server for this.
There are other ways to go about it: