1

I have a MySQL table containing names, email addresses etc. for users (table 1). I also want another table (table 2) in which data generated by the user, but intended to be anonymous (such as workplace reviews), are stored.

In the event of someone gaining access to my database, I do not want them to be able to easily link the individuals in table 1 to their data in table 2. Obviously, I need to be able to communicate between the tables to associate the user with his/her data (for example, if they wanted to edit or delete a review).

I understand that it would be impossible to completely failsafe this, but what would best practice be?

Would encrypting the userids in table 2 be worthwhile? Obviously, the encryption key would be stored somewhere on the server, but it would at least provide an additional hurdle.

I thought about hashing the userid in table 2, so that it could be queried from table 1, but without it being possible to query table 1 from table 2. Is this is sensible? I can't be certain that there wouldn't be a situation in the future where I could want to query table 1 from table 2, and this would preclude me from ever doing so.

Rob
  • 128
  • 1
  • 7
  • 29

1 Answers1

0

There are a number of solutions to this which can be used separately or in harmony with each other.

Column Level Encryption

You can also Encrypt your data using a separate key in both the tables but then you won't be able to use indexes while joining two tables.

SELECT AES_ENCRYPT(TABLE1.COLUMN1, 'table1key');
SELECT AES_ENCRYPT(TABLE2.COLUMN2, 'table2key');

So, in your scenario TABLE1.COLUMN1 and TABLE2.COLUMN2 is the link between the two tables. You can encrypt them using different keys and then while your application retrieves the information, you can decrypt them using the keys again. It will add considerable overhead, please remember.

Column Level Database Permissions

You can grant access to specific columns in a table to whoever you want by doing this

GRANT SELECT ON TABLE1 (COLUMN1, COLUMN2, COLUMN8, COLUMN9) TO USERNAME@HOST;

Stored Procedures/Functions

Although there is a downside of stored procedures that MySQL doesn't cache the query inside the stored procedure, but it definitely does provide an abstraction. The person running the code doesn't need SELECT access to the database. They only need EXECUTE access to that stored procedure. This way you can show the results that you want.

MontyPython
  • 2,906
  • 11
  • 37
  • 58
  • Thanks @montypython. In your first solution, where are the encryption keys stored I'm not sure how the other two solutions can work in my situation, as all access to the databases is done through a single user via PHP. – Rob Jan 26 '18 at 23:17
  • @Rob - You can either create a UDF for encrypting where you hard-code the encryption/decryption keys or you can keep them in some config files in your code. – MontyPython Jan 28 '18 at 10:25