Encrypting stored data for multiple unique users to access information

Developing a database for many users, and thousands more "people" in the database, one section requires we encrypt the personal data - Youth's information, medical information, etc.

We have users who are able to access the personal data on people within their control, but we are thinking that we will need to also encrypt the data when it's stored on our server. I believe that if I use each user's salted passwords to encrypt the data, only that user can access the data (not good). If I have one master key, then it would need to be stored outside of the application to access the data.

Are there techniques that can encrypt the data in such a way that a data breach on the server won't show the data in the database in clear text, but that multiple users of certain permissions can decrypt the data they need?

• Must encrypt the data in the database


• Multiple users may need to access the same encrypted data, based on their permissions


• Cannot be derived from the user's passwords (since some users may leave or change, requiring access to be removed/edited/added)


• HTTPS is already getting used, and I understand encrypting data client-side is a Bad Idea^TM


• If it matter: MySQL is the database system