How to store private encrypted user data in the database, but make them available to other chosen us

[thorn knot]

I have a website where users can store their personal data and can share their data with other users. Since, the data is very sensitive, I need to encrypt these data values using public-key cryptography before storing it in DB, but when the user can share the data with other users, they can view the unencrypted text.

Now, I have a use case where the user can search the data based on the values. But since the data values are encrypted and the ciphertext generated each time using public-key cryptography are different, so I cannot perform the search.

I have a way to solve this. I can store the hash of data values before encrypting and store the hash and encrypted data. I can use the hash for searching and decrypt the data while sharing the data with other users.

I don't want to store the same data twice. Can you please give me some alternate options.

I am using python, fastapi and MySQL database.

[short lotus]

What kind of application is it?

[short lotus]

1. Will you be utilising one set of private and public keys for encrypting and decrypting data?
2. Will the data be decrypted be with one private key or will there be multiple private keys involved?
3. What types of data will the users be sharing between each other that has to be encrypted?
4. What fields/keys can be searched associated with corresponding user?
5. Will users give access to their data explicitly to other users e.g. Friend requests
6. Will you support partial search because hashing doesn't allow for such an aspect?

I'm assuming you want a system like this as a failsafe in case someone access to your database

[short lotus]

-- Ask for Clarification --

Use symmetric key encryption opposed to asymmetric (public-key cryptography). This way you only manage one key for both encryption and decryption.

User accounts each have an autogenerated key (PIN Number e.g. 4290) that’s encrypted with their unhashed password. The user password will be stored hashed of course in the database alongside the encrypted autogenerated key. When a user decides they want a specific user to access their data, they will provide their password which will be used to decrypt the autogenerated key (4290). The decrypted autogenerated key (4290) will be given to the requested user to have them store and encrypt the autogenerated key with their own unhashed password which will be stored in association with the user’s account.

Creating a unidirectional bind between the user and requested user, the requested user can access the user’s encrypted data not vice versa unless of course they repeat the process mentioned above with swapped roles.
This method allows a user to safely encrypt and decrypt the data for the authorised users with no exposure of either the user’s password or autogenerated key. You will have to decide though how will users share the autogenerated key amongst each other safely likely in person exchange to keep the decrypted autogenerated key off your database.

You can safely search for users’ data by now decrypting the target user’s data with their account’s autogenerated key. This method will require the requesting user to provide their password every instance of decrypting the autogenerated key. User data can now be searched through securely.

-- This is purely theoretical but makes the most practical sense, this way you won't have to store both a hash and encrypted copy of the same data, if you have a better solution I am genuninely curious ---