Is a traditional client app which connects directly to a database a good idea?

Question

After using Django's excellent admin interface, I was pondering creating a similar system which wasn't as tied to an ORM.

Now, while considering this, I thought that overcoming webapps limitations (basic widgets, sessions, text/HTTP-based forms, distinct client-side/server-side languages in most cases with a limited communication mechanism, etc.) were a big timewaster, and that maybe traditional GUI development was a better option.

So that brought me to using a desktop app, and then a server part which connects to the database and exposes it via RPC (web services, whatever) to the desktop app, handling security in the traditional webapp fashion.

However, nowadays you can connect to databases remotely securely using SSL, and supposedly databases do provide sufficient role-based authorization (GRANT/REVOKE), so why not connect directly to the database and avoid having to code a server?

Is that a good idea? Are there unassailable problems with this approach? [I think that coupled with good database introspection, one could write a relatively simple framework which would enable very nice RAD of database-based apps)? Which are theoretical problems and which ones are problems with specific implementations?

bonus question: do databases provide enough authorization? While doing my research, I came across the following discussion thread:

http://archives.postgresql.org/message-id/4AE02DF0.40101@enterprisedb.com

which pokes some holes into traditional view-based row access control in the PostgreSQL implementation.

Answer 1

There are different points you need to consider regarding connecting an application directly to a database and relying on userid as a security control mechanism:

1. How to secure the database connection credentials on the client. There is no easy way I know of that is 100% guaranteed.

2. It is usually better to design security around the concept of a role and associate the role with a userid. linking userid to authorization is not very flexible. Databases I know only support userid permissions.

3. To connect to a database directly, you need to:

   3.A. Encrypt data communicated between the server and the client

   3.B. Bypass firewalls on the client (at least) and have specific server port open. Not all service providers allow this.

Web Services and Microsoft Silverlight using RIA Services allow you to overcome this problem.

I would suggest that you don't connect directly to the database because of the above and the possible security issues if the application data need to be secure.

Answer 2

If your primary concern is usability, a desktop application absolutely is the way to go. Most modern languages that have GUI toolkits also have robust libraries for communicating with databases.