I got to thinking this morning over my coffee, that it would be nice if I could encrypt data for a Tenant with one key, and decrypt it with another key. In such a way that only a logged in User can access data in his organization (Tenant). I, as the system administrator, cannot access it. That's public key encryption so far. The public key can be used by the system to encrypt data and the private key is the decrypt key, similar to TLS. The decrypt key could itself be encrypted and stored in the users table using the plaintext passwords of each Tenant User, which is not stored. Thus the decrypt key can only be obtained when a Tenant User logs in to the system with the plaintext password. This way not even the system administrator can access the data when no Tenant User is logged in.
Encrypting the same plaintext with multiple keys doesn't make them materially more vulnerable, so it's ok to have multiple encrypted copies of the private key like this.
A tricky part is encrypting the decrypt key with the users password when they create an account. Another user would need to login and create an invite link first, which would contain the decrypt key encrypted with a system key. That seems ok, since that's a common flow for adding a user to an organization anyway.
Another tricky part is resetting the user's password. They'd need access to their email plus an invite link created by another logged in user (or the original invite link.)
This seems to me like it could work. If you lose all passwords, the data is unrecoverable, but that's how it should be.
It seems like it would reduce a lot of surface area to attacks because decrypt keys only exist in plaintext when a user is logged in. In fact, if you send the password with every user request, instead of having a separate login and session, the decrypt key is only in plaintext for the duration of a request, which is the minimum possible. An attacker would need to compromise the system and wait for a request to snag the key and be able to decrypt the data. System administrators wouldn't have access to user's data unless they also compromised the system in some way. On the other hand, if you can't trust your system admins, you're screwed. But this would protect against casual access to user's data - which is still valuable and it means attackers would need to gain access to both the persisted data and the transient in-memory decrypt key.
Would this work like I'm describing? Does it improve security? Is there a flaw here I'm not seeing?