I have a Drupal multisite setup with two sites connected to the same civicrm database. One site needs access to all of the data as usual. But I would like to hide some data from the other site, so that it accesses the DB on a "need to know basis". There are lots of custom fields for example that contain sensitive data, that the second site will never use.
I would like to secure this at the mysql level by letting the second site connect using a different DB username and to grant that user only the necessary privileges.
It would be nice if I could do this in a subtractive way - first
grant all privileges on civicrm.* and then remove access to the sensitive tables. However this does not work in mysql. You have to grant permissions to all the tables one by one if you don't want to include them all. So I did that, and granted SELECT, INSERT, UPDATE and DELETE on all the tables. But there were problems before I even began
revokeing anything. The event registration form was missing some fields, for example.
I tried adding a few other permissions - LOCK_TABLES, EXECUTE ... but clearly I am missing something.
What permission which is necessary for civicrm to work is missing from that list but is granted when you
grant all privileges?