Figure: Encryption works to prevent direct access to disk data
What most people forget is that most of the encrypted data is available in plaintext to database systems (if transparent encryption is used) and applications. If an attacker can get access to the database or application, they get access to plaintext data. The strongest encryption algorithm or the key for that matter won't help in that case.
Figure: Encryption fails as attacker hacked into the database server which has access to plaintext data
Figure: Encryption fails again as attacker gained access to the application server which sees plaintext data
So, if encryption won't fully help to protect your data, what can we do to improve protection? Obviously, we need to use defense in depth and we need to make it harder for attackers to gain access to application or database server. Application firewalls and database firewalls can help.
What if attackers penetrate the firewalls and gain access to application? One way to contain/control the damage is to always use a least privileged database user in the application so that the attacker has access only to limited objects in the database. Hence, the damage they can do is limited.
Another control mechanism is to make only the least information available for the application. For example, if the application requires two columns of a table, instead of using the full table, you can limit to the two columns only. That way, if the attacker does not have access to the database directly, they can only steal limited amount of data. One step further is to dynamically mask (redact) data before sending to the application if the full data is not necessary for the application functionality. For example, if the application just displays the credit card number, instead of sending the full 16 digit credit card number, masked number with the last four digit could be sent to the application.
Let me tell you one last, but not the least, brilliant idea. Delete or archive the data that you don't use. If there is no data, there is nothing to steal!
What about insiders who misuse the applications or databases .. and also targeted insiders (i.e. insiders whose credentials are stolen by attackers to avoid perimeter security)? Granting least privilege can help, but this where you need to have detection mechanisms (auditing and monitoring) in place. Behavioral anomaly detection could help to stop such attacks and remove the malicious or targeted insiders from the system.
The bottom line is encryption is not the cure for all your security threats. You've got to strengthen the security at the points where the plaintext is readily available. Always grant least privileges to application and database users. Deploy firewalls and detection systems. Extensively audit to make users accountable for what they do. Educate users how not be the target of phishing attacks which gives easy access to your systems to attackers. With all these sophisticated attacks we see day in and day out, defense in depth is the way to go!