I'm developing a web-application that stores sensitive user data. Although the data is sensitive we want it stored permanently because the users might want to come back later and reuse it. The user data must be accessible by the server during a user session.

We can assume that the number of active sessions is low in comparison to the number of datasets permanently stored.

* **Asset**: Part of the data stored in the database that is really sensitive - but needs to be processed on the server-side during user-sessions (that's the point of the application) and stored there permanently (for usability reasons).
* **Scenario**: A hacker gains access to the database and is able to read all it's data.

*NOTE: This is not meant as substitute for a risk-analysis nor any other part of security management, nor is it meant as substitute for certain other measures (password policy, SSL/TLS, audits, … you name it). This is about checking one specific security measure.*

**Proposed counter-measure: User-data encryption**

To mitigate the risk of leaking all of the sensitive data to an attacker gaining access to the database, I'd like to encrypt the data (very similar to option 1 [in this question](https://security.stackexchange.com/questions/53924/which-approach-to-encrypting-data-on-a-per-user-basis-is-more-secure)):

1. During user registration the server generates a user-key `U` (AES).
2. The user-key is encrypted with a password derived key (SHA256) `d(p)` and stored in the database `AES(d(p), U)`.
3. Every time the user logs in, the key is decrypted and stored in the server-side session storage `p -> d(p) + AES(d(p), U) -> U`.
4. The user then is able to work with his/her data as long as the session persists. The data is encrypted with a random AES-key (message key `m`). The AES-key is then encrypted with the user-key and stored alongside the encrypted data (`AES(U, m) + AES(m, data)`).

**Question 1**: Are there any obvious security flaws in that scheme (except that the attacker can access the data of all logged-in users)?

---

**Password-recovery**

One obvious flaw of the above scheme is that there is no process to do a password recovery: Once the password is lost, the user-key can't be decrypted and thus the data is lost too.

To work around that we generate another RSA-key-pair (`M_pub + M_sec`). The public key is part of the application configuration. Additionally to the previous scheme:

1. a) The user-key and the user-email is encrypted with the master key and stored with the user-record as recovery information `RSA(M_pub, U+user.email)`

We have another app, on another server that has access to the private master key (but not to the database). The password recovery would then look like this:

1. The user starts the password recovory as usual (reset password -> email with link to a form).
2. The user chooses a new password `p'`.
3. The original app now asks the password-reset app to decrypt the user-key by sending the recovery info `RSA(M_pub, U+user.email)` and the new password derived key `d(p')` to the recovery server.
4. The password-reset app decrypts the recovery info `M_sec + RSA(M_pub, U+user.email) -> U + user.email`, sends a notification email to the `user.email` (telling the user to report the incident if he/she didn't cause the recovery) and sends the newly encrypted user-key `AES(d(p'), U)` back to the original app.

The recovery-process would be rate-limited so that the number of breached keys is limited until the breach is discovered.

Pros:

* Password recovery is still immediate.
* An attacker needs to hack two systems to gain access to the full database.

Cons:

* An attacker able to gain access to one system is likely to gain access to the password-reset system too.

**Question 2**: To what point does this way of doing the password recovery reduce the effect of the encryption? (Is it still worth doing?)

**Question 3**: Are there any other processes that improve data-security (in case of a hacked server) without making an automated password recovery impossible?