> If the frontend layer needs to access the data anyway, how does that separation help?
It helps in multiple ways. Since you define the data API yourself, you can tailor it tightly to only the exact operations you need. This makes it more difficult to flat-out drain everything (or even query everything, depending on your app). It's also another layer of insulation. The fact that it's another, non-standard access protocol also helps. If I'm an attacker, I'll probably have a harder time figuring out how the custom data layer works; and when I'm done with that I still need to actually siphon the data, which will ideally require me to either issue millions of suspicious requests, or try to break into the API server itself, which has a drastically reduced attack surface compared to a typical web app. Which is where your second question comes in:
> And why is it easier to monitor for unusual activity?
Because you know what the legitimate access patterns of your app look like, you can raise flags programmatically. In fact, you can incorporate certain checks right from the time you start writing your API. It puts you, as a programmer, into a position of control and you can use the global insight you have into your app's design to your advantage by defining what's normal and what's not.
Compare that with a general-purpose DB interface, which is ...well, general purpose. It doesn't know when it's doing something implausible. When something goes wrong with these databases, it's often the admin/ops people who notice it, if at all.
> Since you define the data API yourself, you can tailor it tightly to only the exact operations you need. This makes it more difficult to flat-out drain everything (or even query everything, depending on your app).
Considering that the database was designed specifically to support the website (so it's not supposed to have unrelated data), and that the frontend intermediates all access to the data, wouldn't the sum of the operations require access to all data? Why would you have data that was not needed by the website?
> If I'm an attacker, I'll probably have a harder time figuring out how the custom data layer works
This is just security by obscurity, except if the attacker has access to the client code, it's not even very obscure. Finding all calls to the backend API is a trivial task nowadays.
> Because you know what the legitimate access patterns of your app look like, you can raise flags programmatically.
Why can't you do that from the database logs? A query is just like an API call - you can raise flags if the frontend makes queries outside of a known pattern (ie, non-recognized queries or in an unexpected order).
> wouldn't the sum of the operations require access to all data
How you can access the data matters, and typically not all data (nor all views to that data) are required to run the app. Some info should only ever travel one way (typically from the web server to the backend, like passwords, analytics, or payment data). There are whole categories of information that do not ever need to be queried in bulk by a legitimate user.
> This is just security by obscurity, except if the attacker has access to the client code, it's not even very obscure.
It's not like this approach in any way relies on that obscurity, but as a measure to increase time and effort required by the hacker, I'll take it. Just increasing the time spent figuring out how to query the backend gives site operators additional opportunities to spot that something is wrong. Compare this to the immeasurably small effort required to dump all SQL tables into a text file.
> Why can't you do that from the database logs?
Typically logs aren't analyzed in real time, and I'd hazard a guess in most cases they're not even analyzed at all. Most databases don't even keep an access log by default, and in many cases it's hard to implement one that is useful.
> A query is just like an API call - you can raise flags if the frontend makes queries outside of a known pattern
Theoretically: yes, practically: no. In case of SQL databases, this would practically mean communicating with the DB exclusively through stored procedures (or event handlers/filters in case of document databases), which is a poor man's version of what we're talking about in the first place. In practice, almost nobody uses their database in this way, because it's really painful and does not even offer that much protection.
