Not necessarily throwing away security, auditing or fine-grained control - MS-SQL has all these features, including row-level security.

Agree. In short, you lose all opportunities for the "Business logic" that is (typically) much more important than the simple data storage that the database itself is actually capable of. I'm sure there are some simple use cases where you really just want to let people easily share data in a "dumb" way. In that case there are probably much better fits than an SQL database. Heck, use an online spreadsheet at that point in time. As a result, I doubt there are really many use-cases where this option would actually be a good fit.

You can put all the business logic in stored procedures and triggers. Feature-wise they are equivalent. And a database is structured data with a schema, not comparable to a spreadsheet. But, yeah, still probably not a good choice.

It's not a good choice. You can have stored procedures, but again a database is optimized to storing data, not running business logic. The public library from where I grew up had MS Word as their database. It worked, but was not the best choice.

Powerful query capabilities... Most of the users won't make powerful, optimized queries, making the DB slower for everyone.

@EsaJokinen that's a very good point. DBAs struggle to run optimized queries, the average user will just SELECT * FROM TABLE full of LIKE anyway.

Also anytime you introduce a new API to the user your allowing the user to find any unhandled exception there could be. It could crash the application, the server, or manipulate memory in unpredicted ways. By having your server filter request into narrow set of commands you can better prove security and reliability. Obvious there can be some costs, reduced programmability and versatility, performance etc. Yeah you could do high speed data base right over I.P. But then you rely on who ever wrote your version of SQL.

And also it wouldn't work on most browser cause of plug ins and browser wars and trendy api nonsense.

@StackTracer - I don't think its FUD so much as he's focusing on "should you" not "can you". Personally, I think that's the wrong focus given this is a security site not a software architecture site. But that's not the same as FUD.

"You don't have permission on the record level" yes but you could create a view with only a user's records and give them permission to view it. Sure you'd have to probably make other views of any aggregated values, but I think it's assumed you'd be architecting your service differently anyway.

" A salary table would be a bad one" Citation needed. In most of the world, including the US, salaries are absolutely not confidential in general! If you want to use a vivid example replace "salary" with "health conditions" or similar...

A very big issue is also that once you discover that the MySQL permissions can't do what you want, it's difficult to fix without changing the whole protocol. This story about Basware's application has its roots in exactly this - direct access to database, leading to doing part of the access control on client side.

@paj28: "Should you" is the right focus for a security site. Almost every decision in security is based on "should you" factors. You physically can give everyone access to your database, or set your password to "swordfish", or hash your passwords with MD5, or build SQL with string interpolation, or generate keys with java.util.Random, or all sorts of other insecure things, but you shouldn't do any of that.

@corsiKa Give your clients access to the database, and scale it as you get more clients. That's the problem, it does not scale, and it takes one user with more privileges than needed to bring down everything. Managing thousands of database privileges aren't trivial, managing a couple roles on an application is better.

@ThoriumBR How is "It only takes one user with elevated privileges" any different in a database than it is in a webapp? Have you seen how swiss chesse the security is in most webapps in all the application code?

@ThoriumBR - That's why you also use roles on a database. Or ABAC

@paj28 and even with roles and ABAC, the majority of databases are accessed by applications, not end-users. Why?

@ThoriumBR Because users suck at writing SQL queries, especially ones that don't slow down the database, and they really don't WANT to log into a SQL database - they want a point and click web app for convenience. It's really a matter of performance and preference.

Tons of good reasons here and in chat. 1) App dev environment is easier to work with that in-DB logic 2) More flexibility of databases (e.g. NoSQL) and ability to change or use multiple 3) Easier to provide compatibility when migrating schema 4) More control of queries, stop people running expensive ones. ** So rereading your answer, you've mostly covered ones that are not completely accurate, and omitted some of the good ones. If you edited in all the good reasons you brought up on chat, would be an excellent answer.

there's also the whole API issue. if you provide direct SQL access. the database format becomes the API... but as your product goes forward you are forced to change database format every so often. providing direct access means you can' go forward with that database change. providing a high level access would make the change to the database seemless without issue.