Well, that depends. If the attackers gain complete control of the automated application, they can potentially decrypt the entire system (or, at least, the parts that application has access to). If they only gain access to just the datastore, they may or may not be able to decrypt the data -- the encryption keys might not be in the datastore at all. It depends on the manner and scope of the access that's gained.
In a reasonable environment, gaining remote code execution on the database server wouldn't give you the ability to decrypt anything, the DB shouldn't have access to the decryption keys/oracle/whatever.
To get a dump you'd need to compromise two systems - a database server that has access to all the data, but in the data it stores and returns all the interesting columns are encrypted; and an app server that can decrypt data that it gets (and it has the keys only for the columns that this app server actually needs to use, if you have different classes of confidential data) but can't get all the data from the database freely, only a predefined subset in a limited and logged manner controlled by the database system.
Granted, if the attackers can get RCE on one of those systems then it's likely that with some effort they can get the other system as well in a similar manner, but still it's an useful defense in depth.
I'm trying to imagine a scenario where data is stored encrypted in the db and then decrypted in the app server (I've seen this in a bunch of random PHP apps over the years). A SQLi might be able to dump encrypted data or escalate to execution on the database (from which you could pivot back to the app server), but just the SQLi doesn't necessarily expose the encrypted data.
Is the assumption that control over the db can pivot back to the server or that if there are basic issues with db access that there must be other exploitable issues (probably a fair assumption)?
No, I still disagree. An attacker could compromise the backup system, or access the file system and duplicate the database at the file level, or just steal a disk. Both Oracle and Microsoft SQL Server support transparent disk encryption to protect against precisely this type of attack.
> If you're a defender, I would go further and say you must assume it.
The assumptions you make when reacting to a known compromise are wildly different than what access an attacker actually has and what they might be able to do.
I am not sure if thats necessarily true - a lot of folks are using cloud databases/datastores (Azure, S3 etc). Getting access to that is a different problem than remote code execution. Unless I am misunderstanding something, or you are specifically talking about on-site databases.
"Encryption" in cloud data stores (like KMS) is really just an expression of permissions; it's systems security, not cryptographic security. If you don't have permission to access a resource on another server, yes, you've protected that resource --- but you don't need cryptography to express that.
Since you mentioned KMS, S3 has ACL mechanisms, along with separate mechanisms to encrypt at rest using KMS, or any client based key. On cloud based stores, you can't basically guarantee systems security against the cloud provider or intrusions in their system, and for sensitive data need to encrypt it from your side.
In general I am not sure if we wish to conflate systems security and cryptographic security - cryptographic security ideally should guard against system security failures. Although in practice I grant you that broad system failures which expose crypto secrets (code execution would fall into that) would lead to crypto failures as well.
