Why can't I just let customers connect directly to my database?
TL,DR: Don't.
(My-)SQL permissions are pretty fine-grained, so I'd wager there shouldn't be any obvious security issues
Even with permission on the record level, it does scale easy. If a user has irrestricted SELECT on a table, they can select any record on that table, even those not belonging to them. A salary table would be a bad one. If any user has DELETE or UPDATE, they may forget the WHERE clause, and there goes your table. It happens even to DBAs, so why would it not happen to a user?
Performance should be way better, since we skip the entire HTTP communication and web app code
And you throw away all security, auditing, filtering and really fine grained permission control from using an application to validate, filter, grant and deny access. And usually most of the time spent on a transaction is the database processing the query. Application code is less than that, and you will not remove the HTTP communication, you just replace it with SQL communication.
New features are a matter of database migrations, everything is reflected in the schema
That's why so many people use a "spreadsheet as a database." And it's a nightmare when you need to reconcile data from multiple sources.
Powerful query capabilities are provided to users, without any additional effort
It's like putting a powerful engine on a skeleton chassis, bolting on a seat, and taking it to a race. There's no extra weight slowing the car down, so it's very fast!
It's the same here. Sure, it's fast and powerful, but without security measures provided by the application, no session, record-level access control, "users do what they are allowed to", or auditing.
One of the most common vulnerabilities on web applications is SQL Injection, and you are giving a SQL console to your users. You are giving them a wide variety of guns, lots of bullets, and your foot, your hand, your head... And some of them don't like you.
Interesting question. In theory, this can be done securely. MS-SQL can secure the connection with encryption, authenticate the user, and provides fine-grained permissions and other security features like auditing.
In fact, it used to be common in intranet environments that thick clients would access a database directly, so the database security controls were the primary security mechanism. This tended to be done badly, for example, all users connecting as admin with a password hardcoded in the app. But it can be done well.
A major problem is privilege escalation flaws. The database API is extremely complex presenting a huge attack surface, and the protocols are designed for speed, as well as being old and not Internet hardened. Oracle, for example, has had hundreds of privilege escalation flaws. However, MS-SQL is one of the better databases in this regard. You can also reduce the attack surface by locking down user permissions.
Architecturally, exposing an interface that allows generic queries, and applies security restrictions, makes a great deal of sense. To some extent people are reinventing the wheel as REST APIs gain features like custom queries.
Whether you can do this depends largely on the relationship with your users. If these are paying customers with a contractual relationship, so to some degree more trusted than a random Internet user, it may be appropriate to use this architecture. Especially if different clients are siloed on separate databases. Tread with great care. It's the kind of thing where if you were to suffer a breach, you could be criticised for this, despite having consider the risks and benefits carefully. If you're running a web scale service with anonymous sign-up, I would avoid this. Although it's worth noting that most cloud platform providers do offer features where they expose database ports to clients.
I've built both RESTful interfaces and provided customers with direct SQL access.
The problem here is that the question is fundamentally flawed:
Lots of the work backend developers do is providing CRUD access to customers via HTTP, essentially mapping data from and to the internal database.
This is not, in my experience, a significant portion of what I do. Let's simplify it to 4 tasks RE data access:
- Validate incoming Data.
- Authentication, Authorization, and Logging.
- Expose a limited set of functionality.
- Provide an API that is sensible for the user.
DB's typically do not provide tools to do this as needed by these tasks. For example, I might want to:
- Validate incoming data using an external service.
- Use OAuth to provide Authentication, and roles to provide access to specific rows. I then have specific logs I want to write based on roles/data access.
- I might want to only expose certain reporting (e.g. for performance (think DoS) or business reasons).
- SQL is not the format that most of my customers want most of the time.
While I'm sure there is some DB with some feature for each one of these scenarios... typically most DB's won't support most of these scenarios because they are databases and as such not designed to handle business logic.
All that being said, there are scenarios where clients want database level access - in which case you will find solutions that do provide direct access. There's nothing prohibiting this from occurring - it's just not typical.